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'); ?>