Examples
Download the code you see in the column at the right, prepared by Pam Van Londen and James Holladay, OSU.
Readings
- PHP and MySQL for Dynamic Web Sites
- Larry Ullman. Visual QuickPro Guide. Peachpit Press. 2003. Read and try chapters 4 to 10.
- PHP.net
- Type in any php function name after the domain name to read what it means and how to use it.
A standard set of actions between PHP and MySQL is often structured like this in a PHP file:
1. Start the script.
<?php
/* This form allows the user to add a descriptive link to a database after preview and validation
and display it on the same page.
Written by Pam Van Londen and James Holladay in 2009.
*/
// 1. Set variables for the script.
$pagetitle = "Daily Painters ";
$subtitle = "Famous Artists";
//Set the default background color for each input field.
$firstnamecolor = '#fff';
$lastnamecolor = '#fff';
$websitenamecolor = '#fff';
$imagenamecolor = '#fff';
$bionamecolor = '#fff';
//Define global variables so their values can be accessed by other PHP tags.
global $valid;
global $submitted;
global $validFirstName;
global $validLastName;
global $validWebsite;
global $validImage;
global $validBio;
//initialize variable values
if(!isset($_POST['submit'])){//if the submit button was not yet pressed
$submitted = 0;
$valid = 0;//to stop the preview from showing when the page first loads with no content.
}
if(isset($_GET['submitted'])){//lets the preview division show that the entry has been added to the database
$submitted = 1;
}2. Include site-wide files.
// 2. Display the template header. include '../header.htm' ;
Open a connection to the database.
// Connect to the database.
require_once ('../../connect.php');3. Verify the form data.
// 3. Set up the preview validation.
//if the preview button is clicked, set $valid to 1, we will make it 0 if any part of validation fails. Set all other validation-related variables to 1 or true.
if(isset($_POST['preview'])){//if the preview button is clicked
$valid = 1;
$validFirstName = true;
$validLastName = true;
$validWebsite = true;
$validImage = true;
$validBio = true;
}
//Validate each field.
if(isset($_POST['first_name'])){
if(!ctype_alpha($_POST['first_name']) || empty($_POST['first_name'])){//must not be empty, must be alphabetic.
//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
$valid = 0;
$firstnamecolor = '#FFCC99';
$validFirstName = false;
}
}
if(isset($_POST['last_name'])){
if(!ctype_alpha($_POST['last_name']) || empty($_POST['last_name'])){//must not be empty, must be alphabetic.
//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
$valid = 0;
$lastnamecolor = '#FFCC99';
$validLastName = false;
}
}
if(isset($_POST['website'])){
$webURL = $_POST['website'];
if(@fopen($webURL,'r') === false || empty($_POST['website'])){//must not be empty, URL must exist.
//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
$valid = 0;
$websitecolor = '#FFCC99';
$validWebsite = false;
}
}
if(isset($_POST['image'])){
$imgURL = $_POST['image'];
if(@GetImageSize("$imgURL") === false || empty($_POST['image'])){//must not be empty, an image must exist at the specified URL.
//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
$valid = 0;
$imagecolor = '#FFCC99';
$validImage = false;
}
}
if(isset($_POST['bio'])){
if(empty($_POST['bio'])){//must not be empty.
//if not valid, change background color of input element, set pertinant validation variables to 0 or false.
$valid = 0;
$biocolor = '#FFCC99';
$validBio = false;
}
}3.1 Handle the data given by the visitor.
global $submitted;
// Handle the form when submit is clicked.
if(isset($_POST['submit'])){
if(!$submitted){//change the $submitted variable to 1, after clicking the submit button
$submitted = 1;
// Insert new data from the form into the database
// 3.1 Grab field data from the form and protect against SQL injections (stop any code inserted into the form field from running, which could erase our database tables).
$cleanFirstName = mysql_real_escape_string($_POST['first_name']);
$cleanLastName = mysql_real_escape_string($_POST['last_name']);
$cleanWebsite = mysql_real_escape_string($_POST['website']);
$cleanImage = mysql_real_escape_string($_POST['bio']);
$cleanBio = mysql_real_escape_string($_POST['image']);3.2 Construct SQL INSERT query.
// 3.2 Construct the query.
$insert = "INSERT INTO artists (user_id, first_name, last_name, website, bio, image, registration_date) VALUES (
'NULL',
'{$cleanFirstName}',
'{$cleanLastName}',
'{$cleanWebsite}',
'{$cleanImage}',
'{$cleanBio}',
NOW()
)";3.3 Execute SQL query, saving visitor data to the database.
// 3.3 Send the data to the database
if(@mysql_query($insert)){
print '<p class="alert">Entry added.</p>';
}
else{
die("<p>Could not add the entry because: it was probably a duplicate, or <b>" . mysql_error() . "</b>. The query was $query.</p>");
}
//this works to keep data from resubmitting on refresh, but no 'submitted' message will appear, and it requires javascript to be turned on.
echo "<script>window.location.replace('redirect.php?name=".$_POST['first_name']."');</script>";
}//end if for insert
}4. Add content from the database.
4.1 Query the database.
// 4. Display selected files from the table and order them. // 4.1 Display the list of artists in ascending order by last name. $show = 'SELECT * FROM artists ORDER BY last_name ASC';
4.2 Check for errors.
// 4.2 Run the query; if the table has rows fo data...
if($r = mysql_query($show)){4.3 Begin the <table> element.
// 4.3 Start a division, list, and/or table before the rows are displayed. echo '<div class="rightbox"><dl> ';
4.4 Loop through all records, adding a <tr> for each
// 4.4 Retrieve and print every record or row along with the list or table tags each needs.
while($row = mysql_fetch_array($r)){
print "<dt><a href='{$row['website']}' >{$row['last_name']}, {$row['first_name']}
<dd><img src='{$row['image']}' alt='{$row['bio']}' height='30' width='175' />
\n";
}4.5 Close the <table> element
// 4.5 End the list, table, and/or division. echo '</dl></div>'; }
5. If an error occured while inserting the form data into the database, add an error message.
// 5. Error - Query didn't run.
else{
die ('<p>Could not retrieve the data because: <b>' . mysql_error() . "</b>. The query was $show.</p>");
}// End of query IF.6. Close the database connection.
// 6. Close the database connection. mysql_close($dbc); // Close the php script. ?>
7. Add the form.
<!-- 7. display a form with validadtion, preview, and submit to database options. -->
<form name="addfamous" id="addfamous" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>" method="post">
<!--7.1 preview is hidden until preview button is clicked -->
<div id="previewFormData">
<?php
global $valid;
global $submitted;
if($submitted){//clear the form if it was submitted.
echo '<fieldset class="preview"><legend style="margin-top:30px;">Preview</legend><dl>';
print "<dt><p>Your Information has been submitted, ".$_GET['submitted'].".</p></dt>";
echo '</dl></fieldset>';
$valid = 0;
}
else if($valid == 1){//if the form is valid, but not yet submitted, write the content to the preview division.
echo '<fieldset class="preview"><legend style="margin-top:30px;">Preview</legend><dl>';
print "<dt><a href='{$_POST['website']}' style='color:#000;' >{$_POST['last_name']}, {$_POST['first_name']}</a></dt>
<dd><img src='{$_POST['image']}' alt='{$_POST['bio']}' height='30' width='175' /></dd>
\n";
echo '</dl>
<label for="submit"><input name="submit" id="submit" class="confirm" type="submit" value="Add to List" tabindex="y" accesskey="a" /></label>
</fieldset>';
}
?>
</div>
<!--7.2 form instructions-->
<p>Please use this form to add a famous artist, a link to their web site, and an image of their work. </p>
<p><span class="example">Required fields are marked with an orange <span class="alert">*</span>
and accesskeys are labeled with an <u>underline</u> and can be used with the Alt-Shift (Windows) or Control (Mac) keys.</span> </p>
<!-- 7.3 form fields-->
<fieldset>
<legend>Who do you consider famous?</legend>
<p><label for="first_name"><u>F</u>irst name <span class="alert"> *</span></label><br />
<input style="background-color:<?php echo $firstnamecolor?>" name="first_name" id="first_name" type="text" tabindex="1" accesskey="f" value="<?php echo $_POST['first_name']; ?>" size="20" maxlength="30" /><?php global $validFirstName; if (isset ($_POST['preview'])) { if(!$validFirstName){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['first_name'])){echo ' valid';}echo ' first name.</p>';}} ?> </p>
<p><label for="last_name">Last <u>n</u>ame <span class="alert"> *</span></label><br />
<input style="background-color:<?php echo $lastnamecolor?>" name="last_name" id="last_name" type="text" tabindex="2" accesskey="n" value="<?php echo $_POST['last_name']; ?>" size="20" maxlength="30" /><?php global $validLastName; if (isset ($_POST['preview'])) { if(!$validLastName){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['last_name'])){echo ' valid';}echo ' last name.</p>';}} ?> </p>
<p><label for="website"><u>W</u>ebsite address <span class="alert"> *</span> </label><br />
<input style="background-color:<?php echo $websitecolor?>" name="website" id="website" type="text" tabindex="3" accesskey="w" value="<?php echo $_POST['website']; ?>" size="20" maxlength="100" /><?php global $validWebsite; if (isset ($_POST['preview'])) { if(!$validWebsite){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['website'])){echo ' valid';}echo ' website address.</p>';}} ?> <br />
<span class="example">Example: http://www.site.com</span></p>
<p><label for="image"><u>I</u>mage of their work or portrait <span class="alert"> *</span> </label><br />
<input style="background-color:<?php echo $imagecolor?>" name="image" id="image" type="text" tabindex="4" accesskey="i" value="<?php echo $_POST['image']; ?>" size="20" maxlength="100" /><?php global $validImage; if (isset ($_POST['preview'])) { if(!$validImage){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['image'])){echo ' valid';}else{echo 'n';}echo ' image URL.</p>';}} ?> <br />
<span class="example">Example: http://www.site.com/picture.jpg</span></p>
<p><label for="bio"><u>B</u>iography (short) <span class="alert"> *</span> </label><br />
<textarea style="background-color:<?php echo $biocolor?>" name="bio" id="bio" tabindex="5" accesskey="b" cols="20" rows="6" ><?php echo $_POST['bio']; ?></textarea><?php global $validBio; if (isset ($_POST['preview'])) { if(!$validBio){echo '<p class="invalidData" style="color:#f00;">Please enter a'; if(!empty($_POST['bio'])){echo ' valid';}echo ' bio.</p>';}} ?> <br />
<span class="example">No html allowed; just plain text.</span>
<!-- note that textarea size attributes are rows and columns, not size -->
</p>
</fieldset>
<!--7.4 submit if preview is ok and all fields are valid -->
<fieldset>
<legend>Preview will show at the top of the page.</legend>
<label for="submit"><input name="preview" id="preview" class="validator" type="submit" value="Preview" tabindex="6" accesskey="v" />
to <u>v</u>iew your submission.</label>
</fieldset>
</form>8. Include the site template.
<!--8. end the template items -->
<?php
include ('../footer.htm');
?>