Working with MySQL: Data Access

Taking data from the database is even more simple than storing it in there. Because we’re already set up our forms and created the database in our Working with MySQL: Storage tutorial, we can get struck into displaying that data on a page.

First we create our page, as normal this can be called anything you like but for consistency we’ll go with “results.php” — to show the results of our visitor survey. We start with the database connection string that we created when we made our survey table:

<?php
$conn = mysql_connect("localhost", "YOUR-MYSQL-USER", "YOUR-PASS");
mysql_select_db("YOUR_DATABASE", $conn);

?>

We now need to decide how we want to view the results. If you had lots of complex fields you might want to display them in a table as tabular results, but as we only have a few fields there’s no reason why we can’t display them using normal paragraph tags. So, first we start by selecting our data from the database:

<?php
$conn = mysql_connect("localhost", "YOUR-MYSQL-USER", "YOUR-PASS");
mysql_select_db("YOUR_DATABASE", $conn);

$select = mysql_query("SELECT * FROM `survey`");
?>

The query is simply: we’re select “*” (which means all fields) from table “survey”. If we had lots of fields and we only wanted to display a couple, it’d make more sense to specify our fields individually (e.g. SELECT `name`, `email` FROM `survey`) which would speed up the query and not waste valuable processing time.

Now that we’ve built our query, we’ll use mysql_fetch_assoc() to read it into an associated array (that’s an array that you reference by field name, instead of with a numerical key, e.g. $row[‘name’] instead of $row[0]) and while() to loop through it and display each row. Inside our while() loop we’ll break in and out of PHP several times. Although this is not necessary for this tutorial, it helps if you’ve got bulky HTML that you may need to ‘escape’ and it’s better to learn now than struggle later! So:

<?php
$conn = mysql_connect("localhost", "YOUR-MYSQL-USER", "YOUR-PASS");
mysql_select_db("YOUR_DATABASE", $conn);

$select = mysql_query("SELECT * FROM `survey`");
while($row = mysql_fetch_assoc($select)) {
?>
	<p>
	Name: <?php echo $row['name']; ?><br>
	E-mail: <a href="mailto:<?php echo $row['email']; ?>">email</a><br>
	Country: <?php echo $row['country']; ?><br>
	Score: <?php echo $row['score']; ?><br>
	Comments: <?php echo nl2br($row['score']); ?>
	</p>
<?php
}
?>

It’s as easy peasy as that. Each of your survey results will now display in a paragraph of their own, each pulled from your database.

Speak Your Mind

*