Simple SQL Query to UPDATE a field using a Session Variable

[ikon]

Hi All,

 

I'm passing a variable through my website which is $_SESSION['photo'] to update a table during my checkout however i know for sure that the SQL query is wrong but i don't know how to correct it.

 

My Table in my SQL Db is structured as follows:

 

od_id    pd_id    username    image      image_id (autoincremented)

 

 

The fields username, image and image_id all update when a photo is uploaded earlier in my site but i am trying to capture the od_id (order ID) and pd_id (product id) to update when the user goes to my checkout.

 

The query I am using to try and do this is:

 

$sql2 = "UPDATE tbl_order_image(od_id, pd_id)
VALUES ($orderId, {$cartContent[$i]['pd_id']})
WHERE ($_SESSION = ['photo']";

$result = dbQuery($sql);	

 

Its not throwing an error which is good however, the table isn't updating.

 

If anyone could shed some light on this it would be wonderful - this could be the difference between a 2.2 and a 1st in my degree

 

Thanks All!

 

[BlueSkyIS]

the SQL is wrong as displayed, which is why the table isn't updating. you're not seeing an error because either your error reporting is turned off, or the dbQuery function isn't displaying an error. I don't know what the dbQuery function is (it must be custom), so I can't tell what's happening there.

[ikon]

Here is the whole query:

 

if ($orderId) {
for ($i = 0; $i < $numItem; $i++) {
			$sql = "INSERT INTO tbl_order_item(od_id, pd_id, od_qty)
					VALUES ($orderId, {$cartContent[$i]['pd_id']}, {$cartContent[$i]['ct_qty']})";

			$sql2 = "UPDATE tbl_order_image(od_id, pd_id)
					VALUES ($orderId, {$cartContent[$i]['pd_id']})
					WHERE ($_SESSION = ['photo']";
			$result = dbQuery($sql);	
		}

 

The first query inserts the order id, product id and quanty into my order table - which works perfectly and the 2nd is trying to do the same without the qty but i really dont know how to structure my WHERE part - the 'photo' session is being carried across and is also the filename for the image which is stored in the DB so when a user checks out an order it updates the order id and product id into the row.  When i upload the image previously i am entering 0, 0 into pd_id and od_id fields so that i can use an UPDATE at this stage.....

 

Thanks for the rapid response.

 

Regards,

[paul2463]

Hi IKON

 

you never actually run the second query, you create it but there is no

 

$result2 = dbQuery($sql2);

 

in your statement

[BlueSkyIS]

this query is still not valid:

 

$sql2 = "UPDATE tbl_order_image(od_id, pd_id)
					VALUES ($orderId, {$cartContent[$i]['pd_id']})
					WHERE ($_SESSION = ['photo']";

 

you're missing a right paren in the WHERE clause. i'd just remove the left one.

[ikon]

I've found a way of cracking this that will work but really need your guys help on how i would write the query....

 

The Database has values 0,0 written in when i upload an image and during the checkout (when the od_id is generated) i want to put the od_id and pd_id (product ID) into the value that are 0 and 0.....

 

So i need to write

 

if ($orderId) {
for ($i = 0; $i < $numItem; $i++) {
			$sql = "INSERT INTO tbl_order_item(od_id, pd_id, od_qty)
					VALUES ($orderId, {$cartContent[$i]['pd_id']}, {$cartContent[$i]['ct_qty']})";

			$sql2 = "UPDATE tbl_order_image(od_id, pd_id)
					VALUES ($orderId, {$cartContent[$i]['pd_id']})
					WHERE (od_id = 0 and pd_id = 0)";
			$result = dbQuery($sql);	
		}

 

Would WHERE (od_id = 0 and pd_id = 0)" work?  if not, what do i need to change ?

 

Regards,