Changing data in a database
Example of UPDATE and INSERT queries
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Connecting to a database
Author: Elena Machkasova
Last Modifed: 3/28/2006
-->
<html>
<head>
<title>
Updating data in a database
</title>
</head>
<body>
<pre>
<?php
function showerror()
{
die("Error ". mysql_errno(). " : " .mysql_error());
}
function mysqlclean($data, $maxlength, $connection) {
$data = substr($data, 0, $maxlength); // chop off extra characters
// automatically inserting escapes where needed
$data = mysql_real_escape_string($data, $connection);
return $data;
}
function display_posts($connection) {
$q1 = "SELECT * FROM wp_posts;";
if (! ($result = @mysql_query($q1, $connection))) {
showerror();
}
while($row = @mysql_fetch_array($result,MYSQL_NUM))
{
foreach($row as $attribute)
{
print "$attribute\n";
}
print "<hr/>\n"; // added for readability
}
@mysql_free_result($result);
}
// note: the passowrd is available in the file
// ~elenam/1101_fall06/php_examples/database/db_insert.php
$password = "";
// connect to the server
if (! ($connection = @mysql_connect("localhost","1101",$password)))
die ("connection to the database failed");
// select a database
if (!@mysql_select_db("1101fall06", $connection)) showerror();
// WARNING: EXTREME CARE MUST BE TAKEN WHEN INSERTING DATA
// INTO THE DATABASE OR UPDATING INFORMATION
print "BEFORE THE UPDATE:\n";
display_posts($connection);
// LOCKING THE TABLE BEFORE UPDATE
$lock_q = "LOCK TABLES wp_posts WRITE";
if (! (@mysql_query($lock_q, $connection))) {
showerror();
}
$update_q1 = "UPDATE wp_posts SET post_title ='Greetings!' WHERE ID = 7;";
if (! (@mysql_query($update_q1, $connection))) {
showerror();
}
$unlock_q = "UNLOCK TABLES";
print "AFTER UPDATE\n";
if (! (@mysql_query($unlock_q, $connection))) {
showerror();
}
display_posts($connection);
// INSERTING INTO THE DATABASE
if (! ($result = @mysql_query($lock_q, $connection))) {
showerror();
}
$insert_q1 = "INSERT INTO wp_posts SET post_title = 'Test post',
post_author = 18, post_date = NOW(), post_content = 'Just a test'";
if (! (@mysql_query($insert_q1, $connection))) {
showerror();
}
display_posts($connection);
if (! (@mysql_query($unlock_q, $connection))) {
showerror();
}
?>
</pre>
</body>
</html>
UMM CSci 1101