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