View all MySQL tables in console

You may be wondering why phpmyadmin is showing more tables than the console or vise versa.

The reason is because one of them does not have the privilege to view them all. I was having a issue with seeing all my tables in the mysql console and this is what fixed it. I had to log in as the root. (I'm using a linux computer)

$ /opt/lampp/bin/mysql -u -p


If its the other way around which phpmyadmin doesn't show all the databases is because you have put the wrong user in phpmyadmin source code. Change phpmyadmin settings to fix this.

tags:
phpmyadmin database does not match console
mysql hidden tables console
not all databases are shown in console
show all databases

Access MySQL console in Linux using XAMPP

Open the terminal and type:

/opt/lampp/bin/mysql

Then press enter and the terminal will turn into the console.

To exit the mysql console type exit and press enter

Autostart XAMPP in linux ubuntu

This is the simplest and easiest way to autostart automatically xampp when your computer boots

1. run the line below in terminal, this opens the file rc.local for editing
$ sudo gedit /etc/rc.local

2. write this line of code in the file rc.local. Make sure you follow the instructions contained in the file
/opt/lampp/lampp start

3. save and exit




#no need to edit init.d

Decrypt and Encrypt in MySQL example

If you need to put encrypted data (eg.credit cards) into MySQL database. Below are examples on how to encrypt and decrypt your data. We don't use the password function because once encrypted it cannot be decrypted.

Make sure the data field accepts special charaters like char() not varchar()

Encrypt:

INSERT INTO tableName (fieldName)
VALUES (AES_ENCRYPT('dataGoesHere','ThePasswordGoesHere'));

Decrypt:

SELECT aes_decrypt(fieldName,'ThePasswordGoesHere') 
FROM tableName;

MySQL Cheat Sheet

MySQL Commands

//select database
mysql> use [databasename]

//show all databases
mysql> show databases;

//show database tables
mysql> show tables;

//describe table, must have the database selected
mysql> show tables;

//a search query that allow regular expressions
SELECT * FROM customers WHERE city like 'Air%';

//create table, must have a type after the name. eg: name=customerid, type=text
create table books
(customerid text, name text);

//delete table
drop table balls;

//insert values
insert into customers values
(null,'blahblah', 3);

//connecting subqueries
select name from customers where customers.customerid = any (select orders.orderid from orders,order_items where orders.orderid = order_items.orderid);

//Update record
update books set price = price1.1;

//alter table, add columns. its like create table, name and type
alter table books
add (tax text);

alter table books
drop tax;

//delete record
delete from customers
where cus

Principle of Least Privilege

The principle of least privilege can be used to improve the security of any computer system. It's a basic but important principle that is often overlooked. The principle is as follows:

A user (or process) should have the lowest level of privilege required to perform his assigned task.

It applies in MySQL as it does elsewhere. For example, to run queries from the Web, a user does not need all the privileges to which "root" has access. You should therefore create another user who has only the necessary privileges to access the database you just created.

Excerpt from PHP and MySQL Web Development page 223

AutoStart WAMP when windows start(apache and mysql)

Auto starting Mysql and Apache when windows starts gives you a desktop application feel built on PHP.

The Instructions:
Easiest way is to move 'start wampserver' from the start>program menu>wampserver and drag it into your start>program menu>startup folder. However if windows ask for permission to start the program the option below will fit your needs better

Go start>run> type in 'services.msc' and press enter

scroll down to the two services

wampapache
wampmysql

right click them and select properties.

on the general tab change startup from manual to automatic.

WARNING: Doing this method will not show the wamp icon at the bottom right hand conner of screen

in order to adjust Apache and Mysql settings start wamp and the icon will then appear