MySQL One Liners

We have been using PHP and MySQL for web development almost exclusively for several years. During this time, our team has come up with a list of simple, yet powerful one line SQL statements that make MySQL easier to use. We thought we might share them with you.

Check out the list and feel free to submit your favorite MySQL one liner statements in the Leave a Reply section below.

Most one line statement nest multiple MySQL operations. For many the examples below the one-line statement wraps to the next line due to space limitations, but they really are one line statements.

The first example simply establishes a MySQL connection and selects the database–all in one line. To use it, you can either plug in the database name (database1), host (localhost), username and password or assign the values to variables.


mysql_select_db('database1', mysql_connect('localhost','username','password'));

Using values assigned to variables, it would look like this.

$host = "localhost";
$user = "username";
$pword = "password";
$dbname = "database1";
mysql_select_db($dbname, mysql_connect($host,$user,$pword));

If you are not using auto_increment to establish a unique ID for each row, you can do the same thing manually. This example grabs the next category number to be used for an INSERT statement. The second line establishes a starting value if the table is empty.

$catID = mysql_result(mysql_query("SELECT MAX(catID)+1 AS catID FROM categories"),0);

This one returns the total number of rows in a table.

$total = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM items"),0);

You can pull a single value out of a table if you are accessing a unique record. This example uses an established item ID to extract the item name.

$itemName = mysql_result(mysql_query("SELECT item_name FROM inventory WHERE item_id='$itemID' "),0);

Here is a one that we use to increment a count in a table every time someone clicks on a product in one of our affiliate marketing sites. You really do not need to read a row, increment a count and write the count back to the table. You can can do it all in one line with a single MySQL update statement.

mysql_query("UPDATE items SET click_count = LAST_INSERT_ID(click_count + 1) WHERE item_id = '$itemID' ");