<!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/11/2010
-->
<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","spring1101read");
// select a database
mysql_select_db("wordpress", $connection);
$result = mysql_query("SHOW TABLES;",$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_ASSOC))
{
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://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/database/connect.php
<!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","spring1101read")))
die ("connection to the database failed");
// select a database
if (!@mysql_select_db("wordpress", $connection)) showerror();
// define queries
//select the title and the contents of the post with the
// title "Project groups"
$q1 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title='Project groups'";
// select only final posts, no previous revisions
$q2 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title='Project groups' AND post_type ='post'";
// Find all post entries whose title starts with "Pro" or its contents has "!"
$q3 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title LIKE 'Pro%' OR post_content 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_content, post_date FROM wp_posts WHERE
post_date < '2010-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_content,
wp_posts.post_date,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 by user display name
$q7 = "SELECT wp_users.display_name,wp_posts.post_content,
wp_posts.post_date,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_content,
wp_posts.post_date,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_ASSOC))
{
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://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/database/queries.php
<!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","spring1101re\
ad")))
die ("connection to the database failed");
// select a database
if (!@mysql_select_db("wordpress", $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://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/database/database_assoc.php