Learn PHP: Selecting from a Database

Probably one of the most used MySQL commands is SELECT. This lets you select information out of a database table. If you take the table created in the previous post, and use phpMyAdmin to insert a few records to give us something to play with.

So to get PHP to work with MySQL you need to first of all tell PHP how to connect to it. In configuring phpMyAdmin (alternatively if you’re using it online) you should have a username and password. 9/10 times you’ll connect as well to the host “localhost”. Unless you’re running MySQL on a different server this should be fine for you. So to test your connection details create a file with the following code:

[source:php]< ?php // first we need to connect to the database mysql_connect("localhost", "username", "password") or die("Error connecting to MySQL: ".mysql_error()); ?>
[/source]

This is a simple connection to test your host, username and password. You need to run mysql_connect() once on a page, when you need to connect to a database. I say once on a page as it doesn’t have to be at the start of the page, it can be run when it’s first needed, however I seriously advise against running the function more than once in a page else your visitors will start to use up the MySQL connections allowed and you will overload the MySQL server (and your host won’t be too happy!). I tend to run the connection at the top of any page that requires it so that I know it’s run and I don’t have to run it again during the page code. Of course in the code above you need to replace username and password with your own preset username and password for MySQL.

To explain the die() function. This will run if there is an error. If the first function is not successful then it will kill the script dead in its tracks. I’ve added in a short string so you realise where the error occurs (on a page with many mysql queries etc it can be like finding a need in a haystack!) along with the use of another function mysql_error(). This function prints out the error returned by MySQL to help you debug your code.

If the host, username and password are correct then you shouldn’t see an error. In fact you shouldn’t see anything if it works, just a blank screen. If you do get an error then you will have to check the username and password are correct and that your host is correct too. MySQL connections are automatically closed when the page has been parsed, executed and displayed, so don’t worry, there’s no connections being left open here 😉

Once that’s working you want to ensure you can select a database and query a table, so to expand on the above:

[source:php]< ?php // first we need to connect to the database mysql_connect("localhost", "username", "password") or die("Error connecting to MySQL: ".mysql_error()); // then select the database mysql_select_db("mydb") or die("Error selecting database: ".mysql_error()); // now query the database $sql = mysql_query("SELECT * FROM content") or die("Error connecting to table: ".mysql_error()); ?>
[/source]

So the above, as the comments say, first connects to MySQL, then it selects which database to use using the mysql_select_db() function. Finally it uses the mysql_query() function with the most basic form of the SELECT statement to retrieve all content from the database and store it in the variable $sql. At this point the script doesn’t really do anything productive so what you need to do is extend it further.

We know that the database has the fields rowid, firstname, surname, address and postcode. So we use these to retrieve data from the result in the $sql variable as below:

[source:php]< ?php // first we need to connect to the database mysql_connect("localhost", "username", "password") or die("Error connecting to MySQL: ".mysql_error()); // then select the database mysql_select_db("mydb") or die("Error selecting database: ".mysql_error()); // now query the database $sql = mysql_query("SELECT * FROM content") or die("Error connecting to table: ".mysql_error()); // now display the information using a while loop while ($rowdetail = mysql_fetch_array($sql)) { print "

Name: “.$rowdetail[‘firstname’].” “.$rowdetail[‘surname’].”
\n”;
print “Address: “.$rowdetail[‘address’].”
\n”;
print “Postcode: “.$rowdetail[‘postcode’].”\n”;
print “


\n”;
}
?>
[/source]

This will then produce a list of results along the lines of

Name: John Smith
Address: 1 New Street, Any Town, Some County, England
Postcode: NS1 3FG


Name: Mary Jones
Address: 22 Wood Road, London, Greater London
Postcode: W4 3KF


So how does it work? The while loop, as explained previously, runs whilst the condition is true. So in this instance it is saying whilst there is a row to ‘fetch’ then run the following set of statements. To retrieve the table row details I’ve used this line:

[source:php]$rowdetail = mysql_fetch_array($sql)[/source]

As explained on the PHP.net site, mysql_fetch_array() Returns an array that corresponds to the fetched row and moves the internal data pointer ahead. It stores the details into the array variable $rowdetail. The array indexes are then defined by the fieldnames of the table. As you can see the print statements then just print the information out using each index. Once all statements are executed it then repeats until all rows have been displayed.

This is the most straightforward method of database retrieval. You can display the information and also choose which information you like as you like (in this case I didn’t bother using the rowid). It can be displayed in an XHTML table, a list or however you wish.

So what else can you do with the SELECT statement? Well there are plenty of options available. I’ll only touch on the basics and advance as and when required. So options available at this point are:

[source:sql]
// select all rows and details with no conditions
SELECT * FROM content
// select just the firstname and surname
SELECT firstname, surname FROM content
// select all rows where the surname is Jones
SELECT * FROM content WHERE surname LIKE ‘Jones’
// select all rows where the surname contains “Mac” – here we use the wildcard % to match part of a string
SELECT * FROM content WHERE surname LIKE ‘%Mac%’
// select all rows and put in alphabetical order of the surname
SELECT * FROM content ORDER BY surname ASC
// select just the first 5 rows of the table
SELECT * FROM content LIMIT 5
// select just the first 3 rows of the table after the first 2 (so rows 3-5)
SELECT * FROM content LIMIT 2, 3
// select rows where the firstname contains sam and the surname contains mac
SELECT * FROM content WHERE firstname LIKE ‘%sam%’ AND surname LIKE ‘%mac%’
// now put a whole select statement together
SELECT firstname, surname FROM content WHERE surname LIKE ‘%mac%’ ORDER BY firstname ASC LIMIT 30
[/source]

using the previous PHP example, if you only want to retrieve one record from the table then you do not need to use the while loop but just the mysql_fetch_array() function on it’s own ie.

[source:php]
< ?php // first we need to connect to the database mysql_connect("localhost", "username", "password") or die("Error connecting to MySQL: ".mysql_error()); // then select the database mysql_select_db("mydb") or die("Error selecting database: ".mysql_error()); // now query the database $sql = mysql_query("SELECT * FROM content WHERE surname LIKE '%mac%' LIMIT 1") or die("Error connecting to table: ".mysql_error()); // save information into the array $rowdetail $rowdetail = mysql_fetch_array($sql); // print the information out on the page print "

Name: “.$rowdetail[‘firstname’].” “.$rowdetail[‘surname’].”
\n”;
print “Address: “.$rowdetail[‘address’].”
\n”;
print “Postcode: “.$rowdetail[‘postcode’].”\n”;
?>
[/source]

As you can see the above is virtually the same except that you only need to retrieve an array once so there is no requirement for the While loop.

That’s probably the most used MySQL statement in web page development. It may not seem like it now but that statement is all you usually need to use on a typical database driven web site.

You may also like...

2 Responses

  1. Sharon Spain says:

    I think You’ve explained all that really well. I think selecting from one table is the easiest, the difficulty for me as a learner is selecting data from different tables to display at the same time. I’ve noticed books all have different ways of doing it so it adds to the confusion. Anyway I like this blog.

  2. Sarah says:

    Hi Sharon, I’ve not had time to go further into these posts (apologies to everyone else too), but to select from multiple tables as a beginner I’d just go for the method of using

    SELECT * FROM table1, table2 WHERE table1.rowid = table2.rowid AND table1.field = 'some value'

    Then you access as usual using the field names. If you have fieldnames that overlap (eg. both have ‘title’ as a fieldname) then you can give these alternate names in the SELECT statement. I understand what you mean, there are plenty of different methods to select multiple information, but my MySQL knowledge isn’t very good in the use of multiple tables (well not yet!)

Leave a Reply

Your email address will not be published. Required fields are marked *