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;

Following output will appear..

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)

if(!$con)
{
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>
<TH>CUSTOMER ID</TH>
<TH>FIRST NAME</TH>
<TH>LAST NAME</TH>
<TH>STREET</TH>
<TH>HOUSE NUMBER</TH>
<TH>CITY</TH>
<TH>ZIPCODE</TH>
<TH>PHONE</TH>
<TH>EMAIL</TH>
<TH>COMPANY NAME</TH>
</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 “<TR>”;
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>” ;
“</TR>”;
}

==> 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! ;-)

Advertisements

2 thoughts on “Advanced webcoding: PHP – Part 3: Selects

  1. Pingback: Advanced webcoding: PHP – Part 3: Selects | All kinds of ….solutions

  2. Pingback: Beginners guide to advanced webcoding « Tech-o-rama

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: