<?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 )");
?>
* 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.
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?
<?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");
?>
$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 )
<?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());
?>
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:
<?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());
<?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 )
<?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 at
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 'SELECT id FROM stpatsblogtable ORDER BY id DESC LIMIT 3,1)' at
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.