HTML, PHP, and MySQL : Putting It All Together

HTML, PHP, and MySQL:Putting It All Together

Making a Form Input tags <input type=“text” /> Types: “text” “radio” “checkboxes” “submit”

Making a Form Put multiple “inputs” inside <form> </form> tags. Make the last one a “submit” button.

<form> <dl> <dt>Text: </dt> <dl><input type="text" /></dl> <dt>Buttons: </dt> <dd><input type="radio" value="Option 1" />Option 1 <input type="radio" value="Option 2" />Option 2</dd> <dt>Checkboxes: </dt> <dd><input type="checkbox" value="Option 1" />Option 1 <input type="checkbox" value="Option 2" />Option 2</dd> <dt>Submit button: </dt> <dd><input type="submit" value="submit" /></dd> </dl> </form>

Making the Form Useful Add attributes to the form tag: <form action=“sendToDatabase.php” method=“post”> ... </form> Add attributes to the input tags: <input type=“text” name=“email” /> We will need to make a PHPfile.

How a WAMP server gets PHP code to a client browser (Windows Apache MySQL PHP Web Server) WAMP Server a.php <? Echo “Hello”; ?> PHP Interpreter Rendered PHP In HTML format Request a.php Client Browser Rendered PHP In HTML format <html> <body> Hello </body> </html> My SQL Database Send back a.html

What our PHP file needs to do • Connect to our database • Get that data from the form • “Escape” the data so it’s safe • Send the data to the database

Beginning the PHP file <?php mysql_connect(“localhost”, “root”, “psswd”); Server Address Username Password

Getting the data from the form Recall the attributes we added to the form and the inputs: <form action=“sendToDatabase.php” method=“post”> <input type=“text” name=“email” /> $person = $_POST[`person`]; $email = $_POST[`email`];

Sending data to the database mysql_query(“INSERT INTO `mydatabase`.`table1` (`ID`, `Name`, `Email`) VALUES (NULL, $name, $email)”); Queries can perform many different functions, including adding to the database, reading from the database, or ...

Close the Connection mysql_close(); That’s all there is to it! After inserting the data into the database, put in the code for the page the user should see after sending information to the database, or use the header function to navigate to a different page. header(“Location: index.html”);

A Complete PHP File <?php mysql_connect(“localhost”, “root”, “psswd”); $person = $_POST[`person`]; $email = $_POST[èmail`]; mysql_query(“INSERT INTO `mydatabase`.`table1` (ÌD`, `Name`, Èmail`) VALUES (NULL, ‘$name’, ‘$email’)”); mysql_close(); header(“Location: index.html”); ?>

PHP Arrays A PHP array can store pairs of pieces of information (usually numbers or strings). In each pair, one piece of information is called the “key,” and the other is called the “value.” You can create a PHP array like this: $myarray = array( ‘breakfast’ => ‘eggs’, ‘lunch’ => ‘sandwich’, ‘dinner’ => ‘steak’); To retrieve one of the values, use square brackets and the correct key: $food = $myarray[‘breakfast’]”; echo “$food”;

PHP Arrays We used arrays earlier when using the POST method. $person = $_POST[`person`]; $email = $_POST[`email`]; PHP automatically created an array called $_POST with the information provided by the user. The ‘name’ attributes from the input tags became the keys of this array.

PHP Arrays To insert information into an array after it’s been created: $myarray[‘snack’] = ‘chips’; If you leave the key field blank, the keys will be integers, auto-incrementing from 0. $myarray[] = ‘Dwayne Wade’; $myarray[] = ‘Lebron James’; $myarray[] = ‘Chris Bosh’; $myarray[0] $myarray[1] $myarray[2]

PHP Arrays You can also omit keys when creating the array… $myarray = array(‘knicks’, ‘heat’, ‘bulls’, ‘cavs’); $myarray[2] or start with an empty array… $myarray = array()

PHP For Loops For loops let you run the same lines of code over and over again. Example: <?php $num = 2; for($i = 0; $i < 10; $i ++){ $num = $num + 3; } echo ‘$num’; ?>

PHP For Loops • There are three parts to the definition of a for loop in PHP: • Create a variable and give it a starting value. • Usually the variable will start at 0 • Set a condition for the variable. • i.e. $i < 10, or $i <= 10 • The for loop will not run unless the condition is true. • Specify how to change the variable after each iteration. • $i++ is shorthand for making $i go up by 1. • It’s the same thing as $i = $i + 1 • for($i = 0; $i < 10; $i ++)

Getting InformationFrom the Database Use the mysql_query() function again. $rs = mysql_query(“SELECT * FROM `mydatabase`.`mytable`”); Now $rs is a MySQL resource. It contains all the information we quested in our select query, but in order to use it, we need to convert it into PHP arrays. The mysql_fetch_array() function converts the first row of the resource into an array. $row1 = mysql_fetch_array($rs); The next time mysql_fetch_array() is called on $rs, it will return the second row. $row2 = mysql_fetch_array($rs);

mysql_fetch_array The keys of the array created by myql_fetch_arrray() can be either the column titles or the column indices (zero-indexed). $row1 = mysql_fetch_array($rs); $row2 = mysql_fetch_array($rs); $row1[“Name”] $row1[1] $row2[“Name”] $row2[1] “Lebron James” “Dwayne Wade”

Use a Loop… for($k = 0; $k < 2; $k++){ $row = mysql_fetch_array($rs); $name = $row[“Name”]; echo ‘$name’; }

mysql_num_rows The mysql_num_rows() function tells you how many rows are in your MySQL resource. $num = mysql_num_rows($rs); This can be especially useful when used to create a for loop: for($i = 0; $i < $num; $i++){ $row = mysql_fetch_array($rs); $name = $row[“Name”]; echo ‘$name’; }

A Complete PHP File <?php mysql_connect(“localhost”,”root”,”password”); $resource = mysql_query(“SELECT * FROM `mydatabase`.`mytable`”); $n = mysql_num_rows($resource); for($i = 0; $i < $n; $i++){ $row = mysql_fetch_array($rs); $name = $row[“Name”] echo ‘$name’; } mysql_close(); ?>

A Couple More Things to Know How to escape your inputs: $email = mysql_real_escape_string($_POST[`email`]); The difference between POST and GET: GET encodes information in the URL. POST encodes information where it can’t be seen by the user. Don’t use GET if it would be a problem if the user submitted the information twice!

Useful Links PHP Arrays: http://php.net/manual/en/language.types.array.php PHP For Loops: http://www.tizag.com/phpT/forloop.php List of PHP MySQL-related functions: http://www.php.net/manual/en/ref.mysql.php

HTML, PHP, and MySQL : Putting It All Together