JDT

John Dixon
Technology
Limited

Essential MySQL 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 aren't 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 haven't got a root password set, here's 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's 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's 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';

Author: John Dixon
John Dixon Technology Ltd

Go back to MySQL Tutorials home page

Go back to Tutorials home page

Earnings Tracker is John Dixon Technology's FREE accounting / bookkeeping software tool.

Aimed at UK contractors and freelancers, Earnings Tracker enables you to perform bookkeeping and accounting tasks, helping you to keep track of your company's revenue and spending.

The software is written in PHP and MySQL and is available to use for FREE online, or as a FREE download.

Earnings Tracker can also be used simply as a dividend, corporation tax, or VAT calculator.

JDT

Privacy Statement

Terms & Conditions