PHP I : Database Connections

Follow this lesson in Ullman Chapter 12. The scripts are located in the 12 directory.

For this discussion, you will need to create a testing file. I suggest you call it test.php and save it in your local web root.

As we discuss each code block, you can paste it into test.php and view it (run it) in your browser.

Connecting to MySQL

$dbc = mysql_connect (‘hostname’, ‘username’, ‘password’)

Set Up These Values In An Included File That Is Outside Your Web Root!

The dbcon.php file:

<?php
$server = ‘localhost’; // DATABASE SERVER
$database = ‘myblog’; // DATABASE NAME
$user = ‘root’; // USER NAME
$password = ”; // USER PASSWORD
?>

Specifying dbcon.php as an include in your PHP code:

<?
include (‘../../../dbcon.php’);
….
?>

See the script mysql_connect.php in the 12 directory.

MySQL Error Handling

mysql_err()

die()

Ullman’s Script:

// Attempt to connect to MySQL and print out messages.
if ($dbc = mysql_connect (‘localhost’, ‘root’, ”)) {

print ‘Successfully connected to MySQL.’;

mysql_close(); // Close the connection.

} else {

print ‘Could not connect to MySQL.’;

Ullman’s Script with Error Handling:

// Attempt to connect to MySQL and print out messages.
if ($dbc = @mysql_connect (‘localhost’, ‘root’, ”)) {

print ‘Successfully connected to MySQL.’;

mysql_close(); // Close the connection.

} else {

die (‘Could not connect to MySQL because: ’ . mysql_error() . ‘’);

}

Note the use of “@” and mysql_error().

Creating a Database

Note that you have to connect to the MySQL server before you can create a database within it.

Ullman’s DB Creation Script: (edited)

if (@mysql_query (‘CREATE DATABASE myblog’)) {
print ‘The database has been created.’;
} else {
die (‘Could not create the database because: ’ . mysql_error() . ‘’);
}

Selecting a Database

Note that you have to connect to the MySQL server before you can select a database within it.

Ullman’s Select Script: (edited)

//Attempt to select a database, after connecting
if (@mysql_select_db (‘myblog’)) {
print ‘The database has been selected.’;
} else {
die (‘Could not select the database because: ’ . mysql_error() . ‘’);
}

Creating a Table

Note that you have to connect to the MySQL server and select a database before you can create or access tables.

Ullman’s Table Creation Script: (edited)

// Define the query.
$query = ‘CREATE TABLE blog_entries (
blog_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
entry TEXT NOT NULL,
date_entered DATETIME NOT NULL
)’;

// Run the query.
if (@mysql_query ($query)) {
print ‘The table has been created.’;
} else {
die (‘Could not create the table because: ’ . mysql_error() . ‘.The query being run was: ‘ . $query . ‘’);
}

INSERT INTO: Adding Data

Insert by specifying column names; do this if you don’t know column positions:

INSERT INTO tablename (column1_name, column2_name, column3_name) values (‘value1’, ‘value2’, ‘value3’)

Insert using column positions; it’s easier, but you must insert data in all columns, and in the correct order:

INSERT INTO tablename values (‘value1’, ‘value2’, ‘value3’)

SELECT: Getting Data

Note that you have to connect to the MySQL server and select a database before you can create or access tables.

Ullman’s Select Script: (edited)

// Define the query.
$query = ‘SELECT title, entry FROM blog_entries ORDER BY date_entered DESC’;

if ($r = mysql_query ($query, $dbc)) { // Run the query.

// Retrieve and print every record.
while ($row = mysql_fetch_array ($r)) {
print “{$row[‘title’]} {$row[‘entry’]} ”;
}

} else { // Query didn’t run.
die (‘Could create the table because: ’ . mysql_error() . “. The query was $query.”);
}

DELETE: Deleting Data

Note that you have to connect to the MySQL server and select a database before you can create or access tables.

Ullman’s Delete Script: (edited)

// Define the query.
$query = “DELETE FROM blog_entries WHERE blog_id={$_POST[‘id’]} LIMIT 1″;
$r = mysql_query ($query); // Execute the query.

// Report on the result.
if (mysql_affected_rows() == 1) {
print ‘The blog entry has been deleted.’;
} else {
print “Could delete the entry because: ” . mysql_error() . “. The query was $query.”;
}

UPDATE: Changing Existing Data

Ullman’s Update Script: (edited)

// Define the query.
$query = “UPDATE blog_entries SET title='{$_POST[‘title’]}’, entry='{$_POST[‘entry’]}’ WHERE blog_id={$_POST[‘id’]}”;
$r = mysql_query ($query); // Execute the query.

// Report on the result.
if (mysql_affected_rows() == 1) {
print ‘The blog entry has been updated.’;
} else {
print “Could update the entry because: ” . mysql_error() . “. The query was $query.”;
}

Close Your Database Connection!

ALWAYS close your database connection at the end of your script!

mysql_close()

That’s all it takes.

To do out of class:

Review Chapter 12 of Ullman.