Viewing data from a database
A tutorial on viewing data from a database
There are many ways to retrieve data from a database this tutorial will show you a popular way of selecting the data you want and then printing it to the page.
This tutorial assumes you have a basic knowledge of PHP
To start off include your database connection details
|
1 2 3 4 |
<?php //connect to database require ('config/globel.php'); ?> |
Select what you want to display from the database to select everything use * which stands for ‘All’
If you wanted to select certain fields you can specify them like:
|
1 2 3 4 5 |
<?php "SELECT id, name, email FROM tablename" $sql = "SELECT * FROM table-name"; ?> |
Then add the results to a variable called result
|
1 2 3 |
<?php result = mysql_query($sql); ?> |
Then were going to get all the affected rows using the function mysql_num_rows and add it to a variable called Rows
|
1 2 3 |
<?php $Rows = mysql_num_rows($result); ?> |
We now create a new variable and give it a value of 0
|
1 2 3 |
<?php $i = 0; ?> |
To get all rows instead of just one we need to loop through all results until there is no more. To do this we will use a while loop.
|
1 2 3 4 5 6 7 |
<?php while ($i <$Rows) { $messageID = mysql_result($result, $i, "messageID"); $messageTitle = mysql_result($result, $i, "messageTitle"); $messageCont = mysql_result($result, $i, "messageCont"); ?> |
The while loop remains true while $Rows is less then $i once false the while loop stops. Inside the while loop we add the result, $i variable and the row name to a variable of the same name.
We now have the data from the database and want to show the data on the screen so we echo the data and add some html tags to make them more readable while inside the loop.
At the end of the loop we set $i to increment (++) for each loop. Then close the loop.
|
1 2 3 4 5 6 |
<?php echo "$messageTitle "; echo "$messageCont "; $i ++; } // close while ?> |
You will now have the data from the database provided there is some data to show. To print a message when there is nothing to show you can use a simple if statement
|
1 2 3 4 5 |
<?php if ($i == 0){ echo "There are no results to show "; } ?> |
If $i is equal to 0 meaning there is no data print a message.
Here’s the full code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<?php //connect to databse require ('config/globel.php'); $sql = "SELECT * FROM table-name"; $result = mysql_query($sql); $Rows = mysql_num_rows($result); $i = 0; while ($i <$Rows) { $messageID = mysql_result($result, $i, "messageID"); $messageTitle = mysql_result($result, $i, "messageTitle"); $messageCont = mysql_result($result, $i, "messageCont"); echo "$messageTitle "; echo "$messageCont "; $i ++; } // close while if ($i == 0){ echo "There are no results to show "; } ?> |
About Author
David Carr | Website | Twitter
I'm a PHP Web Developer based in Hull it is my passion to develop clean, accessible and usable websites and applications, I'm an avid Twitter user why not Follow me? @daveismynamecom or join me on Google+