PostgreSQL & PHP Tutorials - Retrieving data from your database
PHP »
Retrieving Data from your database
PHP » Starting Out » Retrieving Data from your database
PostgreSQL » Retrieving Data from your database
PostgreSQL » Starting Out » Retrieving Data from your database
PHP » Starting Out » Retrieving Data from your database
PostgreSQL » Retrieving Data from your database
PostgreSQL » Starting Out » Retrieving Data from your database
Posted By Chris Smith
Posted on 09 Feb 2006, 05:32 AM
Now we can add data to the database we'll look at how to retrieve it.
Firstly we need to connect to the database. We can copy this from our add script:
$db = pg_connect('host=localhost dbname=contacts user=contacts password=firstphp');
Next we have to work out how to fetch it.
Fetching data uses a 'SELECT' query. Our fetch page will look a little like this:
As with our add script, we run a query:
and get the database result:
It gets a little different here.
This will go through each row returned from the database and print out the details in a table row. When there are no more entries to retrieve, pg_fetch_assoc returns false instead of a proper resource, so we know it has finished. This stops an endless loop from running.
We use the 'htmlspecialchars' function so in case someone adds any html code in incorrectly (eg a <b> or <i>) it doesn't show up. While bold and italics tags are fine, if they add javascript code (<script> tags) then it would execute in the browser (this is called XSS - Cross Site Scripting) and cause a problem.
That's it! Pretty simple!
Firstly we need to connect to the database. We can copy this from our add script:
$db = pg_connect('host=localhost dbname=contacts user=contacts password=firstphp');
Next we have to work out how to fetch it.
Fetching data uses a 'SELECT' query. Our fetch page will look a little like this:
<html>
<body>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
Friend ID
</td>
<td>
First Name
</td>
<td>
Surname
</td>
<td>
Email Address
</td>
</tr>
<?php
$db = pg_connect('host=localhost dbname=contacts user=contacts password=firstphp');
$query = "SELECT * FROM friends";
$result = pg_query($query);
if (!$result) {
echo "Problem with query " . $query . "<br/>";
echo pg_last_error();
exit();
}
while($myrow = pg_fetch_assoc($result)) {
printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", $myrow['id'], htmlspecialchars($myrow['firstname']), htmlspecialchars($myrow['surname']), htmlspecialchars($myrow['emailaddress']));
}
?>
</table>
</body>
</html>
As with our add script, we run a query:
<?php
$query = "SELECT * FROM friends";
?>
and get the database result:
<?php
$result = pg_query($query);
if (!$result) {
echo "Problem with query " . $query . "<br/>";
echo pg_last_error();
exit();
}
?>
It gets a little different here.
<?php
while($myrow = pg_fetch_assoc($result)) {
printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", $myrow['id'], htmlspecialchars($myrow['firstname']), htmlspecialchars($myrow['surname']), htmlspecialchars($myrow['emailaddress']));
}
?>
This will go through each row returned from the database and print out the details in a table row. When there are no more entries to retrieve, pg_fetch_assoc returns false instead of a proper resource, so we know it has finished. This stops an endless loop from running.
We use the 'htmlspecialchars' function so in case someone adds any html code in incorrectly (eg a <b> or <i>) it doesn't show up. While bold and italics tags are fine, if they add javascript code (<script> tags) then it would execute in the browser (this is called XSS - Cross Site Scripting) and cause a problem.
That's it! Pretty simple!
Avg Rating: 4
Vote Count: 31
Vote Count: 31