Examples of PHP database access and functions


<!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("1101spr07", $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_spring07/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/28/2007
-->
<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 database failed");

// select a database
if (!@mysql_select_db("1101spr07", $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_spring07/database/sql_error.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: 4/2/2007
-->
<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("1101spr07", $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_spring07/database/assoc_result.php

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.