PHP MYSQL DRIVEBY
Basic HTML
<html> <!-- Required for all HTML pages -->
<head>
<title>The Top Bar Title</title>
</head>
<body>
Some information
<!-- A Comment Tag -->
</body>
</html> <!-- Closing tag, required -->
Example 1
Basic HTML with PHP
PHP = Hypertext Preprocessor
<html> <!-- Required for all HTML pages -->
<head>
<title>The Top Bar Title</title>
</head>
<body>
Some information
<br> <!-- A line break -->
<!-- A Comment Tag -->
<? // Denotes the start of PHP processing
echo "The Date and Time is: "; // print a string, end with a semicolon
$mydata = "July 29, 2004"; // Assign a variable
echo $mydata; // print the string variable
?>
</body>
</html> <!-- Closing tag, required -->
Example 2
Take note of the different comment styles.
The "//" style within PHP and "<--" outside of PHP (normal HTML)
Variables begin with "$".
"=" are assignment.
"echo" is the print function.
";" ends line of code.
PHP files are saved with ".php" not ".html"
PHP Functions
Can occur anywhere in the page within "<? ?>" tags
<?
function printDate()
{
echo "The Date and Time is: ";
$mydata = "July 29, 2004";
echo $mydata;
}
?>
<html>
<head>
<title>The Top Bar Title</title>
</head>
<body>
<?
printDate();
?>
</body>
</html>
Example 3
Built-in PHP Functions
More than you can imagine: Everything from writing XML to creating images on the fly including creation of Flash files and running Java applications
PHP.net is your best friend
<?
function printDate()
{
echo "The Date and Time is: ";
$mydata = exec(date);
/* -- Multiline comment
"exec" is a built-in function that allows you to run any
unix command line command (and more). Running "date" on
the command line yeilds the following: Thu Jul 29 12:36:40 EDT 2004
*/
echo $mydata;
}
?>
<html>
<head>
<title>The Top Bar Title</title>
</head>
<body>
<?
printDate();
?>
</body>
</html>
Example 4
exec page from the PHP Manual
PHP with Forms
PHP is ideal for generating HTML forms and even more so for dealing with data that comes back from forms.
A Basic HTML Form
<html>
<head>
<title>A Basic HTML Form</title>
</head>
<body>
<form action="form_example.php" type="get">
First Name:<input type="text" name="first_name"><br>
Last Name:
<select name="last_name">
<option value="Smith">Smith</option>
<option value="Doe">Doe</option>
</select>
<br>
<input type="submit" value="Submit Me">
</form>
</body>
</html>
HTML Form Example
Form Processing with PHP
Dealing with data submitted via a form
<?
/*
Description: Helper function to get values from a form post (type="post" or query string (type="get")
Returns: value of key or null on failure
*/
function getPOSTorGETValue($key)
{
if (isset($_POST[$key]))
{
$value = $_POST[$key];
}
else if (isset($_GET[$key]))
{
$value = $_GET[$key];
}
else
{
$value = null;
}
return $value;
}
?>
<html>
<head>
<title>The Form Output</title>
</head>
<body>
<?
$first_name = getPOSTorGETValue("first_name");
$last_name = getPOSTorGETValue("last_name");
echo "Your First Name is: " . $first_name . "<br>";
echo "Your Last Name is: " . $last_name . "<br>";
?>
</body>
</html>
Example Form Output
PHP with MySQL
PHP and MySQL integrate very well together. A prime example of a PHP application working with the MySQL engine is phpMyAdmin.
Connecting to a pre-existing MySQL Database
<?
$hostname = "localhost";
$dbname = "your_database_name";
$username = "your_username";
$password = "your_password";
$mySql = mysql_pconnect($hostname, $username, $password) or die (mysql_error());
mysql_select_db($dbname, $mySql) or die(mysql_error());
?>
PHP MySQL Select
<?
function sqlConnect() {
# Configuration Variables
$hostname = "localhost";
$dbname = "your_database_name";
$username = "your_username";
$password = "your_password";
$mySql = mysql_pconnect($hostname, $username, $password) or die (mysql_error());
mysql_select_db($dbname, $mySql) or die(mysql_error());
return $mySql;
}
function sqlQuery($query)
{
global $mySql;
$data = null;
$result = mysql_query($query, $mySql);
# This set's up an associative array (key->value pair) for all of the data returned
if (sizeof($result) > 0)
{
$num_fields = mysql_num_fields($result);
$row_cnt = 0;
while ($row_data = mysql_fetch_array($result)) {
for ($cnt = 0; $cnt < $num_fields; $cnt++) {
$field_name = mysql_field_name($result, $cnt);
$data[$row_cnt][$field_name] = $row_data[$cnt];
}
$row_cnt++;
}
}
return $data;
}
$mySql = sqlConnect();
?>
<html>
<body>
<?
$query = "select first_name, last_name from names";
$someQueryResults = sqlQuery($query, $mySql);
for ($i = 0; $i < sizeof($someQueryResults); $i++)
{
echo "First Name = " . $someQueryResults[$i]['first_name'] . "<br>";
echo "Last Name = " . $someQueryResults[$i]['last_name'] . "<br><br>";
}
?>
</body>
</html>
Mysql Select Example
PHP MySQL Insert
<?
$hostname = "localhost";
$dbname = "your_database_name";
$username = "your_username";
$password = "your_password";
$mySql = mysql_pconnect($hostname, $username, $password) or die (mysql_error());
mysql_select_db($dbname, $mySql) or die(mysql_error());
$first_name = "Joe";
$last_name = "Smith";
$query = "insert into names (first_name, last_name) values ('$first_name', '$last_name')";
$result = mysql_query($query, $mySql);
echo "mySql = " . $mySql . "<br>";
echo "first_name = " . $first_name . "<br>";
echo "last_name = " . $last_name . "<br>";
echo "query = " . $query . "<br>";
echo "result = " . $result . "<br>";
?>
Mysql Insert Example
Integrating it all
<?
/*
Description: Helper function to get values from a form post (type="post" or query string (type="get")
Returns: value of key or null on failure
*/
function getPOSTorGETValue($key)
{
if (isset($_POST[$key]))
{
$value = $_POST[$key];
}
else if (isset($_GET[$key]))
{
$value = $_GET[$key];
}
else
{
$value = null;
}
return $value;
}
function sqlConnect() {
# Configuration Variables
$hostname = "localhost";
$dbname = "your_database_name";
$username = "your_username";
$password = "your_password";
$mySql = mysql_pconnect($hostname, $username, $password) or die (mysql_error());
mysql_select_db($dbname, $mySql) or die(mysql_error());
return $mySql;
}
function sqlQuery($query)
{
global $mySql;
$data = null;
$result = mysql_query($query, $mySql);
# This set's up an associative array (key->value pair) for all of the data returned
if (sizeof($result) > 0)
{
$num_fields = mysql_num_fields($result);
$row_cnt = 0;
while ($row_data = mysql_fetch_array($result)) {
for ($cnt = 0; $cnt < $num_fields; $cnt++) {
$field_name = mysql_field_name($result, $cnt);
$data[$row_cnt][$field_name] = $row_data[$cnt];
}
$row_cnt++;
}
}
return $data;
}
$mySql = sqlConnect();
?>
<html>
<head>
<title>Integrated Form</title>
</head>
<body>
<?
$submitted = 0;
$submitValue = getPOSTorGETValue("submit");
if (isset($submitValue) && $submitValue == "Submit Me")
{
$submitted = 1;
$first_name = getPOSTorGETValue("first_name");
$last_name = getPOSTorGETValue("last_name");
echo "You submitted: <br>";
echo "First Name: $first_name <br>";
echo "Last Name: $last_name <br>";
$query = "insert into names (first_name, last_name) values ('$first_name', '$last_name')";
$result = mysql_query($query, $mySql);
if ($result)
{
echo "<br>Database Insert Successfull <br>";
}
else
{
echo "i<br><blink>Database Insert Failed</blink> <br>";
}
}
?>
Existing Database Entries <br>
<?
$query = "select first_name, last_name from names";
$someQueryResults = sqlQuery($query, $mySql);
for ($i = 0; $i < sizeof($someQueryResults); $i++)
{
echo "First Name = " . $someQueryResults[$i]['first_name'] . "<br>";
echo "Last Name = " . $someQueryResults[$i]['last_name'] . "<br><br>";
}
?>
<br> <br>
Add New Entry <br>
<form action="mysql_form.php" type="get">
First Name:<input type="text" name="first_name"><br>
Last Name:
<select name="last_name">
<option value="Smith">Smith</option>
<option value="Doe">Doe</option>
</select>
<br>
<input type="submit" name="submit" value="Submit Me">
</form>
</body>
</html>
Integrated Form Example
SQL for MySQL
A good friend is the Online MySQL Manual
mysql Command Line Tool
$ mysql -u <username> -p
Enter password: <password>
mysql> use <database name>
Select
mysql> select * from names;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Shawn | Van Every |
| Joe | Smith |
| Joe | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| | Smith |
+------------+-----------+
10 rows in set (0.00 sec)
mysql> select * from names where first_name = 'Shawn';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Shawn | Van Every |
+------------+-----------+
1 row in set (0.01 sec)
mysql> select first_name, last_name from names where first_name = 'Shawn' or last_name = 'Smith';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Shawn | Van Every |
| Joe | Smith |
| Joe | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| Karen | Smith |
| | Smith |
+------------+-----------+
10 rows in set (0.01 sec)
Notes:
Always end line with ";".
Separate multiple items to select with comma
Put strings in single quote
Single "=" for comparison
Use "and" and "or" instead of "||" and "&&"
Insert
mysql> insert into names (first_name, last_name) values ('John', 'Better');
Query OK, 1 row affected (0.00 sec)
Delete
mysql> delete from names where first_name = 'Karen' and last_name = 'Smith';
Query OK, 6 rows affected (0.01 sec)
Update
mysql> update names set first_name = "Matt" where last_name = "Van Every";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
If there is time
HTTP Query Strings
SQL Joins
phpMySQL
Create Table
Alter table
Questions and Comments
Email me: vanevery@walking-productions.com