mysql_fetch_row() Error's out 2

[WilliamMute]

Hi,

Dont know if you can help, but I am quite stuck and have been for quite a while now, for the life of me, I just cant understand why am getting the following error

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/fhlinux205/s/springrise.net/user/htdocs/cart_view.php on line 55

on the following script:

mysql_select_db($database_Connection, $Connection);
$query = "SELECT SUM(".$this->products.".product_price*" .
             $this->springrisecart.quantity .") as subtotal,
             SUM(subtotal) as total
             FROM ".$this->springrisecart."," .
             $this->products."
             WHERE session='".$this->cart_id."' AND
             ".$this->springrisecart.".id = " .
             $this->springrisecart.".product_id
             GROUP BY ".$this->springrisecart.".product_id";
   $result = mysql_query($query);
   $row = mysql_fetch_row($result);
   return $row['total'];
   $Final = $result;


The error seems to be coming from the line:

$row = mysql_fetch_row($result);
   return $row['total'];

Any help would be appreciated please

Thank you ever so much.

 

[WilliamMute]

Hi SkiFlyer,


I just noticed that my previous post to you left out some of the code at present,

here is the while loop on the body of the page it self

		$total = 0;
while ($row = mysql_fetch_assoc($result))
{
  echo 'Subtotal: '. $row['subtotal'].'<br>';
  $total+= $row['subtotal'];
}

echo 'Total: '. $total;

 

[jpadie]

the error message indicates that your query is malformed.
try the following for debugging. post the output

[CODE]
mysql_select_db($database_Connection) or die ("cannot connect to database ".mysql_error());
$sessionid = "$cart_id" ;//complete the session id here
$sql = "
SELECT
        Sum(products.product_price * springrisecart.quantity) AS total
FROM
        products
        INNER JOIN
                    springrisecart
                    ON
                    products.product_id = springrisecart.product_id
GROUP BY
         springrisecart.`session`
HAVING
        springrisecart.`session`='$sessionid'";

echo "query is : $sql";
$result=mysql_query($sql)
 or die ("Query Error: " . mysql_error());
$row=mysql_fetch_assoc($result);
echo $row['total'];

 

[WilliamMute]

Here is the output

query is : SELECT Sum(products.product_price * springrisecart.quantity) AS total FROM products INNER JOIN springrisecart ON products.id = springrisecart.product_id GROUP BY springrisecart.`session` HAVING springrisecart.`session`='f608b0f4448aaabf6ba839a18cd7e7d4'Query Error: Unknown column 'springrisecart.session' in 'having clause'


PS: Note that I have also changed the

ON
                    products.product_id = springrisecart.product_id

To
ON
products.id = springrisecart.product_id

because those are the two matching fields, its due to my bad column naming.

 

[jpadie]

i am concerned that you have changed the query to refer to product.id rather than product.product_id . in your post a couple back you indicated that the field was definitely called product_id.

anyway - the error (if you are certain the column actually exists!) indicates that you are using an old version of mysql prior to 5.02. to workaround the problem please try the following query instead:

$sql = "
SELECT
        Sum(products.product_price * springrisecart.quantity) AS total, springrisecart.`session`
FROM
        products
        INNER JOIN
                    springrisecart
                    ON
                    products.product_id = springrisecart.product_id
GROUP BY
         springrisecart.`session`
HAVING
        springrisecart.`session`='$sessionid'";

 

[WilliamMute]

Here is the output

query is : SELECT Sum(products.product_price * springrisecart.quantity) AS total, springrisecart.`session` FROM products INNER JOIN springrisecart ON products.id = springrisecart.product_id GROUP BY springrisecart.`session` HAVING springrisecart.`session`='f608b0f4448aaabf6ba839a18cd7e7d4'49.9900016784668

 

[WilliamMute]

In reference to your comment previously, product_id on the products table is NOT the same as product_id on the springrisecart table. On the contrary, due to the database design, product_id on the springrisecart table is the same as id on the products table.

 

[jpadie]

re the id stuff - i see! that's always a bad idea though. keep foreign keys named the same as their native equivalents otherwise you will have a real hard time remembering when you come back to the code a few months later.

from the output you can see that the query is now working. so all is well. the total is 49.99 (etc). to see this clearer change the last code line to

echo "<br/><br/> Total is $total";

 

[WilliamMute]

Not echoing out the total i.e see output below

query is : SELECT Sum(products.product_price * springrisecart.quantity) AS total, springrisecart.`session` FROM products INNER JOIN springrisecart ON products.id = springrisecart.product_id GROUP BY springrisecart.`session` HAVING springrisecart.`session`='f608b0f4448aaabf6ba839a18cd7e7d4'49.9900016784668

Total is

 

[jpadie]

my fault again. the total is there.

echo "<br/><br/> Total is ".$row['total'];

 

[WilliamMute]

Another thing, I noticed that the basket consist of old session values, is there anyway I can set the basket table data to expire after a cirtain period? so my basket table is not clogged with old data from previous day/week/month or even years data? or do I do it manually?

 

[WilliamMute]

You are a god! thaaaaaank you sooo much much much!!! you are a star!!! One thing though

Total is 125.96000289917

Formating? rounding up the decimal point?

 

[jpadie]

i assume you are up and working on the query now?

the answer to your question is yes. multiple ways of doing this. You are handling session management through cookies at the moment. i assume you want your cookies to last forever, so to speak.

your current cookie/session management is not doing you any favours. i think you should consider the following revised logic:

if incoming cookie exists
write value to database together with timestamp
reset cookie (with existing id value

every day (cron job) do the following two queries
delete * from cookietable where time now is greater than timestamp + timeout
delete * from sprinrisecart where sessionid NOT IN (select id from cookietable)

 

[jpadie]

Formating? rounding up the decimal point?

the world is your bivalvular mollusc in this case. to round to two decimal places use:

$total = round (floatval($row['total']),2 );

or for a complete solution

$total = "$" . number_format ( floatval($row['total']), 2, ".", ",");

 

[WilliamMute]

jpadie, you are a genious, I cant thank you enough! Thank you ever sooo much for your help, I really appreciate it. Now its left for me to study the whole code and understand every bit of it!

Thanks to all who contributed to this post! Jpadie the god!

 

[WilliamMute]

Hi am back again, hopefully for the last time.

I wrote a query together with a while loop to display the content of the shopping basket, but only returning one record while there are several records on the basket matching the sessionID any particular reason probably please? Query:

mysql_select_db($database_Connection, $Connection);

$query_Recordset1 = "SELECT * FROM springrisecart, products WHERE (springrisecart.session) = '$cart_id' AND (products.id = springrisecart.product_id)"; 
$Recordset1 = mysql_query($query_Recordset1, $Connection) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

 

[WilliamMute]

WHILE LOOP

	      while ( $row_Recordset1 = mysql_fetch_assoc($Recordset1) ) :
					$class = ($class === "even_row") ? "odd_row" : "even_row"; 	{ ?>
	  
	  
	  
	  <tr>
        <td><a href="[URL unfurl="true"]http://www.springrise.net/single_item.php?id=<?php[/URL] echo $row_Recordset1['id']; ?>"><?php echo $row_Recordset1['product_name']; ?></a></td>
        <td><?php echo $row_Recordset1['product_dimension']; ?></td>
        <td><?php echo $row_Recordset1['product_id']; ?></td>
        <td><?php echo $row_Recordset1['product_price']; ?></td>
        <td><img src="<?php echo "products/". $row_Recordset1['image']; ?>.jpg" alt="Your Product" height="25" width="28"></td>
        <td>&nbsp;</td>
      </tr>
    
	
	
	<?php 
  
} 
endwhile; 
?>

 

[jpadie]

am amazed the query returns anything at all (not being rude).

is what you are trying to do, to retrieve the name of each product?

if so

$sql = "
SELECT products.product_name
FROM springrisecart 
INNER JOIN 
  products 
  ON 
    products.id = springrisecart.product_id
WHERE 
   springrisecart.`session`='$cart_id'";

 

[WilliamMute]

Thanks jpadie,

Even with the new code above, it still returning 1 record name only.

 

[jpadie]

how many do you expect to be returned? is there a product name for each product?

let's force the issue a bit

$sql = "
SELECT products.product_name, products.id
FROM springrisecart 
INNER JOIN 
  products 
  ON 
    products.id = springrisecart.product_id
WHERE 
   springrisecart.`session`='$cart_id'";
$result = mysql_query($sql) or die (mysql_error());
while ($row=mysql_fetch_assoc($result)):
echo "<br/>";
echo "<br/>session: ". $cart_id;
echo "<br/>product id : ". $row['id'];
echo "<br/>name: ". $row['product_name'];
endwhile;

please post the output. thanks
Justin

 

[WilliamMute]

Its outputing nothing on the screen.