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("1101fall06", $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_pos
ts.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";
}
}
// 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/1101_fall06//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("1101fall06", $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
}
// 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//1101_fall06/php_examples/database/db_connection_error_handling.php
Exercises
- Write a query to display all posts (titles, contents, and the
number of comments) that anyone has commented on (hint: use
comment_count field)
- Display all comments for the post whose title starts with "Loops"
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/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("1101fall06", $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/1101_fall06//php_examples/database/more_db_processing.php
UMM CSci 1101