UPDATE column in row with 4th highest id in mySQL table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Leao
Forum Commoner
Posts: 49
Joined: Mon Aug 21, 2006 8:57 pm
Location: London

Post by Leao »

volka wrote:Then it's either update-time or two querries like
jayshields wrote:I would do it the long-winded way and use 2 queries, one for grabbing the ID of the row to update and one for updating it.

This isn't going to be a very good example, but this should work:

Code: Select all

SELECT `ID` FROM `tbl` ORDER BY `ID` DESC LIMIT 3, 1
UPDATE `tbl` SET `whatever` = 'something' WHERE `ID` = $idfromabove
Hi, I tried what jayshields suggested but it didn't seem to work. Although I didn't receive an error message, the script didn't seem to make my mySQL table change at all. I was using the following code:

Code: Select all

<?php
mysql_connect("mysqlserver", "myusername", "mypassword") or die(mysql_error());

mysql_select_db("stpatsblog") or die(mysql_error());

$stpatsblogtext = mysql_real_escape_string($_POST['stpatsblogtext']);

$myid = mysql_query("SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3, 1") or die(mysql_error());
$result = mysql_query("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' WHERE id = '$myid'") or die(mysql_error());  ?>
Any idea what I did wrong?

Leo
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

mysql_query returns a result resource.
:arrow: http://de2.php.net/mysql_fetch_array
Leao
Forum Commoner
Posts: 49
Joined: Mon Aug 21, 2006 8:57 pm
Location: London

Post by Leao »

Sorry I've been so slow. I think I've sorted it out.

Thanks, leao

Code: Select all

<?php
mysql_connect("mysqlserver", "myusername", "mypassword") or die(mysql_error()); 

mysql_select_db("stpatsblog") or die(mysql_error());

$stpatsblogtext = mysql_real_escape_string($_POST['stpatsblogtext']);

$result = mysql_query("SELECT * FROM stpatsblogtable ORDER BY id DESC LIMIT 3, 1") or die(mysql_error());
$row = mysql_fetch_array( $result );
$myid = $row['id'];

$result = mysql_query("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' WHERE id = '$myid'") or die(mysql_error()); 
?>
Post Reply