Page 1 of 1

foreach not for-eachinging :D

Posted: Mon Mar 13, 2006 4:25 pm
by LiveFree
Hey Hey,

So I have this code that adds a row for each product seleted to the Order_Details table, but when you select 2+ products only one is entered into the DB :(

order2.php

Code: Select all

<?php

// Select all of the products besides products from the ingredients category
function select_products()
{
   global $conn, $ingredient_id;
   $sql = "SELECT Name, Product_ID FROM Products WHERE Product_Cat_ID!=" . $ingredient_id;
   return mysql_query($sql, $conn);
}
// Takes selected products and adds them to an array
// The array is then stored i na session
function choose_prods($post_info)
{
   $_SESSION['purchase_prods'] = array();
   foreach($post_info as $name => $value)
   {
      if(preg_match('/\d+/', $name) && $value == "yes")
      {
         $_SESSION['purchase_prods'][] = array($name);
      }
   }
}
// Display the products
function disp_prods($error = '')
{
   $prods     = select_products();
   $num_prods = mysql_num_rows($prods);
   if($num_prods > 0) // If there are any products
   {
?>
<form action="order.php" name="select_products" method="post">
<input type="hidden" name="type" value="select_prods">
<?php print $error; ?>
 Please select products:
<div style="width:350px; position: absolute; top: 30px; left: 10px;">
<?php
      $first_side = floor($num_prods / 2);
      for($i = 0; $i < $first_side && $prod = mysql_fetch_assoc($prods); $i++)
      {
?>
<input type="checkbox" name="<?php print $prod['Product_ID']; ?>" value="yes" />
&nbsp;&nbsp; <?php print $prod['Name']; ?><br />
<?php
      }
?>
<input type="submit" value="Next" />
</div><div style="width: 350px; position: absolute; top: 30px; left: 380px;">
<?php
      while($prod = mysql_fetch_assoc($prods))
      {
?>
<input type="checkbox" name="<?php print $prod['Product_ID']; ?>" value="yes" />
&nbsp;&nbsp; <?php print $prod['Name']; ?><br />
<?php
      }
?>
</div>
</form>
<?php
   } else {
      print "There are no products to choose.";
   }
}

if(isset($_SESSION['cust_id'])) // If they've gone through the other pages
{
   if(isset($_POST['type']) && $_POST['type'] == "select_prods") // If there has been
   {                                                             // a form submission
      choose_prods($_POST); // execute choose products
      if(count($_SESSION['purchase_prods']) > 0) // If any products where selected
      {
$_SESSION['product']=$prod['Name'];
         require_once("order" . (++$_SESSION['step']) . ".php"); // continue
      } else {
         disp_prods("No products were selected."); // Otherwise display the products with
      }                                            // an error
   } else {
      disp_prods(); // Display the products without an error
   }
} else {
   require_once("order" . (--$_SESSION['step']) . ".php"); // Go back
}

?>
order5.php (inserts into DB)

Code: Select all

<?php
$cost_query = mysql_query("SELECT * FROM Costs"); // get the cost information
$info = mysql_fetch_assoc($cost_query); // put the information in an array

$sub_total = 0; // start subtotal at 0

// add up products by looping through them and multiplying quantity by final price
foreach($_SESSION['purchase_prods'] as $prod_arr)
{
  $tot_quan=$prod_arr[1] * $prod_arr[2];
   $sub_total += $tot_quan;
}
$vat = (.175*$sub_total);
$month=date('m'); // For Invoice shiz
$year=date('Y');
if (isset($_POST['Yes'])){
$today_date = date("d/m/Y"); // get today's date
//$cost_query = mysql_query("SELECT * FROM Costs"); // get the cost information
//$info = mysql_fetch_assoc($cost_query); // put the information in an array

//$sub_total = 0; // start subtotal at 0

// add up products by looping through them and multiplying quantity by final price
//foreach($_SESSION['purchase_prods'] as $prod_arr)
//{
  // $sub_total += $prod_arr[1] * $prod_arr[2];
//}

//$vat = (1 + ($info['Tax_rate']/100)) * $sub_total + $info['Labour_cost']; // calculate VAT

$ins_sql = "INSERT INTO Orders VALUES(NULL, " . $_SESSION['cust_id'] . ", '" . $today_date . "',
            '" . $_SESSION['deldate'] . "', " . $vat . ", " . $sub_total . ",'$month','$year', 0)"; // SQL to insert
mysql_query($ins_sql, $conn); // perform the insert of the order

$order_id = mysql_insert_id($conn); // get the id of the order

foreach($_SESSION['purchase_prods'] as $prod_arr) // loop through products
{
   mysql_query("INSERT INTO `Order_Details` VALUES(" . $order_id . ", " . $prod_arr[0] . ",
                " . $prod_arr[2] . ", " . $prod_arr[1] . ")", $conn);// OR DIE (mysql_error()) // add to product details
                
// Add into the Invoices Details table

   //mysql_query("INSERT INTO `Invoice_Details` VALUES ('$order_id', '$prod_arr[0]', '$prod_arr[2]', '$prod_arr[1]')"); //OR DIE (mysql_error());
}

mysql_query("INSERT INTO `Invoice_Record` VALUES ('$order_id', '$_SESSION[cust_id]', '$sub_total', '$vat', '$today_date', '$_SESSION[deldate]')");// OR DIE (mysql_error())


// statement SQL
$state_sql = "SELECT statementcustomer FROM Customers WHERE customerid=" . $_SESSION['cust_id'];
$is_state = mysql_result(mysql_query($state_sql, $conn), 0);


if($is_state == 1) // if user is a statement user
{
   mysql_query("INSERT INTO Statement VALUES(NULL, '" . $today_date . "', " . $_SESSION['cust_id'] . ",
                " . $sub_total . ")", $conn); // add a statement
}
unset($sub_total);
print "Done."; // show that it worked

// Make it so another order can be placed if so wanted
//$_SESSION = array(); // erase session array
//@session_destroy(); // destroy the session

unset($_SESSION['purchase_prods']);
unset($_SESSION['deldate']);
unset($_SESSION['cust_id']);
unset($_SESSION['addr_id']);
unset($_SESSION['step']);

}elseif (isset($_POST['No'])){
unset($_SESSION['purchase_prods']);
unset($_SESSION['deldate']);
unset($_SESSION['cust_id']);
unset($_SESSION['addr_id']);
unset($_SESSION['step']);
die('Your Order has Been Stopped!');
}else{
  $custid=$_SESSION['cust_id'];
//$year=date('Y');
// Get Cust info

$cust=mysql_query("SELECT Name AS CustName, Address AS CustAddr, citytown AS CustCity, Postcode AS CustZIP,email AS CustEmail, Telephone AS CustTele, fax AS CustFax FROM Customers WHERE customerid='$custid'") OR DIE (mysql_error());
$cust_info=mysql_fetch_array($cust);

//
//$sql_orders=mysql_query("SELECT Customers.Name AS CustName, Customers.Address AS CustAddr, Customers.citytown AS CustCity, Customers.Postcode AS CustZIP, Customers.email AS CustEmail, Customers.Telephone AS CustTele, Customers.fax AS CustFax, Orders.Date_Ordered AS dateo, Orders.Delivery_Date AS dated, Orders.VAT AS vat, Orders.Subtotal AS subtotal, Order_Details.Price AS price, Order_Details.Quantity AS quan, Products.Name AS name FROM Orders, Order_Details, Products, Customers WHERE Orders.Invoice_Number=Order_Details.Invoice_Number AND Order_Details.Product_ID=Products.Product_ID AND Orders.Customer_ID='$custid' AND Orders.Customer_ID=Customers.customerid") OR DIE (mysql_error());
//
$sql_info=mysql_query("SELECT * FROM `Own Company Details`") OR DIE ('Could not query:' . mysql_error());
$info=mysql_fetch_array($sql_info);
// Start the layout
//$cust_info=mysql_fetch_array($sql_orders);

echo "<div align='center'><img src='Petal Foods Logo.jpg'><br />
<b>Address:</b>{$info['Address']}<br />
<b>City:</b>{$info['CityTown']}<br />
<b>Postal Code:</b>{$info['PostCode']}<br />
<b>Telephone:</b>{$info['Telephone']}<br />
<b>Mobile Phone:</b>{$info['Mobile']}<br />

<b>Email:</b>{$info['Email']}<br />
<b>VAT #:</b>{$info['VATno']}</div>
<br />
<b>Customer:</b> {$cust_info['CustName']}<br />
<b>Address:</b> {$cust_info['CustAddr']}<br />
<b>City/Town:</b> {$cust_info['CustCity']}<br />
<b>Postal Code:</b> {$cust_info['CustZIP']}<br />
<b>Telephone:</b> {$cust_info['CustTele']}<br />
<b>Email:</b> {$cust_info['CustEmail']}<br />
<b>Fax:</b> {$cust_info['CustFax']}<br /><br />
<div align='center'>
<table border='1' width='90%'>
<tr><td><b>Product</b></td><td><b>Price</b></td><td><b>Quan.</b></td><td><b>Total</b></td></tr>";

foreach ($_SESSION['purchase_prods'] AS $product_arr){
  $prod_total=$product_arr[1] * $product_arr[2];
  $prod=mysql_query("SELECT Name FROM Products WHERE Product_ID=$product_arr[0]") OR DIE (mysql_error());
  $prod_info=mysql_fetch_array($prod);
  $prod_total=number_format($prod_total,2,'.','');
  $product_arr[2]=number_format($product_arr[2],2,'.','');
  echo "<tr><td>{$prod_info['Name']}</td><td>{$product_arr[2]}</td><td>{$product_arr[1]}</td><td>£$prod_total</td></tr>";
  $_SESSION['prod_tot']=$prod_total;
}

/*while ($sub_total > 0){ 
$sub_total+=$_SESSION['prod_tot'];
}
*/ 
$sub_total=number_format($sub_total, 2, '.', "");
$vat=number_format($vat, 2, '.', "");
echo "<tr><td></td><td></td><td></td><td></td></tr>
<tr><td></td><td></td><td><b>VAT: £$vat</b></td></tr>
<tr><td></td><td></td><td><b>Total: £$sub_total</b></td></tr></table>
<br />
<form method='POST' action=\"$PHP_SELF\">
<b>Are you sure?:<br />
Yes: <input type='checkbox' name='Yes'><br />
No: <input type='checkbox' name='No'><br />
</b><input type='submit' name='submit' value='Confirm'></form></div>";

}
?>
DB Schema:

`Orders`
Invoice_Number
Customer_ID
Date_Ordered
Delivery_date
VAT
Subtotal
month
year
dispatched

`Order_Details`
Invoice_Number
Product_ID
Price
Quantity

Thank You :)

Posted: Mon Mar 13, 2006 4:48 pm
by neophyte
I think this might be the culprit, or at least give us a clue.

Run this and echo out the sql to the screen ( assuming you can do it in a non-production environment).

Code: Select all

foreach($_SESSION['purchase_prods'] as $prod_arr) // loop through products
{
   mysql_query("INSERT INTO `Order_Details` VALUES(" . $order_id . ", " . $prod_arr[0] . ",
                " . $prod_arr[2] . ", " . $prod_arr[1] . ")", $conn);// OR DIE (mysql_error()) // add to product details
                
// Add into the Invoices Details table

   //mysql_query("INSERT INTO `Invoice_Details` VALUES ('$order_id', '$prod_arr[0]', '$prod_arr[2]', '$prod_arr[1]')"); //OR DIE (mysql_error());
}
Post what it says. I might uncomment the or die(mysql_errror()) too.

Posted: Mon Mar 13, 2006 5:40 pm
by LiveFree
Oh yea,

I nerver noticed the earlier Dev had that commented out :D