Retrieving data

In the last chapter, we established a connection to your MySQL database, so by now its time to do something interestingly with the connection. As mentioned in the introduction, we interact with the MySQL server by sending SQL code through a PHP function, more specifically the mysql_query() function.

To retrieve data through SQL, the SELECT statement is used. It comes in many variations, but it its most simple form, it typically looks like this:

SELECT column_name FROM table_name

This query will select data from the column called "column_name" in the table called "table_name" and return all rows of it. In the introduction chapter, we inserted some rows of data in our test_users table, where we have the columns id, name and country. To get this data, our query could look like this:

SELECT id, name, country FROM test_users

That's the SQL needed to retrieve our data. Let's make PHP execute it for us, by using the mysql_query() function:

mysql_connect("localhost", "username", "password");
mysql_select_db("my_database");
$query = mysql_query("SELECT id, name, country FROM test_users");

The mysql_query() function simply sends the SQL code to the MySQL server and then returns a link to the result. We will then have to use one of the many related PHP functions to actually use the result. For instance, we can see how many rows were returned by using the mysql_num_rows() function (the connection should already be made, as shown above):

$query = mysql_query("SELECT id, name, country FROM test_users");
echo "The table currently contains " . mysql_num_rows($query) . " row(s)";

Of course, if we only wanted the number of rows, there would be more efficient ways of doing it and there would be no reason for selecting all three rows. Instead of just getting the number of rows, let's try actually getting some data out:

$query = mysql_query("SELECT id, name, country FROM test_users");
echo "The first name is: " . mysql_result($query, 0, "name");

The mysql_result() can pick out a single piece of data from a result link. As parameters, we specify the query link, the row index and the name of the column we want data for, so in this case we get a result from the $query result reference, we use the first row of returned (row number zero) and we get data from the column called "name". That will get us the first name in the table, which is then outputted.

In most situations you might need all the rows of data that you select, along with all the columns you select. This is usually done with the mysql_fetch_array() function, which simply gets you an entire row of data as an array, while moving the internal pointer one step ahead, so that the function will get you the row after that the next time you call it. This makes it excellent for using with a loop, to get all the rows one after another. Let's look at an example where we do just that:

$query = mysql_query("SELECT id, name, country FROM test_users");
while($row = mysql_fetch_array($query))
    echo $row["name"] . " is from " . $row["country"] . "<br />";

We use a while loop, in which we assign the result of the mysql_fetch_array() function to the variable named $row on each iteration. This works because mysql_fetch_array() will return FALSE when there are no more rows, in which case the while loop will end. As long as there IS in fact a row left, mysql_fetch_array() places all columns and their values in the $row variable, where we can access it from simply by asking for the same name used in the SQL code. In our example, that gives us access to id, name and country, but to keep it less complicated, I have only used name and country so far.

So, that's the most basic ways of getting data out of the database. As you can see, it's fairly easy, since PHP has a bunch of nice functions for doing it, but there's much more to working with MySQL and there's definitely more to writing SQL. Read on to learn more about both.