Update Record Problem

[liamloveslearning]

Hi everyone,

 

Im about to build an update record but im unsure how it will work and how I code it  :-\.

I have a page of photographs. users can select multiple photos which are then stored in a db with his session id.

The session id is then passed to paypayl with the price where the user pays and is redirected back to my site. When redirected back to my site, the session id is posted back and so all the photographs are listed ready for download.

 

I need to implement an update "photo downloads" column +1 everytime its downloaded, does this make sense? Id greatly appreciate ANY help!, thanks

[-Karl-]

For that, you'd need to select the data, then use PHP maths to add 1 on to it. Then update the record.

[liamloveslearning]

Thanks Karl, My only concern is the data is coming in from just 1 variable? So would I have to query the db to say, Get all "picids" from the database where session name = $sessionvariable, then +1 and insert? if that makes sense?

[-Karl-]

Unless there is a way to do

UPDATE tablename SET `picid` = picid+1 where `sessionname` = '$sessionvariable'

 

That would mean you wouldn't have to SELECT the data prior to updating it.

[liamloveslearning]

That sounds like a perfect solution, so all data == session variable would be updated accordingly?

[liamloveslearning]

Just realised it cant work! I have 2 tables, 1 with al lthe photo details etc incl. download counter. and then the 1 which holds the pic id and session id. So it would need to take the pic id from the session table and find the pic id in the photos table and then update, does this make sense?

[-Karl-]

You'd need to JOIN the tables.

 

But I'm absolutely useless with JOINs, so I can't help any further.

[liamloveslearning]

Ahh okay, thanks for shedding light on the situation anyway

[-Karl-]

Here's some information that could help.

http://dev.mysql.com/doc/refman/5.1/en/join.html

 

In your instance, you have a common field, pic id. So that would just be a easy join.

 

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

 

Possibly. Idk, I'm clueless, I should really read up on them. I've just never had a reason to.

[TeddyKiller]

So it would need to take the pic id from the session table and find the pic id in the photos table and then update, does this make sense?

$query = mysql_query("SELECT s.id, p.* FROM `session_table` as s, `photos_table` as p WHERE p.id = s.id");

You might need the change s.id to the pic id, so it would get the pic id. The 's.' has to stay there, so change id. If you have a image id session or whatever, add in another where before p.id = s.id. If you get me, you'll need to provide more information, and possibly some code.

 

To update.. depending on what you are trying to do...

$query = mysql_query("UPDATE 'photos' WHERE ... ..");

 

Hope this makes more sense, although you'd need to post code to be able to get better help.

[liamloveslearning]

Thanks teddy Killer, I dont quite understand how it works but ill try my best! my current code to selecte all the picture names from the db is

mysql_select_db($database_saucy_connection, $saucy_connection);
$query_getses = sprintf("SELECT photo_downloads.id, photo_downloads.photo_id, photo_downloads.sess_id, photo_downloads.photo_name, photo_downloads.photo_price, model_pictures.id, model_pictures.user_picture, model_pictures.picture_downloads FROM photo_downloads, model_pictures WHERE model_pictures.id=photo_downloads.photo_id AND sess_id = %s ", GetSQLValueString($colname_getses, "text"));
$query_limit_getses = sprintf("%s LIMIT %d, %d", $query_getses, $startRow_getses, $maxRows_getses);
$getses = mysql_query($query_limit_getses, $saucy_connection) or die(mysql_error());
$row_getses = mysql_fetch_assoc($getses);