Examples of PHP database data processing

A simple database example

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


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

        foreach($row as $attribute)
        {
          print "$attribute\n";
        }
}
?>
</pre>
</body>
</html>
http://rynite.morris.umn.edu/~elenam/php_examples/database/db_connection.php

More advanaced 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/28/2006
-->
<html>
<head>
<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","readonly")))
	die ("connection to the dtabase failed");

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

// define queries
$q1 = "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;";

$q2 = "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 ORDER BY 
display_name;";

$q3 = "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 ORDER BY 
post_date DESC;";

$q4 = "SELECT post_title,post_content FROM 
wp_posts WHERE post_title='well-designed web pages'";

$q5 = "SELECT post_title,post_content FROM 
wp_posts WHERE post_title LIKE 'Wel%' OR post_title LIKE 'Ab%'";

$q6 = "SELECT MAX(comment_count) FROM wp_posts";

$q7 = "SELECT post_title, post_date FROM wp_posts WHERE
post_date < '2006-02-15 00:00:00'";

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
}
?>
</pre>
</body>
</html>
http://rynite.morris.umn.edu/~elenam/php_examples/database/db_connection_error_handling.php

Exercises

Processing data as an associative array, closing the connection


<!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/30/2006
-->
<html>
<head>
<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","readonly")))
	die ("connection to the database failed");

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

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 $title $formatted_date $num_comments $id1<br/>\n";
	print "$content<br/>\n";
}

// clean-up:

// free the memory used by the result - helps efficiency
@mysql_free_result($result);

// need this, unless using mysql_pconnect
@mysql_close($connection);
?>
</pre>
</body>
</html>
http://rynite.morris.umn.edu/~elenam/php_examples/database/more_db_processing.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.