Order Form Part 11: The PHP Script -- The MySQL Database

By Vince Barnes

Before we get to actually adding our data to our MySQL database there is something else we need to consider.  Since we have at least one text field in our data there is always the possibility that it could contain an apostrophe, or quote mark.  Such a thing in the data would be interpreted by MySQL as a delimiter rather than as part of the data, with most undesirable results.

There are two things that we could do about this.  Either one would work just fine, but both together will create another set of problems!  The basic idea is to add an escape character (a backslash) in front of each character that would be misinterpreted to tell MySQL to treat the character as a simple character.  Of course, we only want the one escape character!

The first is known as "magic quotes".  Magic quotes is an option in the configuration on the server.  If you don't control the server then you will have to live with the setting whichever way it is.  Most are set on.  PHP is here to help!  PHP includes the means for you to determine whether magic quotes is set on or off.  We'll take advantage of that.

PHP also has the means to add the needed backslashes.  So, what we'll do is check for magic quotes and if they're on, we're all set, and if not we'll add our backslashes ourselves.  We'll add the required code at the front of our script, just following that debug aid we mentioned before, like this:

<?
//uncomment for debugging
//print_r($_POST);
 

//most sites have magic quotes on
//but if they do not, this code simulates magic quotes
if( !get_magic_quotes_gpc() )
{
    if( is_array($_POST) )
        $_POST = array_map('addslashes', $_POST);
}
 

That'll take care of it!  Now we can add the code to save the order information into our MySQL database.  Here is the code for this (note that it goes at the end of our PHP script and closes it out):

//we can store the order in a database as well
 
$link = @mysql_connect('servername', 'username', 'password');
if (!$link)
{
   echo "Could not connect: " . mysql_error();
}
else
{
    mysql_select_db('my_database');
 
    $query  = "INSERT INTO order_queue
             (  Name ,   Email ,   OtherInfo ,   qtyA ,
                totalA ,   qtyB ,   totalB ,   qtyC ,   totalC ,   GrandTotal )";
    $query .= " VALUES
             ('$Name', '$Email', '$OtherInfo', '$qtyA',
              '$totalA', '$qtyB', '$totalB', '$qtyC', '$totalC', '$GrandTotal')";
    //echo $query . "<br>\n";
 
    $result = mysql_query($query);
    mysql_free_result($result);
    mysql_close($link);
}
?>

Of course, in the real thing "servername", "username" and "password" would be replaced with the actual names which, hopefully, are a little more secure than these examples would be!  We've also shown in here how you could use an echo as a debugging aid -- in our script we have, of course, commented it out.

Now that the PHP script is complete, perhaps you'd like to see it all in one place, so here it is:

<?
//uncomment for debugging
//print_r($_POST);
 
//most sites have magic quotes on
//but if they do not, this code simulates magic quotes
if( !get_magic_quotes_gpc() )
{
    if( is_array($_POST) )
        $_POST = array_map('addslashes', $_POST);
}
 

//make sure there is data in the name and email fields
if( empty($_POST["Name"]) )
{
    $error["name"] = "Name is required.";
    $Name = "";
}
else
    $Name = $_POST["Name"];
 
if( empty($_POST["Email"]) )
{
    $error["email"] = "Email is required.";
    $Email = "";
}
else
    $Email = $_POST["Email"];
 
if( empty($_POST["OtherInfo"]) )
{
    $OtherInfo = "";
}
else
    $OtherInfo = $_POST["OtherInfo"];
 
  
//check to make sure the qty fields are whole numbers
//but only check if there was data entered
if( !empty($_POST["qtyA"]) )
{
    if( is_numeric($_POST["qtyA"]) && ( intval($_POST["qtyA"]) == floatval($_POST["qtyA"]) ) )
    {
        //we have a whole number
    }
    else
        $error["qtyA"] = "Please enter a whole number for Class A Widgets.";
}
 
if( !empty($_POST["qtyB"]) )
{
    if( is_numeric($_POST["qtyB"]) && ( intval($_POST["qtyB"]) == floatval($_POST["qtyB"]) ) )
    {
        //we have a whole number
    }
    else
        $error["qtyB"] = "Please enter a whole number for Class B Widgets.";
}
 
if( !empty($_POST["qtyC"]) )
{
    if( is_numeric($_POST["qtyC"]) && ( intval($_POST["qtyC"]) == floatval($_POST["qtyC"]) ) )
    {
        //we have a whole number
    }
    else
        $error["qtyC"] = "Please enter a whole number for Class C Widgets.";
}
 

//we should have at least 1 item ordered in the form
if( empty($_POST["qtyA"]) && empty($_POST["qtyB"]) && empty($_POST["qtyC"]) )
    $error["no_qty"] = "Please enter at least 1 item to order.";
 

if( is_array($error) )
{
 
    echo "An error occurred while processing your order.";
    echo "<br>\n";
    echo "Please check the following error messages carefully, then click back in your browser.";
    echo "<br>\n";
 
    while(list($key, $val) = each($error))
    {
        echo $val;
        echo "<br>\n";
    }
 
    //stop everything as we have errors and should not continue
    exit();
 
}
 
  
//we do not need the rest of the form fields as we can just calculate them from the whole numbers
if( !empty($_POST["qtyA"]) )
{
    $qtyA = $_POST["qtyA"];
    $totalA = $qtyA * 1.25;
}
else
{
    $qtyA = 0;
    $totalA = 0;
}
 
if( !empty($_POST["qtyB"]) )
{
    $qtyB = $_POST["qtyB"];
    $totalB = $qtyB * 2.35;
}
else
{
    $qtyB = 0;
    $totalB = 0;
}
 
if( !empty($_POST["qtyC"]) )
{
    $qtyC = $_POST["qtyC"];
    $totalC = $qtyC * 3.45;
}
else
{
    $qtyC = 0;
    $totalC = 0;
}
 
$GrandTotal = $totalA + $totalB + $totalC;
 
  
//we have our data, and now build up an email message to send
$mailto = "emailaddr@nowhere.not";
$subject = "Web Order";
 
$body  = "The following confirms the details of your order:\n";
$body .= "\n\n";
$body .= "Name: " . $Name . "\n";
$body .= "Email: " . $Email . "\n";
$body .= "Other Contact Info: " . $OtherInfo . "\n";
$body .= "\n\n";
$body .= "Class A Widgets: (" . $qtyA . " * 1.25) = " . $totalA . "\n";
$body .= "Class B Widgets: (" . $qtyB . " * 2.35) = " . $totalB . "\n";
$body .= "Class C Widgets: (" . $qtyC . " * 3.45) = " . $totalC . "\n";
$body .= "\n";
$body .= "TOTALS: " . $GrandTotal . "\n";
 
mail($mailto, $subject, $body);
mail($Email, $subject, $body);
 
//we should state the order was sent
echo "The following information was sent.";
echo "<br>\n";
echo "<pre>\n";
echo $body;
echo "</pre>\n";
 

//we can store the order in a database as well
 
$link = @mysql_connect('servername', 'username', 'password');
if (!$link)
{
   echo "Could not connect: " . mysql_error();
}
else
{
    mysql_select_db('my_database');
 
    $query  = "INSERT INTO order_queue
             (  Name ,   Email ,   OtherInfo ,   qtyA ,
                totalA ,   qtyB ,   totalB ,   qtyC ,   totalC ,   GrandTotal )";
    $query .= " VALUES
             ('$Name', '$Email', '$OtherInfo', '$qtyA',
              '$totalA', '$qtyB', '$totalB', '$qtyC', '$totalC', '$GrandTotal')";
    //echo $query . "<br>\n";
 
    $result = mysql_query($query);
    mysql_free_result($result);
    mysql_close($link);
}
?>

And with the conclusion of that script we have concluded the processing of our order form.  This project is now a complete representation of a simple order form, from the initial HTML page containing the form, through all the client-side validations and calculations, to the server-side "back end" processing of the order.  I hope that this clearly illustrates the relationships between the various technologies involved and maybe can even help you by forming the basis of a project you are working on.

Thanks for following along with me!

 

 

Return to the previous step  |  Return to the beginning

Make a Comment

Loading Comments...

  • Web Development Newsletter Signup

    Invalid email
    You have successfuly registered to our newsletter.
  •  
  •  
  •  
Thanks for your registration, follow us on our social networks to keep up-to-date