[Resolved] displaying shopping cart issues

[shanis42p]

As a precursor to this script I pass 'partnum' and 'description' from an item detail page. This script is intended to take the passed values and add them into a shopping cart, then display the cart with the second half of the script. The script displays the first row of the table (the row with headings) then I get a Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource problem once the script hits the while loop needed to display the records. I am not sure if it is adding it to the session, I dont know how to check that. Also after I fix the while loop I dont know what type of errors I am going to get after that. This is really uncharted PHP territory for me so any help would be useful.

<?php
				$partnum = $_GET['PARTNUM'];
				$description = $_GET['DESCRIPTION'];
				$datatable = $_GET['DB'];

			if (isset($partnum)) {
				if (isset ($_SESSION['cart'][$partnum])) {
					$qty = $_SESSION['cart'][$partnum] + 1;
					}
				else {
					$qty = 1;
					}
			$_SESSION['cart'][$partnum] = $qty;
			}
			else {
				header ("Location: http//" .$_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php");
				exit();
			}
	 		?>
			  <?php
			#Script 13.9 - view_cart.php
			if(isset ($_POST['submit'])){
				foreach ($_POST['qty'] 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){
				// INITIALIZING VARIABLES
				$hostname = "localhost";
				$username = "xxxxxxxxxxx";
				$password = "xxxxxxxxxxx";
				$dbname = "xxxxxxxxxxxx";
				$usertable = $datatable;

				mysql_connect($hostname,$username,$password) or die("Unable to connect to database");
				mysql_select_db($dbname) or die("Unable to select database");

				$query = 'SELECT * FROM "$usertable" WHERE "$usertable".PARTNUM LIKE "$partnum" IN (';
					foreach ($_SESSION['cart'] as $key => $value){
						$query .= $key . ','; 
					}
					$query = substr ($query, 0, -1) . ') ORDER BY partnum ASC';
					$result = mysql_query ($query);

				echo 
				'<table border="1" width="90%" cellspacing="0" cellpadding="3" align="center" bordercolor="#E0DFE3">
					<tr bgcolor="#4E7B98" class="style2">
                  		<td width="12%"><div align="center" class="style15">Part Number </div></td>
                  		<td width="48%"><div align="center" class="style15">Description</div></td>
                  		<td width="10%"><div align="center"><span class="style15">Quanitity</span></div></td>
                  		<td width="13%"><div align="center"><span class="style15">Price</span></div></td>
                  		<td width="17%"><div align="center" class="style15">Line Total</div></td>
                	</tr>
					<form action="view_cart.php" method="post">
					';

				//Print each item.
				$total = 0; //total cost of each order
				while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

					//Calculate the totals and subtotals.
					$subtotal = $_SESSION['cart'][$row['partnum']] * $row['price'];
					$total += $subtotal;

					//Print the row
					echo " 
					<tr>
						<td align=\"left\">{$row['partnum']}</td>
						<td align=\"left\">{$row['description']}</td>
						<td align=\"right\">\${$row['price']}</td>
						<td align=\"center\"><input type=\"text\" size=\"3\" name=\"qty[{$row['partnum']}]\" 
							value=\"{$_SESSION['cart'][$row['partnum']]}\" /></td>
						<td align=\"right\">$" . number_format (subtotal, 2) . "</td>
					</tr>\n";
					} //end of while loop

					echo '  
					<tr>
						<td colspan="4" align="right"><b>Total:<b></td>
						<td align="right">$' .number_format ($total, 2) . '</td>
					</tr>
				</table><div align="center"><input type="submit" name="submit" value="Update My Cart"
				/></form><br /><br /><a href="checkout.php"><font size="+3">Checkout</font></a></div>';

				//Close the database connection
				mysql_close();

			}
			else{
				echo '<p>Your cart is empty.</p>';
			}
			?>

Thanks,
Shanis

[Shrike]

Check what mysql_error() says, and echo out the query to make sure it the syntax is correct.

[shanis42p]

By "echo out" you mean have php print the query string to the screen right. I can do that, it is a good error catching method,

Thanks.

I will also copy/paste that mysql_error.

Thanks Again,
Shanis

[shanis42p]

Modified Query

mysql_connect($hostname,$username,$password) or die("Unable to connect to database");
					mysql_select_db($dbname) or die("Unable to select database");

				$query = 'SELECT * FROM "$usertable" WHERE PARTNUM LIKE "$partnum" IN (';
					foreach ($_SESSION['cart'] as $key => $value){
						$query .= $key . ','; 
					}
					$query = substr ($query, 0, -1) . ') ORDER BY partnum ASC';
					$result = mysql_query ($query);


				print $query;

Echo-ing out the query string results in the following

SELECT * FROM "$usertable" WHERE PARTNUM LIKE "$partnum" IN (CB006) ORDER BY partnum ASC

It seems that the query is not picking up the values stored in the variables, instead it is outputting "$usertable". I have also echoed out the variables to ensure they are recieving values, they indeed are.

The mysql_error I get is as follows:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home3/tchbyrd/tool-link-www/shoppingcart.php on line 283

Line 283 is the while statement:
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

Let me know what you think,

Thanks Again.
Shanis

I think there is a problem with my use of " and ' and nothing. I always get them confused.

[Shrike]

PHP does not interpolate inside of single quotes. You can concatenate the string or use double quotes.

echo 'blah blah' . $variable . ' blah blah';
echo "blah blah $variable blah blah";

[shanis42p]

OK, good tip,

Messing with the syntax of the query I now have this printing to the browser:

partnum= CB09B
datatable= GiftGallery

SELECT * FROM GiftGallery WHERE PARTNUM LIKE CB09B IN (CAV67,CAV68,C0AAA,C4C46,CB09😎 ORDER BY partnum ASC

I added 5 parts to the shopping cart thats what all the CXXXX numbers are.

Anyhow, now I get the same error with the while loop. I changed the code dramatically, I know it is bad coding, but I prefer weaving PHP into html. In dreamweaver it makes things a little more visual instead of using echo statements.

Here is the updated code:

<table width="100%"  border="0" cellspacing="0" cellpadding="10">
              <tr>
                <td colspan="2"><p class="style9">My Shopping Cart
				  <p class="style9">
				  <?php
					$partnum = $_GET['PARTNUM'];
					$datatable = $_GET['DB'];

				print "partnum= $partnum<br>";
				print "datatable= $datatable<br><br>";

			if (isset($partnum)) {
				if (isset ($_SESSION['cart'][$partnum])) {
					$qty = $_SESSION['cart'][$partnum] + 1;
					}
				else {
					$qty = 1;
					}
			$_SESSION['cart'][$partnum] = $qty;
			}
			else {
				header ("Location: http//" .$_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php");
				exit();
			}
	 		?>
			  <?php
			#Script 13.9 - view_cart.php
			if(isset ($_POST['submit'])){
				foreach ($_POST['qty'] 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){
				// INITIALIZING VARIABLES
				$hostname = "localhost";
				$username = "xxxxxxx";
				$password = "xxxxxxx";
				$dbname = "xxxxxxx";
				$usertable = $datatable;

				mysql_connect($hostname,$username,$password) or die("Unable to connect to database");
				mysql_select_db($dbname) or die("Unable to select database");

				$query = "SELECT * FROM $usertable WHERE PARTNUM LIKE $partnum IN (";
					foreach ($_SESSION['cart'] as $key => $value){
						$query .= $key . ","; 
					}
					$query = substr ($query, 0, -1) . ") ORDER BY partnum ASC";
					$result = mysql_query ($query);


				print $query;
				?>
              <form name="form1" method="post" action="">
                <p class="style9">
                <table border="1" width="90%" cellspacing="0" cellpadding="3" align="center" bordercolor="#E0DFE3">
                  <tr bgcolor="#4E7B98" class="style2">
                    <td width="12%"><div align="center" class="style15">Part Number </div></td>
                    <td width="48%"><div align="center" class="style15">Description</div></td>
                    <td width="10%"><div align="center"><span class="style15">Price</span></div></td>
                    <td width="13%"><div align="center"><span class="style15">Quantity</span></div></td>
                    <td width="17%"><div align="center" class="style15">Line Total</div></td>
                  </tr>
                  <?php 
				//Print each item.
				$total = 0; //total cost of each order
				while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

					//Calculate the totals and subtotals.
					$subtotal = $_SESSION['cart'][$row['partnum']] * $row['price'];
					$total += $subtotal;

					//Print the row
					?>
                  <tr>
                    <td align=left><?php print "{$row['partnum']}"; ?></td>
                    <td align=left><?php print "{$row['description']}"; ?></td>
                    <td align=right>$<?php print "{$row['price']}"; ?> </td>
                    <td align=center><input type=text size=3 name=<?php print "qty[{$row['partnum']}]"; ?> 
									value=<?php print "{$_SESSION['cart'][$row['partnum']]}"; ?>></td>
                    <td align=right>$<?php print ' . number_format (subtotal, 2) . '; ?></td>
                  </tr>
                  <?php
					} //end of while loop

					?>
                  <tr>
                    <td colspan="4" align="right"><b>Total:<b></b></b></td>
                    <td align="right">$
                        <?php  $total = number_format ($total, 2); print $total; ?></td>
                  </tr>
                </table>
                <div align="center">
                  <input type="submit" name="submit" value="Update My Cart"/>
                  <br>
                  <br>
                  <a href="checkout.php"><font size="+3">Checkout</font></a></div>
                <?php
				//Close the database connection
				mysql_close();

			}
			else{
				echo '<p>Your cart is empty.</p>';
			}
			?>
            </form>                  <p class="style9">
            </td> 
          </tr>
      </table>

The error I get is: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home3/tchbyrd/tool-link-www/shoppingcart.php on line 281

Line 281 is:

while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

In all the data checking I do, I cant seem to track the values of {$row['partnum']}, any of the row values for that matter. I think there is a strong chance, the query is not putting any values into the $row array. I think I am having trouble conceptualizing stuff like

$_SESSION['cart'][$row['partnum']]

This is supposed to be a quantity to be used in row values. Arrays are mind boggling.

Again I thank you in advance.
Shanis

[shanis42p]

This might be a problem too.

I am using the GET method for DB name (My Table Name) so that when the query runs it will pull values from the proper table. I have three different product tables:

GiftGallery
EndmillDetails
BurDetails

These tables are based on individual suppliers, I have three different suppliers. I am trying to pull item details from three different tables.

Can this work?

Thanks Shrike, You are the man.
Shane

[tsinka]

Hi,

about that error message. There's at least one syntax error in your query

Replace

SELECT * FROM $usertable WHERE PARTNUM LIKE $partnum IN (

with

SELECT * FROM $usertable WHERE PARTNUM IN (

Thomas

[shanis42p]

After making that change the query seems a lot more logical,

SELECT * FROM EndmillDetails; WHERE PARTNUM IN (56601,51770) ORDER BY partnum ASC

I notice that after my table name, php is returning a ;. I dont know where that comes from, I will track that down.

Still getting an error on my line 281:

Line 281 is:

PHP:

while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

---

Thanks For the tip
Shanis

[shanis42p]

I tracked that colon (semi-colon?) down. It is all fixed. It was in my variables sent to this page in my link.

[shanis42p]

Fixing the two things recently,

Now I dont have an error in the while statement.

Unfortunately, the returned table is returning blank lines, so at some point the values are not making it to the $row array.

Let me know if you see anything.

Thank you so much Thomas,
Shane

[tsinka]

Ok,

so now your query looks like

SELECT * FROM EndmillDetails WHERE PARTNUM IN (56601,51770) ORDER BY partnum ASC

Do you get any data if you execute that query with e.g. phpMyAdmin or with the console MySQL client ?

Thomas

[shanis42p]

OK,

I changed my data structure, I created a single table with Partnum, Price and Description, instead of three tables, one for each manufacturer.

I ran the query in MySQLadmin.

It returns the data for the parts.

Everything works perfectly for all the items except parts that contain any Alphabetic Characters.

Parts in my Gift Gallery are part numbered C----, and some of the parts for the other manufacturer start with M-----, these items will not post to the out-putted table.

Here is the new-and-improved:

<?php
					$partnum = $_GET['PARTNUM'];
					$datatable = $_GET['DB'];

				print "partnum= $partnum<br>";
				print "datatable= $datatable<br><br>";

			if (isset($partnum)) {
				if (isset ($_SESSION['cart'][$partnum])) {
					$qty = $_SESSION['cart'][$partnum] + 1;
					}
				else {
					$qty = 1;
					}
			$_SESSION['cart'][$partnum] = $qty;
			}
			else {
				header ("Location: http//" .$_SERVER['HTTP_HOST'] . dirname($_SERVER['PHP_SELF']) . "/index.php");
				exit();
			}
	 		?>
			  <?php
			#Script 13.9 - view_cart.php
			if(isset ($_POST['submit'])){
				foreach ($_POST['qty'] 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){
				// INITIALIZING VARIABLES
				$hostname = "localhost";
				$username = "tchbyrd";
				$password = "lowfat1";
				$dbname = "tool_link_net";


				mysql_connect($hostname,$username,$password) or die("Unable to connect to database");
				mysql_select_db($dbname) or die("Unable to select database");

				$query = "SELECT * FROM ProductMaster WHERE PARTNUM IN (";
					foreach ($_SESSION['cart'] as $key => $value){
						$query .= $key . ","; 
					}
					$query = substr ($query, 0, -1) . ") ORDER BY partnum ASC";
					$result = mysql_query ($query);


				print $query;
				?>
              <form name="form1" method="post" action="">
                <p class="style9">
                <table border="1" width="90%" cellspacing="0" cellpadding="3" align="center" bordercolor="#E0DFE3">
                  <tr bgcolor="#4E7B98" class="style2">
                    <td width="12%"><div align="center" class="style15">Part Number </div></td>
                    <td width="48%"><div align="center" class="style15">Description</div></td>
                    <td width="10%"><div align="center"><span class="style15">Price</span></div></td>
                    <td width="13%"><div align="center"><span class="style15">Quantity</span></div></td>
                    <td width="17%"><div align="center" class="style15">Line Total</div></td>
                  </tr>
                  <?php 
				//Print each item.
				$total = 0; //total cost of each order
				while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

					//Calculate the totals and subtotals.
					$subtotal = $_SESSION['cart'][$row['PARTNUM']] * $row['PRICE'];
					$total += $subtotal;

					//Print the row
					?>
                  <tr>
                    <td align=left><?php print "{$row['PARTNUM']}"; ?></td>
                    <td align=left><?php print "{$row['DESCRIPTION']}"; ?></td>
                    <td align=right>$<?php print "{$row['PRICE']}"; ?> </td>
                    <td align=center><input type=text size=3 name=<?php print "qty[{$row['PARTNUM']}]"; ?> 
									value=<?php print "{$_SESSION['cart'][$row['PARTNUM']]}"; ?>></td>
                    <td align=right>$<?php $subtotal = number_format ($subtotal, 2); print $subtotal; ?></td>
                  </tr>
                  <?php
					} //end of while loop

					?>
                  <tr>
                    <td colspan="4" align="right"><b>Total:<b></b></b></td>
                    <td align="right">$
                        <?php  $total = number_format ($total, 2); print $total; ?></td>
                  </tr>
                </table>
                <div align="center">
                  <input type="submit" name="submit" value="Update My Cart"/>
                  <br>
                  <br>
                  <a href="checkout.php"><font size="+3">Checkout</font></a></div>
                <?php
				//Close the database connection
				mysql_close();

			}
			else{
				echo '<p>Your cart is empty.</p>';
			}
			?>

It seems like there is a problem with the Alpha characters,

But I cant find it, the only time I use "is_numeric" it is in reference to quantity. The problem doesnt seem to be in the code.

I also ran this query in MySQLadmin and got the following error.
#1054 - Unknown column 'M14401' in 'where clause'

I browse the table using LIKE 14401 and it returns two values. Think of a drill bit, one is 1 inch diameter, the M stands for Metric (25.4mm diameter in this example). When I use LIKE M14401 I get an error even though I can see it in the new "ProductMaster" table.

Thanks, if you see anything let me know,

Shane

[tsinka]

Use single quotes in the SQL query

.... WHERE thefield='M14401'

Thomas

[shanis42p]

I got you,

Awesome,

Here is the working query:

$query = "SELECT * FROM ProductMaster WHERE PARTNUM IN (";
						foreach ($_SESSION['cart'] as $key => $value){ 

						$query .= "'" . $key . "',"; 

					}
					$query = substr ($query, 0, -1) . ") ORDER BY partnum ASC";
					$result = mysql_query ($query);

You are F-N great, thanks. Thats huge.

Thanks,
Shane