Home and Learn: PHP Programming Course
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.
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 dont 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.
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:
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.
<-- Back One Page | Move on to the Next Part -->
Email us: enquiry at homeandlearn.co.uk