| CATEGORII DOCUMENTE |
| Asp | Autocad | C | Dot net | Excel | Fox pro | Html | Java |
| Linux | Mathcad | Photoshop | Php | Sql | Visual studio | Windows | Xml |
Tutorial - Data Retrieval with PostgreSQL
We only have a small database so far, but we'll work on that.
Since we only have 2 lines of data in our database, if you put
$row = pg_fetch_row ($result,2);
your output wouldn't work. It would output something like this :
Warning: Unable to jump to row 2 on PostgreSQL result index 2
We'll add some more data into our database now. Create a file called input2 and put this in it.
INSERT INTO friends values (nextval ('friends_id_seq'), 'My', 'Name');
INSERT INTO friends values (nextval ('friends_id_seq'), 'His', 'Name');
INSERT INTO friends values (nextval ('friends_id_seq'), 'Her', 'Name');
If the lines wrap to the next line, make sure the insert commands are on new lines. It won't work if they aren't.
Now, we run the commands to insert the values into the database.
postgres@designmagick:/usr/local/pgsql > psql -d friends -f input2
You shouldn't get any errors doing this.
Our next test will be to get all of the data out of our database in one step. We need to create a new PHP file to do it.
<html>
<body>
<?php
$db = pg_connect('dbname=friends');
$query = 'SELECT * FROM friends';
$result = pg_exec($db, $query);
if (!$result)
$numrows = pg_numrows($result);
$row=0;
printf ('<table border=1>');
printf ('<tr><td>ID</td><td>First Name</td><td>Surname</td></tr>');
do
while ($row < $numrows);
printf ('</table>');
pg_close();
?>
</body>
</html>
This is where it starts being different to MySQL, and a little more complicated. With PostgreSQL, you have to tell it which row
to use when it finds a result. We're looking for all of the data in the database, so we need to know how many rows are in the
database first.
$numrows = pg_numrows($result);
This tells us how many rows there are from $result. Since
$result = pg_exec($db, $query);
and $query = 'SELECT * FROM friends';
$numrows is the number of rows from the query, which in our case is the number of entries in the database. From the first
tutorial, and the start of this one, we know it's 5.
$row=0;
We need to tell it where to start, at row 0.
printf ('<table border=1>');
printf ('<tr><td>ID</td><td>First Name</td><td>Surname</td></tr>');
This is just setting up our table output. Doing it in a table makes it easier to read. If you don't want to do it in a table, you
can change the code to something more suitable for you.
do
while ($row < $numrows);
This is where the 'work' is actually done.
$myrow = pg_fetch_row ($result,$row);
This line actually retrieves the data from the database, depending on the $row number. Since $row = 0 from above, it gets the
first row.
printf ('<tr><td>%s</td><td>%s</td><td>%s</td>', $myrow[0], $myrow[1],
$myrow[2]);
This line prints it out, into the table elements.
These lines can actually be written differently. Instead of using a pg_fetch_row, we can use a pg_fetch_array. How?
$myrow = pg_fetch_array($result, $row);
What's the advantage of doing it this way instead? Instead of using the element numbers in the above example, we can use the
element (or field) names.
printf ('<tr><td>%s</td><td>%s</td><td>%s</td>', $myrow['id'],
$myrow['firstname'], $myrow['surname']);
This makes it a lot easier to specify which fields we want, especially in larger databases where we have lots of different field
names.
$row++;
This adds 1 to $row, so now it becomes $row = 1.
while ($row < $numrows);
while the $row we're fetching is less than the total number of rows, go back and fetch the next row and print it out (ie go to
the top of the loop and start again). Now we'll look at the do .. while loop. In our case, this means in plain English, 'get
all of the data in each row until you reach the total number of rows'.
The rest of the code is fairly simple.
printf ('</table>');
This closes the <table> that we're printing everything out to.
?>
</body>
</html>
This is the standard end of the PHP file, and closes the HTML file.
Our output prints like this in a table -
<table border='1'>
<tr><td>ID</td><td>First Name</td><td>Surname</td></tr>
<tr><td>1</td><td>Test</td><td>User</td></tr>
<tr><td>2</td><td>Your</td><td>Name</td></tr>
<tr><td>3</td><td>My</td><td>Name</td></tr>
<tr><td>4</td><td>His</td><td>Name</td></tr>
<tr><td>5</td><td>Her</td><td>Name</td></tr>
</table>
|
Politica de confidentialitate | Termeni si conditii de utilizare |
Vizualizari: 875
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
