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>
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>