Inserting data into a database, updating data

Updating a record, inserting into a database

Note that the first time the code is run, it modifies the database. After that you need to delete the changes before running it again (or run it with a different data).
<!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: 4/3/2006
-->
<html>
<head>
<title>
Updating data in a database
</title>
</head>
<body>
<pre>
<?php
function showerror()
{
	die("Error ". mysql_errno(). " : " .mysql_error());	
}

// slightly modified from a function in
// "Php and MySQL" by Hugh Williams, David Lane (2nd edition)
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);
}

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

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

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 = 5, 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.