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

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:


<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: 61


              

  New Reply


how do i list down all my data in my database? i found that this command will only print out the latest data only. can i make all my data to be printed? amer 13 Apr 2006 Reply
`-- This does list all your data.

If it doesn't work then shoot me an email through the contact page and I'll help you figure out what's going wrong.
Chris Smith 16 Apr 2006 Reply

Thanks for the simple tutorials; they are a breakthrough for me, the newbie-est.

I can get a field to display in a tabular form by using this tutorial, but it doesn't work when trying to display "select field_1, sum(field_2) from big_table group by field_1". The resultant table builds fine and is grouped on field_1, but the column where the sum(field_2) aggregate values go is empty. How is this done? Any different for count(*)?

A tutorial for outputting to a file (like mysql's "...into outfile '/tmp/some_file.csv' fields term..." would help too.

thanks,

r
Reed Loefgren 27 Apr 2006 Reply
`-- > I can get a field to display in a tabular form by using this tutorial, but it doesn't work when trying to display "select field_1,
sum(field_2) from big_table group by field_1". The resultant table builds fine and is grouped on field_1, but the column where the
sum(field_2) aggregate values go is empty. How is this done? Any
different for count(*)?

Try

sum(field_2) AS mysum

then you can access 'mysum'.

> A tutorial for outputting to a file (like mysql's "...into outfile '/tmp/some_file.csv' fields term..." would help too.

Sure.. will see what I can do :)
Chris Smith 28 Apr 2006 Reply

Hi Smith,

How about using "stored procedures" or "functions" with many pointers?

What PHP version support it?

Parikesit 27 Apr 2006 Reply
`-- > How about using "stored procedures" or "functions" with many pointers?

Sure. It's tough to do this because it depends on what you're trying to do.

> What PHP version support it?

All should. The php version doesn't matter, only postgresql.
Chris Smith 28 Apr 2006 Reply


Want to post a comment? Fill in the details below.

Your Name  : 
Your Email  : 
Your Website  : 
Spam Check! Please answer this question  : 4 + 9 =
Comment