How to select and update only the last row in a table

Hangin' Around Joined: Sep 25, 2003 Posts: 30

Hi All,

I hope you can help me with a mysql query I need.

Given the table below:

Code:

CREATE TABLE `history` (


  `timeOfMove` datetime NOT NULL default '0000-00-00 00:00:00',


  `gameID` bigint(20) NOT NULL default '0',


  `curPiece` enum('pawn','bishop','knight','rook','queen','king') NOT NULL default 'pawn',


  `curColor` enum('white','black') NOT NULL default 'white',


  `fromRow` smallint(6) NOT NULL default '0',


  `fromCol` smallint(6) NOT NULL default '0',


  `toRow` smallint(6) NOT NULL default '0',


  `toCol` smallint(6) NOT NULL default '0',


  `replaced` enum('pawn','bishop','knight','rook','queen','king') default NULL,


  `promotedTo` enum('pawn','bishop','knight','rook','queen','king') default NULL,


  `isInCheck` tinyint(1) NOT NULL default '0',


  KEY `gameID` (`gameID`)


) ENGINE=MyISAM

What I want to do is to select and update only the 'timeOfMove' field in the table, but only for the last row in the table. I have played around with it for quite a while, and I can update the field with the now() function using an UPDATE...SET query, but it updates all rows. How to select only the last (most recent) row?

Thanks for any ideas you can suggest,

Nelson

registered · Posted: Fri Dec 02, 2005 9:43 pm

Try

Code:




UPDATE history SET timeOfMove = Now() ORDER BY gameID DESC LIMIT 0,1;

I haven't tested this, but it should work. I assume the last row is the one with the highest gameID. It should reorder them based on gameID descending, and then select the first row only.

Posted: Sat Dec 03, 2005 5:15 pm

Evaders99 wrote:

Try

Code:




UPDATE history SET timeOfMove = Now() ORDER BY gameID DESC LIMIT 0,1;

I haven't tested this, but it should work. I assume the last row is the one with the highest gameID. It should reorder them based on gameID descending, and then select the first row only.

I actually figured out what I needed after I posted. I only wanted the logged-in player to be able to do this to his games thru a form.

My solution, tested and working, was

Code:

$p = mysql_query("SELECT * from history,games where whitePlayer='".$_SESSION['playerID']."' OR blackPlayer='".$_SESSION['playerID']."' AND history.gameID=games.gameID ORDER BY timeOfMove DESC limit 1");


        $row = mysql_fetch_array($p);


$sql = mysql_query("UPDATE history SET timeOfMove=Now() WHERE timeOfMove='".$row['timeOfMove']."'");

Thanks for the help!

--Nelson

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

This is dangerously written.

where whitePlayer='".$_SESSION['playerID']."' OR blackPlayer='".$_SESSION['playerID']."' AND history.gameID=games.gameID

The way this is written it means
where whitePlayer='".$_SESSION['playerID']."'
OR
where blackPlayer='".$_SESSION['playerID']."' AND history.gameID=games.gameID

Are you sure that's what you need or do you need
where whitePlayer='".$_SESSION['playerID']."' OR blackPlayer='".$_SESSION['playerID']."'
AND
where history.gameID=games.gameID

You probably really mean the latter, in which case you need to write it as
where (whitePlayer='".$_SESSION['playerID']."' OR blackPlayer='".$_SESSION['playerID']."') AND history.gameID=games.gameID

Posted: Sun Dec 04, 2005 2:17 am

Yes, I did mean the latter. Thanks for pointing that out to me, Raven... I appreciate it. Cheers

Posted: Sun Dec 04, 2005 3:47 am

- Value Added Services Wink

Posted: Mon Jan 09, 2006 4:31 pm

Here's a question for you.. How do I echo(or print) a value from ONLY the last submitted record in a mySQL db using PHP? I am using a contact us db I created, on the first page of this admin area I want to show the contact name of ONLY the last submission. Searched all over the web and keep finding "close but no cigar" results. Any help is GREATLY appreciated.

Posted: Mon Jan 09, 2006 4:39 pm

Just do something like this SELECT column FROM table ORDER BY column DESC LIMIT 1

Posted: Mon Jan 09, 2006 7:47 pm

OMG! That was so simple I was blind to it! LOL thanks a ton...guess I have been staring at this script too long...