PostgreSQL & PHP Tutorials - Updating entries in your database - Page 2
PHP »
Updating your data
PHP » Starting Out » Updating your data
PostgreSQL » Updating your data
PostgreSQL » Starting Out » Updating your data
PHP » Starting Out » Updating your data
PostgreSQL » Updating your data
PostgreSQL » Starting Out » Updating your data
Posted By Chris Smith
Posted on 12 Feb 2006, 09:41 PM
This is our "update" script -
Again, we'll break this down again for understanding.
This connects to our database, as in every script we've written so far.
We've seen this before from the 'add' script. The 'id' is a little different. We're casting it to an integer. This means if we type in a non-numeric character (eg 'a' to 'z'), it will be converted to 0. This ensures that we always use a number.
This updates the firstname and the surname for the ID number we type in.
The syntax for the command is -
UPDATE database SET table1=value1, table2=value2, table3=value3, where tableX=valueX
So, in the database "contacts", we're setting the table names with the new information we put in. As long as we keep one value in our table the same, and have another one to refer to, it doesn't matter how many fields are in the rest of the database, we can update them all at once. That's why we're also using an ID field at the start. If we didn't use the ID field, we would only be able to update either the firstname or the surname each time, not both at the same time.
This updates the database with our new information.
If it doesn't work, it prints "ERROR" and exits the script so nothing else happens. This will be explained in a bit
more detail in the error section of our tutorial.
If it works, it displays the information we entered just to make sure we got it right. Normally, we'd have it display a message such as "successfully updated". For our purposes, just displaying this message is sufficient.
We have the usual end of script, to close the database connection,
end the PHP script, and finish the HTML code.
This is a very simple example of what we can do. If we ran the script that shows all of the data in the database again, we'd see that we've updated the firstname and the surname for the ID that we put in. The data comes in at the bottom of the list.
<html>
<body>
<?php
$db = pg_connect('host=localhost dbname=contacts user=contacts password=firstphp');
$firstname = pg_escape_string($_POST['firstname']);
$surname = pg_escape_string($_POST['surname']);
$emailaddress = pg_escape_string($_POST['emailaddress']);
$id = (int)$_POST['id'];
$query = "UPDATE friends SET firstname='$firstname', surname='$surname', emailaddress='$emailaddress' where id='$id'";
$result = pg_exec($db, $query);
if (!$result) {
printf ("ERROR");
$errormessage = pg_last_error();
echo $errormessage;
exit;
}
printf ("These values were updated in the database - %s %s %s %s",$id, $firstname, $surname, $emailaddress);
pg_close();
?>
</body>
</html>
Again, we'll break this down again for understanding.
<?php
$db = pg_connect('host=localhost dbname=contacts user=contacts password=firstphp');
?>
This connects to our database, as in every script we've written so far.
<?php
$firstname = pg_escape_string($_POST['firstname']);
$surname = pg_escape_string($_POST['surname']);
$emailaddress = pg_escape_string($_POST['emailaddress']);
$id = (int)$_POST['id'];
?>
We've seen this before from the 'add' script. The 'id' is a little different. We're casting it to an integer. This means if we type in a non-numeric character (eg 'a' to 'z'), it will be converted to 0. This ensures that we always use a number.
<?php
$query = "UPDATE friends SET firstname='$firstname', surname='$surname', emailaddress='$emailaddress' WHERE id='$id'";
?>
This updates the firstname and the surname for the ID number we type in.
The syntax for the command is -
UPDATE database SET table1=value1, table2=value2, table3=value3, where tableX=valueX
So, in the database "contacts", we're setting the table names with the new information we put in. As long as we keep one value in our table the same, and have another one to refer to, it doesn't matter how many fields are in the rest of the database, we can update them all at once. That's why we're also using an ID field at the start. If we didn't use the ID field, we would only be able to update either the firstname or the surname each time, not both at the same time.
<?php
$result = pg_query($query);
?>
This updates the database with our new information.
<?php
if (!$result) {
printf ("ERROR");
$errormessage = pg_last_error();
echo $errormessage;
exit;
}
?>
If it doesn't work, it prints "ERROR" and exits the script so nothing else happens. This will be explained in a bit
more detail in the error section of our tutorial.
<?php
printf ("These values were updated in the database - %s %s %s %s",$id, $firstname, $surname, $emailaddress);
?>
If it works, it displays the information we entered just to make sure we got it right. Normally, we'd have it display a message such as "successfully updated". For our purposes, just displaying this message is sufficient.
<?php
pg_close();
?>
</body>
</html>
We have the usual end of script, to close the database connection,
end the PHP script, and finish the HTML code.
This is a very simple example of what we can do. If we ran the script that shows all of the data in the database again, we'd see that we've updated the firstname and the surname for the ID that we put in. The data comes in at the bottom of the list.
Avg Rating: 5
Vote Count: 2
Vote Count: 2