[SOLVED] pulling product list from database

Maq · April 8, 2009

Yes, that's because you're adding a comma even if $key is empty. (see the 2 consecutive commas without value in between?) To check do something like:

$query .= (!empty($key) ? $key."," : "";

lional · April 8, 2009

I got rid of the comma by doing

if ($key != "") {

$query .= $key . ',';

}

but I still get

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 '59,41,25,22,1,2,3')' at line 1

Maq · April 8, 2009

I think each value needs single quotes around it. With integers maybe not, but try this:

         $query = "SELECT * FROM supplements WHERE supp_id IN (";
         foreach ($_SESSION['cart'] as $key => $value) {
            $query .= "'$key'" . ",";
         }
         $query .= substr ($query, 0, -1) . ") ";

lional · April 8, 2009

I still get

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 'SELECT * FROM supplements WHERE supp_id IN ('59','41','25','22','1','2','3')' at line 1

Maq · April 8, 2009

Okay I found an easier solution that should work.

Instead of this block of code, which you had your quotes messed up in anyway, try this:

         $query = 'SELECT * FROM supplements WHERE supp_id IN (';
         foreach ($_SESSION['cart'] as $key => $value) {
            $query .= $key . ',';
         }
         $query .= substr ($query, 0, -1) . ') ';

Just use this, you can give IN() an array rather than using a foreach to extract each individual $key.

$query = "SELECT * FROM supplements WHERE supp_id IN({$_SESSION['cart']})";

lional · April 9, 2009

That didn't work for some reason and I got the products to list but it hasn't pulled the quantities through that should be added to the array in the beginning of the script.

Here is my code

<body bgcolor="#32818B" topmargin="0">
<?php
session_start();
  include 'header.php';
  $client_out = $_SESSION['clid'];
  include 'includes/conn_db.php';
    $query_cl = "SELECT * from client_supps WHERE client_id = '$client_out'";
$result_cl = mysql_query($query_cl, $conn);
$supp_id_out = array();
while ($row_cl = mysql_fetch_assoc($result_cl)){
$supp_id_out[] = $row_cl["supp_id"];
$qty_out = $row_cl["qty"];


}
foreach($supp_id_out as $key => $value)
{
$qty1 = $value;
$pid = $key;

	// add to the cart session variable
	$_SESSION['cart'][$pid] = $qty1;

}	

	// Display the cart if it is not empty
	$list = "". implode(",", $_SESSION['cart']) ."";
	print $list;
					// Retrieve all of the information for the products in the cart
		$query = "SELECT * FROM supplements WHERE supp_id IN ($list)";
		$result = mysql_query($query) or die(mysql_error());

		// create a table and a form
		print <<<TOP
		 <table width="790" align="center" bgcolor="#b3314b" cellpadding="0" cellspacing="0">
          <tr><td height="450">
					<table summary="" width="560" align="center" bgcolor="#b3314b">
<tr>
<td nowrap align="center"><font face="arial" size="2" color="white">></font></tr></table>
					<table summary="" align="center" width="560" border="1">
					<tr>
					<td>
					<table border="0" width="560" cellpadding="3"bgcolor="#b3314b">
					<tr>
					<td align="left" width="250"><font face="arial" size="2" color="white"><b>Supplement</b></font></td>

					<td align="right" width="120"><font face="arial" size="2" color="white"><b>Line Total</b></font></td>
					</tr>
			<form action="view_cart.php" method="post">		
TOP;
		// Print each item
		$total = 0; // total cost of the order
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		/*
		if ($row['specials_discount'] > 0) {
		$list_price_out = $row['price'] / $row['specials_discount'];
		} else {
      $list_price_out = $row['price'];
      }
      */
  $supplement_out = $row['supplement'];
  print $supplement_out;
      $list_price_out = $row['price'];
      $list_price_out = number_format($list_price_out, 2, '.', '');
		// Calculate the total and subtotals
		$subtotal = $_SESSION['cart'][$row['supp_id']] * $list_price_out;
		$subtotal = number_format($subtotal, 2, '.', '');
		$total += $subtotal;
		$total = number_format($total, 2, '.', '');
		// if ($_SESSION['cart'][$row['supp_id']] > 0) {
		// print the row
		print <<<ROW
					<tr>

					<td align="left"><font face="arial" size="2" color="white">{$row['supplement']}</font></td>

					<td align="center">
            
            <input type="text" size="3" name="product[{$row['supp_id']}]" value="{$_SESSION['cart'][$row['supp_id']]}"></td>
            
<td align="right"><font face="arial" size="2" color="white">R $list_price_out </font></td>
<td align="right"><font face="arial" size="2" color="white">R $subtotal</font></td>
</tr>
ROW;
// }
} // end of the WHILE loop
// print the footer and close the table and the form
print <<<FOOTER
<tr>
<td></td><td></td><td></td>
<td align="right" valign="top"><font face="arial" size="2" color="white"><b>Total:</b></font></td>
<td align="right" valign="top"><font face="arial" size="2" color="white"><b>R $total</b></font><br><br></td>
</tr>
</table>
<div align="center">
  <table width="260" align="center">
<tr><td width="130" align="right"><input type="image" name="submit" src="images/update_cart.jpg" border="0"></form></td><td width="130" align="left"><form action="checkout.php" method="post"><input type="image" name="checkout" src="images/checkout.jpg" border="0"></form>
</td><td width="85"></td></tr></div>
FOOTER;
/*
print <<<EMP
					<table border="0" width="580" align="center">
					<tr>
					<td>
					<table summary="" width="300" border="1" style="border-color:#960232" bgcolor="#f8f3c2" align="center">
					<tr>
					<td>
					<table border="0" width="300" cellpadding="3">
					<tr><td align="center"><p><font face="arial" size="2" color="#5e5d5d">Your cart is empty!</font>
					</td></tr></table></table></table>
EMP;
*/

?>

        </table>
         </table>
         
  
         
       </body>
</html>

lional · April 9, 2009

I need to somehow be able to pull the supp_id from the mysql table but attach the quantity with it.

Any help will be appreciated

lional · April 10, 2009

I have tried doing the script another way, but I get this error on my query

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 'SELECT * FROM supplements WHERE supp_id IN (59, 41, 25, 22,)' at line 1

here is my code

<?php
  session_start();
  $photo_out = $_SESSION['file_details'];
  include 'header.php';
  
foreach($_POST['product'] as $key => $value)
{
$qty1 = $value;
$pid = $key;

	// add to the cart session variable
	$_SESSION['cart'][$pid] = $qty1;

}


if (isset($_POST['submit'])) {
		foreach ($_POST['product'] as $key => $value) {
		 if (($value == 0) AND (is_numeric($value))) {
				unset ($_SESSION['cart'][$key]);
				} elseif (is_numeric($value) AND ($value > 0)) {
					$_SESSION['cart'][$key] = $value;
				}
			}

			}

		// check if the shopping cart is empty
		$empty = TRUE;
		if (isset($_SESSION['cart'])) {
			foreach($_SESSION['cart'] as $key => $value) {
				if (isset($value)) {
					$empty = FALSE;

				}
			}
		}
	// Display the cart if it is not empty
	if (!$empty) {
    include 'includes/conn_db.php';
					// Retrieve all of the information for the products in the cart
		$query = "SELECT * FROM supplements WHERE supp_id IN ("; 
		foreach ($_SESSION['cart'] as $key => $value) {
		$query .= $key . ', ';
		}
		$query .= substr ($query, 0, -1) . ")";
		$result = mysql_query($query) or die(mysql_error());

		// create a table and a form
		print <<<TOP
		 <table width="790" align="center" bgcolor="#b3314b" cellpadding="0" cellspacing="0">
          <tr><td height="450">
					<table summary="" width="560" align="center" bgcolor="#b3314b">
<tr>
<td nowrap align="center"><font face="arial" size="2" color="white"><b>ORDER DETAILS FOR PHOTO $photo_out</b></font></tr></table>
					<table summary="" align="center" width="560" border="1">
					<tr>
					<td>
					<table border="0" width="560" cellpadding="3"bgcolor="#b3314b">
					<tr>
					<td align="left" width="250"><font face="arial" size="2" color="white"><b>Product</b></font></td>
					<td align="center" width="125"><font face="arial" size="2" color="white"><b>Size in mm</b></font></td>
					<td align="center" width="70"><font face="arial" size="2" color="white"><b>Qty</b></font></td>
					<td align="right" width="120"><font face="arial" size="2" color="white"><b>Unit Price</b></font></td>
					<td align="right" width="120"><font face="arial" size="2" color="white"><b>Line Total</b></font></td>
					</tr>
			<form action="view_cart.php" method="post">		
TOP;
		// Print each item
		$total = 0; // total cost of the order
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		/*
		if ($row['specials_discount'] > 0) {
		$list_price_out = $row['price'] / $row['specials_discount'];
		} else {
      $list_price_out = $row['price'];
      }
      */
      $list_price_out = $row['price'];
      $list_price_out = number_format($list_price_out, 2, '.', '');
		// Calculate the total and subtotals
		$subtotal = $_SESSION['cart'][$row['prod_id']] * $list_price_out;
		$subtotal = number_format($subtotal, 2, '.', '');
		$total += $subtotal;
		$total = number_format($total, 2, '.', '');
		if ($_SESSION['cart'][$row['prod_id']] > 0) {
		// print the row
		print <<<ROW
					<tr>

					<td align="left"><font face="arial" size="2" color="white">{$row['supplement']}</font></td>

            
<td align="right"><font face="arial" size="2" color="white">R $list_price_out </font></td>
<td align="right"><font face="arial" size="2" color="white">R $subtotal</font></td>
</tr>
ROW;
}
} // end of the WHILE loop
// print the footer and close the table and the form
print <<<FOOTER
<tr>
<td></td><td></td><td></td>
<td align="right" valign="top"><font face="arial" size="2" color="white"><b>Total:</b></font></td>
<td align="right" valign="top"><font face="arial" size="2" color="white"><b>R $total</b></font><br><br></td>
</tr>
</table>
<div align="center">
  <table width="260" align="center">
<tr><td width="130" align="right"><input type="image" name="submit" src="images/update_cart.jpg" border="0"></form></td><td width="130" align="left"><form action="checkout.php" method="post"><input type="image" name="checkout" src="images/checkout.jpg" border="0"></form>
</td><td width="85"></td></tr></div>
FOOTER;
} else {
print <<<EMP
					<table border="0" width="580" align="center">
					<tr>
					<td>
					<table summary="" width="300" border="1" style="border-color:#960232" bgcolor="#f8f3c2" align="center">
					<tr>
					<td>
					<table border="0" width="300" cellpadding="3">
					<tr><td align="center"><p><font face="arial" size="2" color="#5e5d5d">Your cart is empty!</font>
					</td></tr></table></table></table>
EMP;
}
?>

        </table>
         </table>
         
  
         
       </body>
</html>

Maq · April 10, 2009

I believe I mentioned this in one of my previous posts but, the problem that's occurring is due to the extra comma after the #22. You have to change your logic a bit.

         $i=0;  //ADDED
         $query = "SELECT * FROM supplements WHERE supp_id IN ("; 
         foreach ($_SESSION['cart'] as $key => $value) {
            $query .= ($i==0) ? $key : ','.$key;  //CHANGED
            $i++;  //ADDED
         }

lional · April 10, 2009

Thanks but my problem was that I had the following line

if ($_SESSION['cart'][$row['prod_id']] > 0) {

and it should have been

if ($_SESSION['cart'][$row['supp_id']] > 0) {

It all works now

Thanks Maq I really appreciated your help and time. I learned a lot

Lional

Maq · April 10, 2009

It all works now

Thanks Maq I really appreciated your help and time. I learned a lot

Lional

Sure no problem. Glad it all works now, after that grueling display of modifying and debugging code, but that's how you learn.

Please mark [sOLVED]

Sign In

[SOLVED] pulling product list from database

Recommended Posts

Maq

Link to comment

Share on other sites

lional

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

lional

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

lional

Link to comment

Share on other sites

lional

Link to comment

Share on other sites

lional

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

lional

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information