Examples of adding data to a database

Note that no link to the file is provided since clicking on the link would actually add data to the database. The file name is update.php in database directory.


<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<!--
Changing data in a database
Author: Elena Machkasova
Last Modifed: 4/10/08
-->
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<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/public_html/1101_spring08/database/update.php
$password = "";

// connect to the server
if (! ($connection = @mysql_connect("localhost","1101spr08write",$password)))
        die ("connection to the database failed");

// select a database
if (!@mysql_select_db("1101spring08", $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);

// May need to lock tables if use multiple queries

// old title: 
$title = "Welcome to CSci 1101!";
//$title = "Greetings!";
$update_q1 = "UPDATE wp_posts SET post_title ='$title' WHERE ID = 1;";

if (! (@mysql_query($update_q1, $connection))) {
        showerror();
}

print "AFTER UPDATE\n";

display_posts($connection);

$insert_q1 = "INSERT INTO wp_posts SET post_title = 'Test post',
        post_author = 2, 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>

Locking tables

The script below updates comment count to the actual number of comments


<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<!--
Changing data in a database
Author: Elena Machkasova
Last Modifed: 4/10/08
-->
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<title>
Updating data in a database
</title>
</head>
<body>
<?php
function showerror()
{
        die("Error ". mysql_errno(). " : " .mysql_error());
}


// note: the passowrd is available in the file 
// ~elenam/public_html/1101_spring08/database/update.php
$password = "1101readwrite";

// connect to the server
if (! ($connection = @mysql_connect("localhost","1101spr08write",$password)))
        die ("connection to the database failed");

// select a database
if (!@mysql_select_db("1101spring08", $connection)) showerror();

// WARNING: EXTREME CARE MUST BE TAKEN WHEN INSERTING DATA
// INTO THE DATABASE OR UPDATING INFORMATION

// LOCKING THE TABLES: count the number of comments,
// update the wp_posts table

$lock_q = "LOCK TABLES wp_comments READ, wp_posts WRITE;";
$unlock_q = "UNLOCK TABLES;";

if (! (@mysql_query($lock_q, $connection))) {
        showerror();
}

// get the comment count for post with ID 3

$count_comment_q = "SELECT COUNT(*) FROM wp_comments WHERE comment_post_ID = 3;";

if (! ($result = @mysql_query($count_comment_q, $connection))) {
  showerror();
}

// process the comment count. Note that it is a table with one element

if ($row = @mysql_fetch_array($result,MYSQL_NUM)) {
  // successfully got the result:
  $comment_count = $row[0];
  print "<p>The post has $comment_count comments</p>";
} else {
  // failed to get the result:
  showerror();
}

// update the comment count:

$update_q1 = "UPDATE wp_posts SET comment_count = $comment_count WHERE ID = 3;";

if (! (@mysql_query($update_q1, $connection))) {
        showerror();
}

// Unlock tables:

if (! (@mysql_query($unlock_q, $connection))) {
        showerror();
}

print "<p>Update successful. Check your blog at <a href=\"http://rynite.morris.umn.edu/wordpress/?p=3#comments\">http://rynite.morris.umn.edu/wordpress/?p=3#comments</a></p>";

?>
</pre>
</body>
</html>
http://rynite.morris.umn.edu/~elenam/1101_spring08/database/update_with_lock.php

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.