Advanced webcoding: PHP – Part 3: Selects
Now you know how to do inserts with PHP and MySQL, maybe, juuust maybe, it’s also great to learn how to get your data out. This tutorial will show you how you show the output of a select statement in PHP.
Right off we go.. If we again take a look at the MySQL console and we execute following statement:
SELECT * FROM CUSTOMER;
Which is all the information from the customer table. We’ll need to execute the exact same statement in our PHP script. In this script there will be some code you probably won’t have seen before (if you’re new to all of this programming business).
However, don’t panic yet, the code is rather simple.
But let me first explain what I want to do. I want to create a PHP page that shows all the records from my CUSTOMER table in an HTML table. This means we will have to dynamically create the table.
The reason for this is that we don’t know how many records are in the table… This amount can vary over time.
So we’ll have to build something in the code that loops through all the data and write table lines around it.
I came up with the following script:
Don’t worry.. it really is not that hard! ;-) Let’s go through it step by step:
<?PHP ==>Starting the PHP page
$con = mysql_connect(“localhost”,”tutorial”,”tutorial”); ==> Creating a variable which contains the connection to the database (like we did in the previous tutorial)
die(‘Error: ‘ . mysql_error());
==> If the connection fails, the mysql error should be shown
mysql_select_db(“tutorial”,$con); ==> Selecting the database (again just like before)
$result = mysql_query(“SELECT * FROM CUSTOMER”); ==> This variable contains the select statement.
ECHO “<TABLE BORDER=’1′><TR>
==> This ECHO will just put some HTML code on the page. More specifically, the starting tag of an HTML table and the table Heading. This only needs to be printed once.
WHILE ($row = mysql_fetch_array($result))
echo “<TD>” . $row[‘ID’] . “</TD>” ;
echo “<TD>” . $row[‘FST_NAME’] . “</TD>” ;
echo “<TD>” . $row[‘LST_NAME’] . “</TD>” ;
echo “<TD>” . $row[‘STREET’] . “</TD>” ;
echo “<TD>” . $row[‘HOUSE_NR’] . “</TD>” ;
echo “<TD>” . $row[‘CITY’] . “</TD>” ;
echo “<TD>” . $row[‘ZIPCODE’] . “</TD>” ;
echo “<TD>” . $row[‘PHONE’] . “</TD>” ;
echo “<TD>” . $row[‘EMAIL’] . “</TD>” ;
echo “<TD>” . $row[‘COMPANY_NAME’] . “</TD>” ;
==> Now this is an interesting piece of code. This is a while loop (which you may have expected, since it says WHILE on top). This while loop has a condition defined between the brackets on top. It says that the variable row should get the value of the variable result and this for as long as you can find data.
So if you have 1 record, this loop will only be done once, if you have 100 records this loop will be done 100 times.
Inside the while loop you can see that each time a record is found a new HTML table row is created and each cell in the table contains a value. For example= $row[‘FST_NAME’]: this just says that the value of FST_NAME from the variable row should be shown here. And I did this for all the fields.
Like this you just build dynamic HTML.
ECHO “</TABLE>” ; ==> The closing tag of the table (also needs to be done only once)
mysql_close($con); ==> closing the database connection
?> ==> End of the PHP tag
The result looks like this:
Not all that hard right? Give it a shot.. Let me know if you have questions! ;-)