MySQL databases - read a record with PHP

 

To read records from a database, the technique is usually to loop round and find the ones you want. To specify which records you want, you use something called SQL. This stands for Structured Query Language. This is a natural, non-coding language that uses words like SELECT and WHERE. At it's simplest level, it's fairly straightforward. But the more complex the database, the more trickier the SQL is. We'll start with something simple though.

What we want to do, now that we have a connection to our database, is to read all the records from our address book, and print them out to the page. Here's some new code, added to the PHP script you already have. The new lines are in blue:

<?PHP
require '../configure.php'

$db_handle = mysqli_connect(DB_SERVER, DB_USER, DB_PASS );

$database = "addressbook";

$db_found = mysqli_select_db($db_handle, $database);

if ($db_found) {

$SQL = "SELECT * FROM tbl_address_book";
$result = mysqli_query($db_handle, $SQL);

while ( $db_field = mysqli_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";
print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

}

}
else {

print "Database NOT Found ";

}

mysqli_close($db_handle);

?>

Before we go through the new code to see what's happening, run your script. You should find that the address you added in a previous section is printed out. (We only have one record at the moment.)

1
Test
Name
12 Test Street

The first line in the new code is this:

$SQL = "SELECT * FROM tbl_address_book";

The $SQL is just a normal variable. But we're putting into it a long string. This is a SQL statement. Here's a brief run down on SQL.

 

Structured Query Language

SQL (pronounced SEEKwel), is a way to query and manipulate databases. The basics are quite easy to learn. If you want to grab all of the records from a table in a database, you use the SELECT word. Like this:

SELECT * FROM Table_Name

SQL is not case sensitive, so the above line could be written:

Select * From Table_Name

But your SQL statements are easier to read if you type the keywords in uppercase letters. The keywords in the lines above are SELECT and FROM. The asterisk (*) means "All Records". Table_Name is the name of a table in your database. So the whole line reads:

"SELECT all the records FROM the table called Table_Name"

You don’t have to select all the records from your database. You can just select the columns that you need. For example, if we wanted to select just the first name and surname columns from this table, we can specify that in our SQL String:

"SELECT First_Name, Surname FROM tbl_address_book";

When this SQL statement is executed, only the First_Name and Surname columns from the database will be returned.

There are a lot more SQL commands to get used to, and you'll meet more of them as you go along. For now, we're just selecting all the records from our table.

 

Back to the Code

The first line of our code, then, was this:

$SQL = "SELECT * FROM tbl_address_book";

SO we have a SQL statement, but we need to pass it to another inbuilt function:

mysqli_query( )

The mysqli_query( ) function is used to send a SQL query to your database. If you have typed out your SQL correctly, then the function will return a value. This value will be true, false, or a file handle. Because we're using the SELECT keyword, the value returned by will be a file handle. In our code, the line was this:

$result = mysqli_query( $SQL );

The file handle returned in our $result variable just points to the results. It doesn't actually bring anything back. To bring back the data, we had this inside a while loop:

$db_field = mysqli_fetch_assoc( $result );

The inbuilt function we're using to bring results back is this:

mysqli_fetch_assoc( $result )

The assoc part means Associative. As in "associative array". So we're asking that the results be brought back in an array format. In between the round brackets of mysqli_fetch_assoc we have typed the name of our file handle – the one that was pointing to the results of SQL statement.

Remember: an associative array is one where the keys are text. So it's this format:

Array['One'] =
Array['Two'] =
Array['Three]' =

And not this:

Array[1] =
Array[2] =
Array[3] =

When the mysql_fetch_assoc function returns an array, we're putting it all into a variable called $db_field. The Key part of the array is all the Column names from our database tables. This is done automatically for you. So the array format will be this:

$db_field[Column_Name] = Value

The reason why you're doing this is so that you can loop round the array and access the values from the table. Here's our loop, without anything between the round brackets:

while ( ) {

print $db_field['ID'] . "<BR>";
print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

}

So we're printing whatever the value is in the array position $db_field['ID'], $db_field['First_Name'], $db_field['Surname'] and $db_field['Address']. We're also adding a HTML line break at the end, just for printing purposes.

If all that is confusing, just remember the format:

Array_Name[Table_Coulmn_Name] = Value_From_Record

Our whole while loop, then, is this:

while ($db_field = mysqli_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";
print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

}

Because that is a bit complex, let's go through the steps we've used to access the records from our table:

  1. Set up a SQL Statement that can be used to get the records from the database table
  2. Use mysqli_query() to bring back the records we've specified in Step 1
  3. Use mysqli_fetch_assoc() to set up an array. The array will contain all the records that were returned in Step 2
  4. Loop round all the data in the array using a While loop

Step 1 was this, in the code:

$SQL = "SELECT * FROM tbl_address_book";

Step 2 was this:

$result = mysqli_query($SQL);

Step 3 was this:

$db_field = mysqli_fetch_assoc($result)

And Step 4 was this:

while ($db_field = mysqli_fetch_assoc($result) ) {

print $db_field['ID'] . "<BR>";
print $db_field['First_Name'] . "<BR>";
print $db_field['Surname'] . "<BR>";
print $db_field['Address'] . "<BR>";

}

 

If you're still confused, study the code and go over this section. In the next section, we'll adapt the code to add more records to our database table.