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.

 

[jpadie]

a bit more broken down for you

$in = "";
// this takes the session element and iterates through each one creating a long string called $in.  this will be used later on in the query.

foreach ($_SESSION['products'] as $id=>$quant):
 $in .= "'".$id ."',";
endforeach;


// this line just removes the trailing comma
$in = rtrim ($in, ",");

//the IN notation just says If the productid is in the set that is contained in the brackets
//so you will end up with something like 
// productid in ('3','43', '296')
$sql = " Select 
          productid, productprice 
         FROM 
          products
         WHERE
          productid IN ($in);

$result = mysql_query($sql);

//presets the variable
$total = 0;

//iterate through the recordset
while ($row = mysql_fetch_assoc($result):
 $price += $_SESSION['product'][$row['productid']] * $row['productprice']; //does the multiplacation.  the += notation causes an increment
endwhile;
echo "total price is $total";

 

[WilliamMute]

Thanks sooo much jpadie for that explanation, that makes more sense now, but I cant see any reference to the cart_table not the products_table or is the products_table what you called "product"? and do I need to say for example $_Session['cart_table.sessionID'] in place of your

$price += $_SESSION['product'][$row['productid']] * $row['productprice'];

?

Thank you.

 

[jpadie]

how do you construct your cart and your session variables?

 

[WilliamMute]

Basically, I set my session using the script below

<?php
 if(!isset($HTTP_COOKIE_VARS['cart_id'])) {
     $cart_id = md5(uniqid(rand()));
     setcookie("cart_id", $cart_id, time() + 14400);
 } else {
     $cart_id = $HTTP_COOKIE_VARS['cart_id'];
 }

?>

Then, When a product is clicked on, it is added to a table called springrisecart
field added to table are:
Session
Product_id (this is the retrieved via the $_GET variable function and is identical to the product ID in the general products table)
Quantity

now

to display the cart together with the total price of the products in the cart table (springrisecart).

A new to all this so I hope this is what you are asking.

 

[jpadie]

i get you now.

try the following query instead:

SELECT 
        Sum(products.product_price * springrisecart.quantity) AS total
FROM 
        product 
        INNER JOIN 
                    springrisecart
                    ON 
                    products.product_id = springrisecart.product_id
GROUP BY 
         springrisecart.session
HAVING 
        springrisecart.session=1;

 

[jpadie]

note that product in the from line should products (plural). formatting has gone wrong too...

 

[skiflyer]

Guessing at your application you need the subtotal & thte total though... so you can say

1x Foo @ 5.29 = 5.29
2x Bar @ 3.33 = 6.66

Total = 11.95

no?

 

[WilliamMute]

Yes Yes Yes! you got me on the spot EXACTLY what i was looking for.... am gonna try them now! will let you know outcome. Yes also Skiflyer I want the total and subtotal how would I go bout that please?

Thank youuuuuuu soooo much guys!

 

[WilliamMute]

Hi guys, this is what the code looks like now

<?php
$query = "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=1";
$Total = mysql_query($query, $Connection) or die(mysql_error());
$row = mysql_fetch_assoc($Total);


?>

 

[WilliamMute]

Hi guys, this is what the code looks like now

<?php
$query = "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=1";
$Total = mysql_query($query, $Connection) or die(mysql_error());
$row = mysql_fetch_assoc($Total);


?>

 

[WilliamMute]

Sorry about the double post, it seems that there is a bug somewhere not all the information is being posted when I hit submit. basically the above is what the code looks like now but I now get this error msg;

Unknown column 'springrisecart.session' in 'having clause'

The weird thing is that, the session column does exist in my DB...

 

[jpadie]

add quotes to the 1

springrisecart.session='1'";

of course in reality you need to use the proper value ffor the session.

 

[WilliamMute]

Hi jpadie,

I have tried the quotes, still the same error. Secondly you said "of course in reality you need to use the proper value ffor the session." what values do I use there in real life? do I use a variable you mean?


Thirdly, I have managed with the great help of the MYSQL forum to get the old version of the query not returning error, now how would you recommend to get the total values echoed out? see the working MYSQL code below please

mysql_select_db($database_Connection, $Connection);

$query = "SELECT SUM(products.product_price * springrisecart.quantity) AS subtotal
            FROM springrisecart, products
             WHERE (springrisecart.session) = '$cart_id' AND
            (springrisecart.product_id) = 'products.id'";
 echo '<pre>';
echo $query;
echo '</pre>';
  $result = mysql_query($query, $Connection) or die(mysql_error());
  $row = mysql_fetch_assoc($result);
   return $row['total'];
  $Final = $result;

echo $Final;

 

[WilliamMute]

The above code produces

SELECT SUM(products.product_price * springrisecart.quantity) AS subtotal
            FROM springrisecart, products
             WHERE (springrisecart.session) = 'a95a23dfdc265c46353bfb07f10e1485' AND
            (springrisecart.product_id) = 'products.id'

i.e not echoing out the total...

 

[skiflyer]

Ok... So this gives you a resultset which you can loop over, while incrementing a totalling variable... i.e.

But realistically... you need more, you need the products.name (or whatever), and quantity and product_price all returned in your select statement so you can echo out the information the user really cares about.

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

echo 'Total: '. $total;

 

[jpadie]

I have tried the quotes, still the same error. Secondly you said "of course in reality you need to use the proper value ffor the session." what values do I use there in real life? do I use a variable you mean?

the query i wrote works just fine. it returns the total in the manner that you wanted... if i have guessed your field names correctly

the error you are getting indicates that one of the fields names has been incorrectly named - i.e. the session. please post your precise db schema for both tables.

 

[WilliamMute]

In response to japadie,

here is my table for products

 	int(11)  	   	PRI  	NULL  	auto_increment
product_id 	varchar(5) 	  	PRI 	  	 
product_name 	varchar(60) 	  	  	  	 
product_details 	text 	YES 	  	NULL 	 
product_price 	float 	  	  	0 	 
image 	varchar(50) 	YES 	  	NULL 	 
product_dimension 	varchar(40) 	YES 	  	NULL 	 
product_colour 	varchar(40) 	YES 	  	NULL 	 
product_material 	varchar(50) 	YES 	  	NULL 	 
Category 	varchar(50) 	  	  	  	 
Gift 	char(3)

Then the Basket Table is

session  	tinytext  	   	   	   	 
product_id 	varchar(5) 	  	  	  	 
quantity 	tinytext 	  	  	  	 
id 	int(4) 	  	PRI 	NULL 	auto_increment

 

[jpadie]

assuming your two tables are called
"products" and "springrisecart" the sql query below will work to return the total.

$sessionid = "" ;//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'";
$result=mysql_query($sql);
$row=mysql_fetch_assoc($result);
echo $row['total'];

 

[WilliamMute]

Hi skiflyer,

I tried implementing your example above but the page just returns blank.

here is what the code looks like now;

mysql_select_db($database_Connection, $Connection);

$query = "SELECT SUM(products.product_price * springrisecart.quantity) AS subtotal
            FROM springrisecart, products
             WHERE (springrisecart.session) = '$cart_id' AND
            (springrisecart.product_id) = 'products.id'";

  $result = mysql_query($query, $Connection) or die(mysql_error());
  $row = mysql_fetch_assoc($result);
   return $row['total'];

 

[WilliamMute]

Hi japadie


Am getting

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

This is how the code looks like now

mysql_select_db($database_Connection, $Connection);
$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'";
$result=mysql_query($sql, $Connection);
$row=mysql_fetch_assoc($result);
echo $row['total'];