Multiply two fields, insert result in next field

[lobfredd]

Hello

I though this code would work

mysql_query("UPDATE cart SET totprice = quantity * price WHERE ID='{$id}'")or die(mysql_error());

because this is working fine

mysql_query("UPDATE products SET rating = rating + 1 WHERE ID='{$id}'")or die(mysql_error());

 

Anything wrong with it, or how should i do it?

[Jessica]

are quantity and price columns in the table?

Try wrapping your math in parens.

 

Are you sure $id is set for both queries? What error do you get?

[lobfredd]

Both these queries are in the same ?php tag, so the $id is set yes, and the columns do exists yes, i do not get any errors, but the totprice field remains 0.

will try wrapping them now

 

mysql_query("UPDATE cart SET totprice = ('quantity * price') WHERE ID='{$id}'")or die(mysql_error());

 

this did not work either

[PFMaBiSmAd]

A) You should NOT store a value in a row that is derived from other values in the same row. That creates redundant data that you must now maintain.

 

B) Show us an example of the price and quantity values in the row you are trying to update. You either have a zero quantity in that row or a price that isn't a valid number.

 

Edit to match your edit above: By putting single-quotes around it, you are making it a string, consisting of the letters - q, u, a, n, t, ... When treated as a number, a non-numerical string will evaluate as a zero.

[lobfredd]

 

 

(i just translated the column names into english in my previous posts)

 

<?php
$con = mysql_connect("localhost","username","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);
$id = $_GET['id'];
mysql_query("INSERT INTO handlevogn (prod_id, vare, pris, bruker)
SELECT produkter.ID, produkter.title, produkter.price, '{$_SESSION['username']}' FROM produkter WHERE ID='{$id}' ON DUPLICATE KEY UPDATE antall = antall + 1")or die(mysql_error());

mysql_query("UPDATE produkter SET rating = rating + 1 WHERE ID='{$id}'")or die(mysql_error());

mysql_query("UPDATE handlevogn SET totpris = ('antall * pris') WHERE ID='{$id}'")or die(mysql_error());

mysql_close($con);
?>

[Jessica]

As was pointed out, you need to remove the quotes around them

[mrMarcus]

You really shouldn't have that second UPDATE query run without validating that the first UPDATE query was successful, especially if you want to keep your data correct.  Should the first fail for whatever reason, and the second fire successfully, you will end up with undesired results.

 

As said, remove single-quotes from ('antall * pris'), and while you're at it, assuming `ID` is a primary INT, remove the single-quotes from it, too:

 

WHERE ID='{$id}'

 

becomes:

 

WHERE ID={$id}

[lobfredd]

I just did that, but still same result, no error massage just totpris remains 0

Any suggestions?

<?php
$con = mysql_connect("localhost","username","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("db", $con);
$id = $_GET['id'];
mysql_query("INSERT INTO handlevogn (prod_id, vare, pris, bruker)
SELECT produkter.ID, produkter.title, produkter.price, '{$_SESSION['username']}' FROM produkter WHERE ID={$id} ON DUPLICATE KEY UPDATE antall = antall + 1")or die(mysql_error());

mysql_query("UPDATE produkter SET rating = rating + 1 WHERE ID={$id}")or die(mysql_error());

mysql_query("UPDATE handlevogn SET totpris = (antall * pris) WHERE ID={$id}")or die(mysql_error());

[PFMaBiSmAd]

You probably have a non-printing character as part of the price value, making it an invalid number. How did price information originally get inserted into the produkter table and what is the definition of the price column? AND as already stated, you should not even be keeping a calculated total in each cart row.

[lobfredd]

I just inserted it using the Insert page in phpmyadmin. and that column is FLOAT

[PFMaBiSmAd]

You should be using the prod_id column in the WHERE clause in your handlevogn table UPDATE statement, as that's the column that is getting the produkter.ID value.

 

You should also be forming your query statements in a php variable so that you can echo them to see if they contain what you expect. Had you echoed the query statement and then compared it with the row you are expecting it to operate on in your table, you would have seen that you are mixing up the ID columns.