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]

Silly old me, connection line missing....

this is actually the error that I am getting

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 '*quantity) as subtotal, SUM(subtotal) as total FROM , ' at line 1

Cheers for helping

 

[skiflyer]

Right so... it's not mysql_fetch_row() that's really having an issue, it's mysql_query(), the problem is you're not doing any error handling, and a mysql_query() error is non-fatal, so you're continuing on until the error bites you again in mysql_fetch_row().

In short, something is wrong with your query... echo the $query variable and take a look at it, it's currently not valid syntax, likely a dropped comma or extra semi-colon or something of that sort.

 

[WilliamMute]

its not even letting me echo out the query.

is there another way of echoing it out?

 

[WilliamMute]

Does anyone know an alternative way of doing the sum to calculate the total cost based on multiplying each item on my basket by the price of the item in the products table and the quantity required on the basket table am using sessions.

Any suggestion or easier way ?

 

[jpadie]

$this->springrisecart.quantity

this kind of notation is unfamiliar to me. where does it come from? if it is an object are you not calling a function of that class? i.e.
should it not be $this->springrisecart.quantity() (added brackets)?

i haven't looked at your syntax in detail but there is nothing wrong with doing your calculation in mysql. i prefer to do things in php and suffer a small speed penalty. i would simply do a query to retrieve the product price of each product id in your session variable (use the IN notation) and then do some multiplying and addition in php.

 

[WilliamMute]

Hi,

I found the style of notation on a tutorial on the web, I must admit that I personally found it hard to follow but because it was the only tutorial I could find on Cart creation so I adapted it.

Is there any other easier method you could suggest? How would I go about doing it in PHP? I notice you suggestion above which seems quite logical but I cant get the IN notation part... any examples please?

Thanks alot

 

[jpadie]

for example:
you have three products in your cart at the moment so your session variable (relevant element of) looks like
$_SESSION['products'][3]=10
$_SESSION['products'][43]=3
$_SESSION['products'][296]=5

where the shape is
products[productid] = quantity

$in = "";
foreach ($_SESSION['products'] as $id=>$quant):
 $in .= "'".$id ."',";
endforeach;
$in = rtrim ($in, ",");
$sql = " Select 
          productid, productprice 
         FROM 
          products
         WHERE
          productid IN ($in);
$result = mysql_query($sql);
$total = 0;
while ($row = mysql_fetch_assoc($result):
 $price += $_SESSION['product'][$row['productid']] * $row['productprice'];
endwhile;
echo "total price is $total";

 

[skiflyer]

Back to the original problem... what do you mean it's not letting you echo the query?

I'm simply talking about putting

echo '<pre>';
echo $query;
echo '</pre>';

Just before your call to mysql_query() & sharing your output so we can see if you're having trouble producing the query.

 

[WilliamMute]

Hi guys,

Firstly, jpadie, am trying to study your code to understand it at the moment so will let you know the outcome.

Skyflyer, here is the output of the query:

SELECT SUM(.product_price*quantity) as subtotal,
             SUM(subtotal) as total
             FROM ,
             WHERE session='' AND
             .id = .product_id
             GROUP BY .product_id


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 59

Thnaks for your help. ( Am a little slow as PHP is not one of my strengths so pls bare with me, will get there soon.)

 

[skiflyer]

well there's your problem... your FROM section of the query is empty.

Also, your code indicates it's meaning to put some value before all those dots, and it's not coming through.

Also, do you really want session='' or do you expect a value in there?

And lastly, without knowing your table structures there's no way to know if that SQL is even valid... your and id = product_id line is certainly suspicious... since if your PHP code were outputting values it would be asking for both of those from the same table, I'm guessing one or the other should be this->products

 

[WilliamMute]

Hi,

I have made a slight alteration to my query now I get this

SELECT SUM(products.product_price) *(springrisecart.quantity) as subtotal,
             SUM(subtotal) as total
             FROM springrisecart, products
             WHERE (springrisecart.session) = 'aaeb4f91f7695b77b61fffb4b04fbfa3' AND
             (springrisecart.product_id = 'products.product_id'
             GROUP BY (springrisecart.product_id)


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 56

What do you think?

 

[skiflyer]

Well first of all, change

mysql_query($query);

to

mysql_query($query) or die(mysql_error());

That'll help us in helping you quite a bit.

Secondly, I can tell you right off the bat you don't want single quotes around products.product_id... and you don't want that open parens right after AND... and you do want parens around something in your select statement, I'm guessing

SELECT SUM(products.product_price * springrisecar.quantity) AS subtotal

 

[WilliamMute]

Thanks for that tip.

I have changed the code to this,

mysql_select_db($database_Connection, $Connection);

$query = "SELECT SUM(products.product_price * springrisecart.quantity) AS subtotal,
             SUM(subtotal) as total
             FROM springrisecart, products
             WHERE (springrisecart.session) = '$cart_id' AND
             (springrisecart.product_id = 'products.id'
             GROUP BY (springrisecart.product_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;

And Now I get this error

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 'GROUP BY (springrisecart.product_id)' at line 6

Line six dont even have that code thats what I dont get.

 

[vacunita]

You are missing a parenthesis.

$query = "SELECT SUM(products.product_price * springrisecart.quantity) AS subtotal,
             SUM(subtotal) as total
             FROM springrisecart, products
             WHERE (springrisecart.session) = '$cart_id' AND
             (springrisecart.product_id = 'products.id'[red]<=missing ) here[/red]
             GROUP BY (springrisecart.product_id)";

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[WilliamMute]

Update, this is the error am getting just going through the code line by line.

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

Unknown column 'subtotal' in 'field list'

 

[WilliamMute]

thanks for that tip!

the follow up update, I dont really understanmd what is happening anymore, I've lost it. I thought (subtotal) was part of the SUM () function? ok am really really lost Help!!! Please....


Thanks

 

[skiflyer]

Firstly... from way back when.. Line 6 was thrown by MySQL, so it was a reference to line 6 of the SQL query

Secondly... subtotal is the output of the first SUM, and total is the output of the second SUM. I don't know my aggregate functions well enough these days to help with that one, but now you're into a straight SQL problem... I would suggest trying the MySQL forum.

 

[jpadie]

i don't think that your query is going to work. it looks to me like you would be better off doing a subquery in query. the mysql forum might be a better place to pose the question.

 

[WilliamMute]

Ok guys will do as well as check for other tutorial on shopping cart. Thanks for your help anyway. jpadie I was looking at the example you gave above, I cant seem to understand it to clearly it seems a little on the advance stage dont know if you've got any simpler idea or perhaps could break it down a little maybe. If not, thank you all for your help, I appreciate your time.