Examples of PHP database access and functions

Connecting to the database


<!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/25/2008
-->
<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>
Connecting to a database
</title>
</head>
<body>
<pre>
<?php
						// connect to the server
$connection = mysql_connect("localhost","1101readonly","1101spr09readonly");
// select a database

mysql_select_db("1101spr09", $connection);

$result = mysql_query("SHOW TABLES;",$connection);
//$result = mysql_query("DESCRIBE wp_posts;",$connection);
//$result = mysql_query("SELECT * FROM wp_users;", $connection);
//$result = mysql_query("SELECT wp_users.display_name FROM wp_users;", $connection);


while($row = mysql_fetch_array($result,MYSQL_NUM))
  {

    foreach($row as $attribute)
      {
        print "$attribute\n";
      }
  }

// generally don't need it but may need for some error conditions
mysql_close($connection);
?>
</pre>
</body>
</html>

http://csci1101sp09.morris.umn.edu/~elenam/1101_spring09/database/connect.php


More queries and error handling


<!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/24/2009
-->
<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>
Error handling when connecting to a database
</title>
</head>
<body>
<pre>
<?php
function showerror()
{
  die("Error ". mysql_errno(). " : " .mysql_error());
}

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

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

// define queries
//select the title and the contents of the post with the
// title "Greetings!"
$q1 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title='Greetings!'";

// Find all post entries whose title starts with "Pro" or ends with "!" 
// Note that this returns all revisions of each post separately
$q2 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title LIKE 'Pro%' OR post_title LIKE '%!'";

// selecting all posts with a title that ends with "!"
// only the final versions will be selected, not
// intermediate revisions
$q3 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title LIKE '%!' AND post_type = 'post'";

// select the maximal number of comments to a post 
$q4 = "SELECT MAX(comment_count) FROM wp_posts";

// select all posts posted before February
$q5 = "SELECT post_title, post_date FROM wp_posts WHERE
post_date < '2009-02-01 00:00:00' AND post_type = 'post'";

// getting information from two tables:
// We are getting user display name from the wp_users table
// Note that we have to relate the two tables by matching
// the post author ID in wp_posts with the ID in wp_users
// table 
$q6 = "SELECT wp_users.display_name,wp_posts.post_title,
wp_posts.post_date,wp_posts.post_content,wp_posts.comment_count
FROM wp_posts,wp_users WHERE wp_posts.post_author = wp_users.ID
AND post_type = 'post';";

// same query, but sorted by name alphabetically
$q7 = "SELECT wp_users.display_name,wp_posts.post_title,
wp_posts.post_date,wp_posts.post_content,wp_posts.comment_count
FROM wp_posts,wp_users WHERE wp_posts.post_author = wp_users.ID 
AND post_type = 'post' ORDER BY display_name;";

// same one, but sorted by date in reverse-chronological order
$q8 = "SELECT wp_users.display_name,wp_posts.post_title,
wp_posts.post_date,wp_posts.post_content,wp_posts.comment_count
FROM wp_posts,wp_users WHERE wp_posts.post_author = wp_users.ID
AND post_type = 'post'  ORDER BY post_date DESC;";

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
  }

// need this in some cases
@mysql_close($connection);

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

http://csci1101sp09.morris.umn.edu/~elenam/1101_spring09/database/queries.php


Processing data as an associative array


<!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/25/2008
-->
<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>
Accessing data as an associative array
</title>
</head>
<body>
<pre>
<?php
function showerror()
{
        die("Error ". mysql_errno(). " : " .mysql_error());
}

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

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

// define the query
$q1 = "SELECT wp_posts.ID, wp_users.display_name,wp_posts.post_title,
wp_posts.post_date,wp_posts.post_content,wp_posts.comment_count
FROM wp_posts,wp_users WHERE wp_posts.post_author = wp_users.ID
AND post_type = 'post';";

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

// access elements by column (field) names
while ($row = @mysql_fetch_assoc($result)) {
        // only one field with name ID can be accessed!
        $id1 = $row['ID'];
        // careful: $row['wp_users.display_name'] doesn't work
        $user = $row['display_name'];
        $title = $row['post_title'];

        $date = $row['post_date'];
        // converting date into a form understood by php:
        $date = strtotime($date);
        // formatting the date:
        $formatted_date = strftime("%a, %B %d %Y %I:%M%p", $date);

        $content = $row['post_content'];
        $num_comments = $row['comment_count'];
        print "$user posted a post titled \"$title\" on $formatted_date <br />\n"; 
        print "The post has $num_comments comment(s) and id = $id1<br/>\n";
        print "$content<br/>\n";
	print "<hr />\n";
}

// need in some cases
@mysql_close($connection);
?>
</pre>
</body>
</html>

http://csci1101sp09.morris.umn.edu/~elenam/1101_spring09/database/database_assoc.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.