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

The views and opinions expressed in this page are strictly those of the page author. The contents of this page have not been reviewed or approved by the University of Minnesota.