Fixed one error to get another error not sure where this syntex error is coming from

Question

This is the error I am getting: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ship_name = 'Clayton'ship_street = '110 Countrystyle Trailer PArk'ship_city =...' at line 1....

I am new to php and I have been tasked with creating a shopping cart application and this was in the tutorial that I was following and this error now shows up after fixing another set of code that is also needed.

here is the code:

<?php
 /* File: shop_form_shipinfo.inc
  * Desc: Contains the code for a form that collects
  *       shipping information for the order.
  */
include("functions.inc");                                                  
include("dbinfo.inc");                                                           
$ship_info =  array("email"         => "Email Address",                     
                    "ship_name"     => "Name",
                    "ship_street"   => "Street",
                    "ship_city"     => "City",
                    "ship_state"    => "State",
                    "ship_zip"      => "Zip",
                    "phone"         => "Phone"
                   );

$length   =   array("email"         => "55",
                    "ship_name"     => "40",
                    "ship_street"   => "55",
                    "ship_city"     => "40",
                    "ship_state"    => "20",
                    "ship_zip"      => "10",
                    "phone"         => "15",
                   );
$today = date_create("Y-m-d");                                                        
if(!isset($_POST)  == "Edit Shipping Information" )
{
  $cxn = mysqli_connect($host,$user,$passwd,$dbname);                        
  $sql = "SELECT
           firstname,address,city,state,
           zip,phone,email FROM Customer WHERE
           id = '{$_SESSION['id']}'";
  $result = mysqli_query($cxn,$sql)
         or die("Error: ".mysqli_error($cxn));
  $n = mysqli_num_rows($result);                                               
  if($n > 0)                                                                
  {
    $row = mysqli_fetch_assoc($result);
    extract($row);
  }
}                                                                              
?>
<head><title>Furniture Order: Shipping Information</title>   
  <style type="text/css">
  <!--
    #form {
      margin: 1.5em 0 0 0;
      padding: 0;

}
    #field {padding-bottom: 1em;}
    label {
      font-weight: bold;
      float: left;
      width: 20%;
      text-align: right;
      clear: left;
      margin-right; 1em;
      }
   -->
  </style>
</head>                                                                  
<h2 align='center'>Edson's Finest Furniture: Shipping Information</h2>
<p style='font-style: italic;
    font-weight: bold'>Please fill in the information below
<?php
echo "<form action='$_SERVER[PHP_SELF]' method='POST'>                   
  <div id='form'>\n";
if(isset($message))                                                            
{
   echo "<p style=\"font-weight: bold; font-style: italic;
             font-size: 90%; color: red\">
             $message</p>";
}
foreach($ship_info as $field => $value)                                     
{
  if($field == "ship_state")                                                   
  {
     echo "<div id='field'>
           <label for '$field'>State:&nbsp;</label>
      <select name='$field' id=$field>";
     $stateName=getStateName();                                                
     $stateCode=getStateCode();
     for ($n=1;$n<=64;$n++)
     {
        $state=$stateName[$n];
        $scode=$stateCode[$n];
        echo "<option value='$scode'";
        if($scode == @$_POST['state'] ||
           $scode == @$ship_state)
                 echo " selected";
        echo ">$state\n";
     }
     echo "</select></div>";
  }
   else                        
  {
     echo "<div id='field'>
            <label for='$field'>$value:&nbsp;</label>
             <input type='text' id='$field' name='$field'
                    value='".@$$field."'
                    size='{$length[$field]}'
                    maxsize='{$length[$field]}' />
           </div>\n";
  }
}
?>
      <p style="margin-top: .05in">                                      
        <input style='margin-left: 33%' type="submit"
               name="Summary" value="Continue" />
</form></body></html>

this form collects the shipping information for the order and when the user inputs their information it is suppose to insert the information into the database which it doesn't and it remains NULL into all the columns in the database.

This was the code that I just recently fixed:

<?php
 /* Program name:  Shop_order.php
  * Description:   Processes order when it's been submitted.
  */
session_start();                                                               
include("dbinfo.inc");
if(!isset($_SESSION['order_number']))                                          
{
   echo "No order number found<br>\n
   <a href='shop_products.php'>Continue shopping</a>";
   exit();
}
if(@$_GET['from'] == "cart")                                            
{
   include("shop_form_shipinfo.inc");
   exit();
}
elseif(isset($_POST['Summary']))                                               
{
   foreach($_POST as $field => $value)                                      
   {

if ($value == "")
      {
         $blanks[] = $field;
      }
      else
      {
        $good_data[$field] = strip_tags(trim($value));
      }
   }
   if(isset($blanks))
   {
      $message = "The following fields are blank.
                  Please enter the required information:  ";
      foreach($blanks as $value)
      {
         $message .="$value, ";
      }
      extract($good_data);
      include("shop_form_shipinfo.inc");
      exit();
   }
   foreach($_POST as $field => $value)                                      
   {
     if($field != "Summary")
     {
       if(preg_match("/name/i",$field))
       {
         if (!preg_match("/^[A-Za-z' -]{1,50}$/",$value))
         {
            $errors[] = "$value is not a valid name.";
         }
       }
       if(preg_match("/street/i",$field)or
          preg_match("/addr/i",$field) or
          preg_match("/city/i",$field))
       {
         if(!preg_match("/^[A-Za-z0-9.,' -]{1,50}$/",$value))
         {
            $errors[] = "$value is not a valid address
                          or city.";
         }
       }
       if(preg_match("/state/i",$field))
       {
         if(!preg_match("/[A-Za-z]/",$value))
         {
            $errors[] = "$value is not a valid state.";
         }
       }

if(preg_match("/email/i",$field))
       {
         if(!preg_match("/^.+@.+\\..+$/",$value))
         {
            $errors[]="$value is not a valid email address.";
         }
       }
       if(preg_match("/zip/i",$field))
       {
         if(!preg_match("/^[0-9]{5,5}(\-[0-9]{4,4})?$/",
                        $value))
         {
            $errors[] = "$value is not a valid zipcode.";
         }
       }
       if(preg_match("/phone/i",$field))
       {
         if(!preg_match("/^[0-9)(xX -]{7,20}$/",$value))
         {
            $errors[]="$value is not a valid phone number. ";
         }
       }
       if(preg_match("/cc_number/",$field))
       {
         $value = trim($value);
         $value = ereg_replace(' ','',$value);
         $value = ereg_replace('-','',$value);
         $_POST['cc_number'] = $value;
         if($_POST['cc_type'] == "visa")
         {
           if(!preg_match("/^[4]{1,1}[0-9]{12,15}$/",$value))
           {
             $errors[]="$value is not a valid Visa number. ";
           }
         }
         elseif($_POST['cc_type'] == "mc")
         {
           if(!preg_match("/^[5]{1,1}[0-9]{15,15}$/",$value))
           {
             $errors[] = "$value is not a valid
                           Mastercard number. ";
           }
         }
         else
         {
           if(!preg_match("/^[3]{1,1}[0-9]{14,14}$/",$value))
           {
             $errors[] = "$value is not a valid
                           American Express number. ";

}
         }
       }
       $$field = strip_tags(trim($value));
     }
   }
   if(@is_array($errors))
   {
      $message = "";
      foreach($errors as $value)
      {
         $message .= $value." Please try again<br />";
      }
      include("shop_form_shipinfo.inc");
      exit();
   }                                                                           
    /* Process data when all fields are correct */
   $cxn = mysqli_connect($host,$user,$passwd,$dbname);
   foreach($_POST as $field => $value)                                      
   {
      if($field != "Summary" )                 
      {
         $value = mysqli_real_escape_string($cxn,$value);
         $updates[] = "$field = '$value'";
      }
   }
   $update_string = implode("",$updates);                                     
   $sql_ship = "UPDATE CustomerOrder SET $update_string                        
          WHERE order_number='{$_SESSION['order_number']}'";
   $result = mysqli_query($cxn,$sql_ship)
                 or die(mysqli_error($cxn));
   extract($_POST);                                                            
   include("shop_page_summary.inc");
}
elseif(isset($_POST['Ship']))                                                  
{
   include("shop_form_shipinfo.inc");
}
elseif(isset($_POST['Final']))                                                 
{
   switch ($_POST['Final'])                                                    
   {
      case "Continue Shopping":                                                
         header("Location: shop_products.php");
         break;
      case "Cancel Order":                                                     
         #include("shop_page_cancel.inc");
         unset($_SESSION['order_number']);
         session_destroy();
         exit();
         break;

case "Submit Order":                                                    
         $cxn =
            mysqli_connect($host,$user,$passwd,$dbname);
         $sql = "UPDATE CustomerOrder SET submitted='yes'
           WHERE order_number='{$_SESSION['order_number']}'";
         $result = mysqli_query($cxn,$sql)
                or die("Error: ".mysqli_error($cxn));
         #processCCInfo();                                                     
         #sendOrder();                                                         
         #include("shop_page_accept.inc");                                     
         #email();                                                            
         session_destroy();                                                    
         break;
    }
}
?>

`if(!isset($_POST) == "Edit Shipping Information" )` What is that? You compare true/false to a string. — Markus Zeller, Jul 08 '22 at 20:34
I am unsure what that is. I was following a tutorial and that was in the code. this is the site that I got the code from: https://learning.oreilly.com/library/view/php-mysql-r/9780470167779/9780470167779_creating_the_shopping_cart_database.html — Clay Korth, Jul 08 '22 at 20:37
The SQL that is giving the error doesn't seem to be included in the section of code you've shown us; you mention inserting data, but I can't see any code to do that here. Looking at the error, though, it looks like you're missing some commas - and are also not using parameterized queries, which are [the best defense against SQL Injection attacks](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — IMSoP, Jul 08 '22 at 20:38

score 0 · Accepted Answer · answered Jul 08 '22 at 21:12

This seems to me to be the code that you can focus on:

    $cxn = mysqli_connect($host, $user, $passwd, $dbname);
    $sql = "SELECT
           firstname,address,city,state,
           zip,phone,email FROM Customer WHERE
           id = '{$_SESSION['id']}'";
    $result = mysqli_query($cxn, $sql)
        or die("Error: " . mysqli_error($cxn));
    $n = mysqli_num_rows($result);
    if ($n > 0) {
        $row = mysqli_fetch_assoc($result);
        extract($row);
    }
}

It says, fetch the query mysqli_query or die. Unfortunately, you are dying . . . or something like that. Anyway. The error you are getting is from the db. the query syntax isn't right. This is the mysql string that you are sending to the db:

SELECT firstname, address, city, state, zip, phone, email
    FROM Customer 
    WHERE id = '{$_SESSION['id']}'

First place I'd look is in this part '{$_SESSION['id']}'. You have too many quote marks messing things up. You can change it to be like this:

    $id = $_SESSION['id'];
    $sql = "SELECT
           firstname,address,city,state,
           zip,phone,email FROM Customer WHERE
           id = '{$id}'";

And that should help with the quotation marks problem. If that doesn't stop the db error, it might be because it's having a tough time mapping to the right columns. It either can't find the table called Customer or can't find the fields. Double check the spellings and you may need to add the db name in front of table name like this DBName.Customer or the table name in front of the fields Customer.firstname, Customer.state.

But overall, I would strongly recommend against continuing with mysqli and instead switch to PDO prepared statements. Querying the db this way opens up lots of security concerns.

Here are a couple other things I found: You have a semicolon after margin-right instead of a colon:

    label {
      font-weight: bold;
      float: left;
      width: 20%;
      text-align: right;
      clear: left;
      margin-right: 1em;
      }

Here is another suggestion:

<h2 align='center'>Edson's Finest Furniture: Shipping Information</h2>

should be:

<h2 style='text-align:center'>Edson's Finest Furniture: Shipping Information</h2>

Easier to read if you move one of the php tags from this:

<?php
echo "<form action='$_SERVER[PHP_SELF]' method='POST'>                   
  <div id='form'>\n";
if(isset($message))

to this:

<form action="<?php echo $_SERVER["PHP_SELF"]?>" method='POST'>                   
<div id='form'>
<?php
    if (isset($message)) {

Hope this helps.

thanks @TylerDill. I unfortunately don't know anything about PDO as the course I am taking is using MySQL and PHP 5 so its confusing. I know that I need to relearn everything afterwards on my own and coding isn't my strong suit, just needing to learn this to pass the course and this is my final exercise and I am struggling. I do appreciate the help. — Clay Korth, Jul 08 '22 at 21:32
I understand. I started with MySQL and PHP 5 years ago. Don't stop learning and you'll be fine. — Tyler Dill, Jul 08 '22 at 21:42
I am wondering if the error has to do with my validation for the shipping information: /* Process data when all fields are correct */ $cxn = mysqli_connect($host,$user,$passwd,$dbname); foreach($_POST as $field => $value) { if($field != "Summary" ) { $value = mysqli_real_escape_string($cxn,$value); $updates[] = "$field = '$value'"; } — Clay Korth, Jul 08 '22 at 21:46
$update_string = implode("",$updates); $sql_ship = "UPDATE CustomerOrder SET $update_string WHERE order_number='{$_SESSION['order_number']}'"; $result = mysqli_query($cxn,$sql_ship) or die(mysqli_error($cxn)); extract($_POST); include("shop_page_summary.inc"); } — Clay Korth, Jul 08 '22 at 21:47
Yeah I think your right. I didn't look at that part close enough when writing my answer. I'll have to edit my answer. But I think the $update_string isn't being created correctly. There should be commas between each field. Like this ship_name = 'Clayton', ship_street = '110 Country,style Trailer PArk', ship_city = ... You can try adding a comma when imploding implode(", "$updates) — Tyler Dill, Jul 08 '22 at 21:58
that was the issue, it is fixed now and finally showing up. thank you for helping me walk through this. it definitely helped. — Clay Korth, Jul 08 '22 at 22:03

Fixed one error to get another error not sure where this syntex error is coming from

1 Answers1