Page 1 of 2
UPDATE column in row with 4th highest id in mySQL table
Posted: Mon Oct 16, 2006 6:21 am
by Leao
Hi,
Could you tell me how to write a PHP script that would insert data via an HTML form on the row in a mySQL table with the 4th highest/most recent id?
I've experimented with the attached script but it doesn't work at all.
Many 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("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' WHERE id = (SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3,1 )");
?>
Posted: Mon Oct 16, 2006 6:42 am
by volka
http://dev.mysql.com/doc/refman/4.1/en/update.html wrote: Starting from MySQL 3.23, you can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause works as follows:
* Before MySQL 4.0.13, LIMIT is a rows-affected restriction. The statement stops as soon as it has changed row_count rows that satisfy the WHERE clause.
* From 4.0.13 on, LIMIT is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.
If an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified by the clause. ORDER BY can be used from MySQL 4.0.0.
Code: Select all
UPDATE
stpatsblogtable
SET
stpatsblogtext = '$stpatsblogtext'
ORDER BY
id DESC
LIMIT 4
Posted: Mon Oct 16, 2006 7:20 am
by Leao
Hi, thanks for the post. I've tried the attached script but it changes all of the first 4 rows rather than just the 4th. How could I modify the code below so that it would only change the column stpatsblogtext on the row with the 4th highest/most recent id?
Many, many thanks again,
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("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' ORDER BY id DESC LIMIT 4");
?>
Posted: Mon Oct 16, 2006 7:28 am
by volka
ah, only the 4th....
LIMIT 3,1 is correct.
edit: would be, but update only supports LIMIT row_count, not LIMIT offset,row_count

Posted: Mon Oct 16, 2006 7:38 am
by Leao
Hi,
While:
Code: Select all
$result = mysql_query("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' ORDER BY id DESC LIMIT 4");
updated all of the first 4 rows. On the other hand:
Code: Select all
$result = mysql_query("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' ORDER BY id DESC LIMIT 3,1");
didn't appear to do anything at all, even though it should presumably update the row with the 4th highest id.
Any ideas about what I'm doing wrong?
Many, many thanks again,
Leao
Posted: Mon Oct 16, 2006 7:46 am
by volka
Leao wrote:Any ideas about what I'm doing wrong?
Yeah, you do not have any kind of error handling
Code: Select all
$query = "UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' ORDER BY id DESC LIMIT 3,1";
$result = mysql_query($query) or die(mysql_error());
But this will only tell you that UPDATE does not support LIMIT offset,row_count
does mysql_error() tell you something when you use your query
UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' WHERE id = (SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3,1 )
?
Posted: Mon Oct 16, 2006 7:49 am
by jayshields
volka wrote:update only supports LIMIT row_count, not LIMIT offset,row_count

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
Edit: Or yeah, use an UPDATE SELECT like volka has shown above - if it works..
Posted: Mon Oct 16, 2006 8:21 am
by Leao
Hi Volka,
mysql_error() told me:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
This is the PHP that I used:
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']);
$query = "UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' ORDER BY id DESC LIMIT 3,1";
$result = mysql_query($query) or die(mysql_error());
?>
The '1' the mysql_error() is referring to is
ORDER BY id DESC LIMIT 3,1
Thanks again,
leao
Posted: Mon Oct 16, 2006 8:47 am
by Leao
Hi Mr Shields,
Thanks for your script. 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?
Thanks again,
Leo
Posted: Mon Oct 16, 2006 9:08 am
by volka
Leao wrote:Hi Volka,
mysql_error() told me:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
This is the PHP that I used:
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']);
$query = "UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' ORDER BY id DESC LIMIT 3,1";
$result = mysql_query($query) or die(mysql_error());
?>
The '1' the mysql_error() is referring to is
ORDER BY id DESC LIMIT 3,1
Thanks again,
leao
Yes, this was expected and obvious.
Question is:
volka wrote:does mysql_error() tell you something when you use your query
UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' WHERE id = (SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3,1 )
?
Posted: Mon Oct 16, 2006 9:20 am
by Leao
Hi, apologies for that. I changed the PHP to:
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("UPDATE stpatsblogtable SET stpatsblogtext = '$stpatsblogtext' WHERE id = (SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3,1)") or die(mysql_error());
?>
I received the following error message, finishing abruptly with the word
atYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3,1)' at
Any ideas? Many thanks again,
Leao
Posted: Mon Oct 16, 2006 9:25 am
by feyd
Your version of MySQL doesn't support subqueries.
Posted: Mon Oct 16, 2006 9:30 am
by volka
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html
Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.
What's your mysql server version?
Posted: Mon Oct 16, 2006 9:30 am
by Leao
I'm running version 4.0.24 of MySQL.
Leao
Posted: Mon Oct 16, 2006 9:35 am
by volka
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