PHP database connections

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