Project C-1 Displaying and Inserting Data in MySQL


You can observe many of the basic PHP syntax features by coding the Hello World test. You can study the client/server environment in which it runs. Most important, however, is that you will be able to see the integration of PHP with HTML. You will then proceed to use PHP to read and display the contents of a MySQL table in a web page, and then use a web page interface to insert data into a MySQL table.

Note  

To do this project, you will need the database you created in the projects in Modules 1 through 5.

Step by Step

  1. Open a word processor that allows you to save a file in plain text. Enter the following PHP code, which is embedded in an HTML page.

     <HTML>    <BODY>       <?php          $howdy = "Hello World:   " . date("m/d/y   -   H:m:s");          echo $howdy;       ?>    </BODY> </HTML> 
  2. Save the page in your Internet directory (/Inetpub/ wwwroot / for Windows and /public_html/ for Linux) as HW1.php .

    Caution  

    Some word processors will automatically put a file type extension on your filename, such as .txt or .doc. If this happens, you must manually remove it, because the filename extension must be .php in order for the web server and browser to interpret it correctly. Doing this may cause a warning to pop up, cautioning against changing file type extensions. Indicate that you do want to change the file type.

  3. Open your web browser to the localhost or public_html directory and view the web page, which should return the traditional message, Hello World, followed by the current date and time.

  4. The following code adds an HTML form with a submit button to create a variable for user input of a temperature in centigrade, convert if to Fahrenheit, and display it. Alter the HW1.php file in a word processor and save it as HW2.php .

     <HTML>    <BODY>    <FORM METHOD="POST">       Enter Centigrade:       <INPUT TYPE="TEXT" NAME="centigrade">       <INPUT TYPE="SUBMIT">    </FORM> <BR> <?php    $howdy="Hello World: ". date("m/d/y   -   H:m:s");    echo $howdy."<br>";    if(isset($_POST["centigrade"]))       {       $ctemp=$_POST["centigrade"];       $ftemp=32+$ctemp*9/5;       echo $ctemp." Centigrade = ".$ftemp." Fahrenheit";       } ?>    </BODY> </HTML> 
  5. View the web page, enter the value 45 into the form box, and click the submit button. You ll see the web page change. You can also try entering 0 centigrade, which equals 32 Fahrenheit, for verification.

  6. Enter the following code in your word processor and save it as dbconnect .php in the Internet directory.

     <?php    unset($dbfailed);    if(mysql_connect("localhost","root"))       {       mysql_select_db("duckwear")or $dbfailed=mysql_error();       }    else       {       $dbfailed="Could not connect to database";       }    if(isset($dbfailed))       {       echo $dbfailed;       } ?> 

    This text creates a routine for connecting to your MySQL database and indicates what the program should do if it cannot connect. It is included in the file you will create in the next step.

  7. Type the following code into a file and save the file as usedata.php in your Internet directory.

     <?php    include 'dbconnect.php'; ?> <HTML>    <BODY> <?php    $rs=mysql_query("select * from duck_sales")    or die(mysql_error());    $num=mysql_num_rows($rs);    echo "There are $num rows<BR>"; ?>    <h1>Sales Report</H1> <TABLE cellpaddig=2 border=1> <TR><TH>Design</TH><TH>Winter</TH><TH>Spring</TH>    <TH>Summer</TH><TH>Fall</TH></tr> <?php    while($row=mysql_fetch_array($rs))       {       echo  "<TR><TD>$row[design_name]</TD><TD>$row[winter_sales]</TD>        <TD>$row[spring_sales]</TD><TD>$row[summer_sales]</TD>         <TD>$row[fall_sales]</TD></TR>";       } ?> </TABLE>    </BODY> </HTML> 
  8. View the page in your web browser. It should display the contents of your duck_sales table in your duckwear database.

  9. Enter the following code into a file and save it as enterdata.php .

     <?php    include 'dbconnect.php';    ?> <html>    <body>       <h1>Enter Design</h1>    <form method="post">    Design:<input type="text" name="design"><br>    Category:<input type="text" name="cat"><br>    Winter:<input type="text" name="winter"><br>    Spring:<input type="text" name="spring"><br>    Summer:<input type="text" name="summer"><br>    Fall:<input type="text" name="fall"><br>    <input type="submit">    </form>    <?php    if(isset($_POST["design"])){       $sql = "insert into duck_sales          values(0,'".$_POST["design"]."',".$_POST["winter"].",          ".$_POST["spring"].",".$_POST["summer"].",".$_POST["fall"].",          '".$_POST["cat"]."')";    if(mysql_query($sql)){       echo "record inserted";       }else{       echo mysql_error();       }       }    ?>    </body> </html> 
  10. View the page in your web browser. As you can see, it will allow you to insert records into the duck_sales table in the duckwear database.

    Caution  

    To express these concepts simply, this script allows user-provided input to be placed directly into a SQL query. Under no circumstances should this be done in a real-world script. It produces a condition called SQL injection and can be a serious security problem. Once you understand the PHP basics in this appendix, return to the advanced topics in Module 7, which will cover how to avoid SQL injection.

  11. Insert a new record using the following information: Rock_Duck , Profession , 98 , 87 , 76 , 65 . Then press the ENTER key. A Record Inserted statement will appear.

  12. In your browser, return to the usedata.php page. If necessary, click the Refresh button. The page should show the Rock_Duck addition to the duck_sales table.

Project Summary

This project gave you experience with creating PHP code to put text on a web page. You also saw how to post data to and from a web page, use a web page interface to view data in a MySQL database, and insert data into a MySQL database.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net