[Resolved] INSERT Problem

jeffgman

I am having a problem with my INSERT command. Here are my two php files:

          <?php
          //connect to the database
          require ('inc/database_conn.php');

      //query the database
      $sql = "SELECT * FROM misc_order_items ORDER BY description";
      $result = mysql_query($sql, $conn);
      ?>
      <BR><BR>
      <FORM ACTION="misc_order_submit.php" METHOD=POST>
          <TABLE BORDER="0">
              <TR>
                  <TD colspan="5">&nbsp;&nbsp;<select name="store">
                      <option value="01">01 - Signal Hill</option>
                      <option value="02">02 - Reseda</option>
                      <option value="03">03 - Orange</option>
                      <option value="04">04 - West Covina</option>
                      <option value="07">07 - Riverside</option>
                      <option value="08">08 - Norwalk</option>
                      <option value="09">09 - Fountain Valley</option>
                      <option value="10">10 - Pasadena</option>
                      <option value="11">11 - South Bay</option>
                      <option value="12">12 - San Bernardino</opion>
                      <option value="13">13 - Kearny Mesa</option>
                      <option value="16">16 - San Marcos</option>
                      <option value="40">40 - Chino</option>
                      <option value="90">90 - Chino Warehouse</option>
                      </select>
                  </TD>
              </TR>
            <TR>
                <TD width="300"><center><b><u>Description</u></b></center></TD>
                <TD width="60"><center><b><u>Min</u></b></center></TD>
                <TD width="60"><center><b><u>Max</u></b></center></TD>
                <TD width="80"><center><b><u>Pkge Qty</u></b></center></TD>
                <TD width="80"><center><b><u>Order</u></b></center></TD>
            </TR>
        <?php
                    while ($row=mysql_fetch_array($result)):
                        $id=$row["id"];
                        $sku=$row["sku"];
                        $description=$row["description"];
                        $vpc=$row["vpc"];
                        $package_qty=$row["package_qty"];
                        $location=$row["location"];
                        $min=$row["min"];
                        $max=$row["max"];
            echo "<TR>
                <TD>{$description}</TD>
                <TD>{$min}</TD>
                <TD>{$max}</TD>
                <TD>{$package_qty}</TD>
                <TD><input name='order[$i]' type=text></TD>
            </TR>";
                endwhile
        ?>
        <TR>
            <TD colspan="5">&nbsp;&nbsp;<input type=submit></TD></TR>
        </TABLE>
        </FORM>

Here is my second file. It does not like the second INSERT line:

    <?php
    //connect to the database
    require('inc/database_conn.php');

//get the variables from the other page
$id=$_POST['id'];
$sku=$_POST['sku'];
$description=$_POST['description'];
$order=$_POST['order'];
$store=$_POST['store'];

//$month=getdate(mon);
//$day=getdate(mday);
//$year=getdate(year);
//$today=$year . "-" . $month . "-" . $day
$today=date('Y-m-d');

// create the unique order number

$headerqry = "INSERT INTO misc_order_header (date, store) VALUES ('$today', '$store')";

echo "First insert okay<BR><BR>";

mysql_query($headerqry, $conn) or die(mysql_error());

$order_no=mysql_insert_id();

echo "Date: {$today}<BR>
    Store: {$store}<BR>
    Order Number: {$order_no}";

$detailqry = "INSERT INTO misc_order_order (order_no, id, sku, order, date, store) VALUES ('$order_no', '$id', '$sku', '$order[$i]', '$today', '$store')";

foreach ($order as $i => $val) {
mysql_query($detailqry, $conn) or die(mysql_error());
}

?>

Any help would be appreciated.

Pig

What exactly is happening? Is the query failing? Is it succeeding, but no insert is made? More specific please.

jeffgman

Originally posted by Pig
What exactly is happening? Is the query failing? Is it succeeding, but no insert is made? More specific please.

Sorry about that. Here is the error I am getting:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , 2004-08-04, 08)' at line 1

Pig

Add some debugging. instead of just die(mysql_error());, have it also echo the query that is being run:

die(mysql_error() . "<br>" . $headerqry );

Do that for each of the queries on the page, and see what is actually being fed to mysql. You can then work backwards to determine what is erroneous.

jeffgman

Originally posted by Pig
Add some debugging. instead of just die(mysql_error());, have it also echo the query that is being run:

die(mysql_error() . "<br>" . $headerqry );

Do that for each of the queries on the page, and see what is actually being fed to mysql. You can then work backwards to determine what is erroneous.

Thank you for the suggestion. I will definitely use that line of thinking in the future. But, I still cannot figure out what is wrong with my INSERT statement. Here is output:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order, date, store) VALUES ('30', '', '', '', '2004-08-04', '08
INSERT INTO misc_order_detail (order_no, id, sku, order, date, store) VALUES ('30', '', '', '', '2004-08-04', '08')

Pig

It could be objecting to the empty strings, or you could be using a reserved words. order, date, and id (I think) are really bad choices for column names.

jeffgman

Okay, changing those variable names did the trick. But, now I have one more problem. I changed order to be order_qty and I changed id to be id_no. I want each order_qty item in the array to be associated with the respective id_no. On the form I have a listing of about 15 items which come from a database. When the order is placed I want to make sure the order quantity is associated with the correct item (or id_no) from the form. It does not seem to be filling in the id_no at all in my last query.

Pig

You need to move this line:

$detailqry = "INSERT INTO misc_order_order (order_no, id, sku, order, date, store) VALUES ('$order_no', '$id', '$sku', '$order[$i]', '$today', '$store')";

within your foreach loop.

jeffgman

Thank you, it appears to be working as expected now.