PHP examples: Database insert/update

Changing data in a database (updates, additions)

Handling database password

Exposing a database password for changing/adding data is a security risk. The following steps describe a secure password setup:

Create a file with an extension .inc (e.g. access.inc) outside of your public_html.
In the file, between opening and closing php tags, set variables for the user name and password for accessing the database, for instance:
```
<?php

$user = "team55write";
$passwd = "OurAwesssomePasssword";

?>
```
You might want to add a print statement in the file for debugging purposes (it will print if the file is included correctly) and comment it out later.
Add a require in your code with the path to the file (see example below). Add ../ for every folder that you need to go out of.

Example of an update

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/8/10
-->
<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
// Including the file with the user name and password.
// The file must be outside of your public_html
// It must be readable only to you
require("../../../1101_includes/wordpress.inc");

function showerror()
{
  die("Error ". mysql_errno(). " : " .mysql_error());
}

   function display_posts($connection) {
     $q1 = "SELECT post_author, post_title, post_content, post_date FROM wp_posts WHERE post_type = 'post';";

     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);
   } // end display posts


// connect to the server using login name and password given in
// database.inc file
if (! ($connection = @mysql_connect("localhost",$user,$passwd))) {
  die ("connection to the database failed");
}

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

$newtitle = "Welcome to CSci 1101!";
$update_q1 = "UPDATE wp_posts SET post_title ='$newtitle' WHERE ID = 4;";

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

print "AFTER UPDATE\n";

display_posts($connection);

$newtitle = "Welcome to CSci 1101!";
$update_q1 = "UPDATE wp_posts SET post_title ='$newtitle' WHERE ID = 4;";

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

print "AFTER UPDATE\n";

display_posts($connection);

$insert_q1 = "INSERT INTO wp_posts SET post_title = 'Testing 1-2-3',
        post_author = 2, post_date = NOW(), post_content = 'Just a test'";


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

?>
</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/7/09
-->
<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
require("../../../1101_includes/wordpress.inc");

function showerror()
{
  die("Error ". mysql_errno(). " : " .mysql_error());
}

// connect to the server
if (! ($connection = @mysql_connect("localhost",$user,$passwd))) {
  die ("connection to the database failed");
 }

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

// When updating comment count, you want to make sure that
// no other script changes it after you read it and before you
// change it. Therefore you need to lock the tables.

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

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

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 = 4;";

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

// Unlock tables:

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

?>
</pre>
</body>
</html>

http://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/database/update_with_lock.php

UMM CSci 1101