Useful MySQL Commands For PHP Development

When I'm developing PHP applications I normally have a DOS window open so I can run SQL scripts from the command line. Often, this is so I can check that MySQL database tables are being updated correctly while I am running PHP scripts.

There are not that many command line tasks I need to perform, but it is useful to be able to run them as and when I need to. In this tutorial I'll look at a few MySQL commands I find really useful during the development of PHP applications.

Logging onto the MySQL monitor

To log onto the MySQL monitor:

1. Open a command prompt window, and navigate to the the mysql\bin folder.
2. Run the command:

mysql

OR

mysql -u user -p

If prompted, enter the password for the database user.

Setting a root password

If you have not got a root password set, here is how you set one.

1. Log onto the MySQL monitor as described above.
2. Run the command:

set password for root@localhost=password('password');

Updating a table

I quite often need to add or delete new columns, or modify existing ones.

To perform the following commands you need to be logged onto the MySQL monitor, as described above.

Adding a new column

The format is:

ALTER table tablename

ADD new_column type

AFTER column;

And here are a couple of examples:

ALTER table users

ADD surname char(30)

AFTER firstname;

ALTER table users

ADD age int (2) NOT NULL default '0'

AFTER surname;

Modifying an existing column

Sometimes it is necessary to update existing columns; here is the format:

ALTER table tablename

MODIFY column type;

And here's an example:

ALTER table users

MODIFY age int (3) NOT NULL default '0';

Dropping a column

Here's the format:

ALTER table tablename

DROP column;

And here's an example:

ALTER table users

DROP age;

Dumping the contents of a database into an SQL file

For backup purposes, if nothing else, it's sometimes useful to dump all the contents of a database into an SQL file. Here's how you do it:

1. Open a command prompt window, and navigate to the the mysql\bin folder.
2. Run the command:

mysqldump -u database_user_name -p database_name > database_name.sql

When prompted, enter the password for the database user.

Sending the output from an SQL query to a file

Sometimes it is useful to send the output from a query to a file, for example, a text file. Here is an example of how to do it.

1. Log onto the MySQL monitor.
2. Run the SQL query, for example:

SELECT name,surname FROM users into outfile '/tmp/users.txt';

About the Author: John Dixon is a web developer working through his own company John Dixon Technology Limited. The company also develops and supplies a free accounting bookkeeping software tool called Earnings Tracker. The company's web site contains various articles, tutorials, news feeds, and a finance and business blog.