Basics of PHP – MySql Queries Explained

While this isn’t strictly exclusive to PHP understanding the insides of a MySql query are vital to any developer, whatever their programming language. In the last couple of tutorials we covered MySql Select and MySql Insert but we didn’t talk a lot about the query itself, that is what actually appears as a parameter in the mysql_query() function (eg. “select * from people”). So first let’s talk about the MySql Select query.

select * from people

In this example we are asking MySql to select all the columns from the table people, if you look at the previous tutorial the table people has three columns called contact_id, name & phone_number. By using an asterisks (*) we are asking PHP to select all three of these columns for use.

But what if we don’t want to read all of the columns. Say we only want to look at ‘name’ and ‘phone_number’ and don’t care about ‘contact_id’ we can ask MySql only to return certain columns.

select `name`, `phone_number` from people

The columns we want MySql to return are specified inside a (`) and separated by commas (,).

We can actually ask MySql to return the results in a certain order, say alphabetically or in numerical order (or the opposite to both these).

select `name`, `phone_number` from people order by name asc

‘Order By’ tells MySql we would like the results returned to us in a specific way, ‘name’ specifies we would like to order by the ‘name’ column and ‘asc’ means ascending (starting with ‘a’, then ‘b, c, d’ ect or ’1, 2, 3′ ect). We could ask MySql to return the results descending by using ‘desc’.

Let’s say now we only want MySql to return the phone number for ‘John Doe’.

select `name`, `phone_number` from people where name='John Doe' limit 0, 1

Two new things have been introduced here ‘where’ and ‘limit’. ‘Where’ searches for results which match a parameter, here we are asking MySql to only return results where the column ‘name’ is set to ‘John Doe’. ‘Limit 0, 1′ means MySql will only return the first result. If we set this to ‘limit 0, 2′ MySql will return the first two results. However as there is only one ‘John Doe’ MySql can only return one result so even if you set it to ‘limit 0, 2′ or you don’t set a limit at all you will only get the one result back. This is used to tell MySql to stop looking for other ‘John Doe’s, this can cut down on MySql execution time significantly (especially if you have a large table) and make your program run faster.

Now that we have covered ‘select’ let’s talk about ‘insert’. Here we have the example used in the previous tutorial.

insert into people (`name`, `phone_number`) values ('John Doe', '555-6725')

Here MySql is being told that we want to insert into the table ‘people’ and the columns ‘name’ and ‘phone_number’ (you should always tell MySql the columns you want to insert into and don’t use (*) like in mysql select). ‘Values’ then tells MySql what the values of ‘name’ and ‘phone_number’ are, and they are then given in the next set of brackets.

Advertisement