<!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","1101spring08read","1101readonly");
// select a database
mysql_select_db("1101spring08", $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://rynite.morris.umn.edu/~elenam/1101_spring08/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/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>
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","1101spring08read","1101readonly")))
die ("connection to the database failed");
// select a database
if (!@mysql_select_db("1101spring08", $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='problem set two'";
$q5 = "SELECT post_title,post_content FROM
wp_posts WHERE post_title LIKE 'Pro%' OR post_title LIKE '%!'";
$q6 = "SELECT MAX(comment_count) FROM wp_posts";
$q7 = "SELECT post_title, post_date FROM wp_posts WHERE
post_date < '2007-02-20 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
}
// need this in some cases
@mysql_close($connection);
?>
</pre>
</body>
</html>
http://rynite.morris.umn.edu/~elenam/1101_spring08/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","1101spring08read","1101readonly")))
die ("connection to the database failed");
// select a database
if (!@mysql_select_db("1101spring08", $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";
}
// need in some cases
@mysql_close($connection);
?>
</pre>
</body>
</html>
http://rynite.morris.umn.edu/~elenam/1101_spring08/database/assoc_results.php