Update the last entry of one user out of many users

[wudz]

Hi,
I wonder if someone can show me the correct code.
I wish to update details of a user out of many users in the table, but only in his last updated file. He may have many previous files but I do not want any of these altered only his most upto date entry.
The script below alters all his previous files which I do not require.
How do I go about just doing his/her most uptodate entry..

//update purchased credit table
$query = "UPDATE AUCTION_purchcredit
SET pp_verified = 'Y',
ver_date = '$VERIFIEDATE'
WHERE user_id='".$_SESSION ["AUCTION_LOGGED_IN"]."'";
$res = mysql_query($query);
if(!$res) {
MySQLError($query);
exit;
}

Cheers in anticipation

Newbie John

 

[jpadie]

could you add a nested subquery looking for the max of a particular value where the userid is $_SESSION['AUCTION_LOGGED_IN']? this seems the neatest way to do it, although two separate queries would also get you there.

lots of stuff on nested queries in the mysql manual.

 

[sleipnir214]

What version of MySQL are you using?

The newer versions allow you to use ORDER BY and LIMIT clauses in an UPDATE query.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[wudz]

Hi,
Thanks for that speedy response...Great.

I have a field ID (auto inc) in the table, could I use this as a reference...highest id associated with this user..
Being a newbie not sure of all the available functions and syntax.

MySQL version is..
4.0.24_Debian-10sarge1

Cheers

John

 

[sleipnir214]

Just to flesh out my answer....

Suppose I have the table bar:

[tt]+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| A | 20 | NULL |
| A | 30 | NULL |
| B | 10 | NULL |
| B | 30 | NULL |
| C | 40 | NULL |
+--------+--------+--------+
[/tt]

Then the query:

UPDATE bar SET field3 = NOW() WHERE field1 = 'A' ORDER BY field2 DESC LIMIT 1

will change the table to read:
[tt]+--------+--------+---------------------+
| field1 | field2 | field3 |
+--------+--------+---------------------+
| A | 20 | NULL |
| A | 30 | 2006-02-03 17:54:25 |
| B | 10 | NULL |
| B | 30 | NULL |
| C | 40 | NULL |
+--------+--------+---------------------+[/tt]





Want the best answers? Ask the best questions! TANSTAAFL!

 

[wudz]

Hi Sleipnir,

Wow! the versitility of this code...I think I understand the jist of it, would never ever have come close to coming up with this...think I should keep to my FLASH graphics..hi

I will give it a whirl and get back to you, may be tomorrow as my eyes are very nearly closed..

Great bunch of guys in this forum, just as in the Flash one.....GREAT

Thanks lads

John

 

[wudz]

Hi Sleipnir,
Could not wait....fantastic all seems to do as I wanted.... now I can sleep soundly....

Here is what I ended up with using your code..


//update purchased credit table
$query = "UPDATE AUCTION_purchcredit
SET ver_date = NOW(),
pp_verified = 'y'
WHERE user_id='".$_SESSION["AUCTION_LOGGED_IN"]."'
ORDER BY id DESC LIMIT 1";

$res = mysql_query($query);
if(!$res) {
MySQLError($query);
exit;
}

It's good to know that people are there to help us thickies.. Thank you

Cheers

john

 

[jpadie]

that's quite cool - never knew mysql did that. doesn't feel too ansi compliant but very useful nonetheless.

thanks
Justin

 

[sleipnir214]

You're right -- it's not ANSI-SQL compliant (see link).

But I'm not to worried about RDBMS-to-RDBMS portability.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[wudz]

Hi,
just another spanner in the works...
How can I do a SELECT query on the above table with the same criteria..i.e.
I wish to access a field using the user id of their last place in the table amongst other users.
Tried a cobble of your last code which works great on the UPDATE but get error check your version ofmysql for the correct syntax.

//get amount of credit purchased
$query = "SELECT pur_credit AUCTION_purchcredit
WHERE user_id='$id'
ORDER BY id DESC LIMIT 1";

Cheers in anticipation

John