Scrigroup - Documente si articole


HomeDocumenteUploadResurseAlte limbi doc
AccessAdobe photoshopAlgoritmiAutocadBaze de dateCC sharp
CalculatoareCorel drawDot netExcelFox proFrontpageHardware
HtmlInternetJavaLinuxMatlabMs dosPascal
PhpPower pointRetele calculatoareSqlTutorialsWebdesignWindows

AspAutocadCDot netExcelFox proHtmlJava
LinuxMathcadPhotoshopPhpSqlVisual studioWindowsXml

Tutorial - Data Retrieval with PostgreSQL


+ Font mai mare | - Font mai mic

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.




$db = pg_connect('dbname=friends');

$query = 'SELECT * FROM friends';

$result = pg_exec($db, $query);

if (!$result)

$numrows = pg_numrows($result);


printf ('<table border=1>');

printf ('<tr><td>ID</td><td>First Name</td><td>Surname</td></tr>');


while ($row < $numrows);

printf ('</table>');





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.


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.


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],


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



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.




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>







Politica de confidentialitate | Termeni si conditii de utilizare



Vizualizari: 792
Importanta: rank

Comenteaza documentul:

Te rugam sa te autentifici sau sa iti faci cont pentru a putea comenta

Creaza cont nou

Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved