Change:
[php]$stmt = $pdo->prepare(“UPDATE current_messages SET id=id+1 ORDER BY id DESC”);[/php]
To:
[php]$stmt = $pdo->prepare(“UPDATE current_messages SET id=id+1”);[/php]
And I’m glad you figured it out, because your original post didn’t make much sense 
What you are doing is plain stupid coded by someone that obviously wanted to show what they thought they understood.
For something as simple and small as 8 records, a database is more overhead than needed. What exactly is the point in changing the id on insert? The id is used as a primary key for the data tables use, not for a users records. You want the last 8 inserted statements? You call the records in reverse order. The id should be a permanent identity to that record. Parents don’t rename their kids because they think the youngest should be Junior, so the oldest will now be Todd.
But, rather than fix the problem correctly you attack someone that is trying to get you to understand that you are going about the problem wrong. Fix the problem, not the symptom and you come out better.
I did them in reverse order for a reason. If I did it in ascending order I would be erasing the next record hence 7 moves to 8, 6 moves to 7,… etc. Just because you didn’t understand what I was doing doesn’t mean you have to tell me my design is bad. It works and does what I intended it to do, I just messed up the execution of it when trying to convert it. I figured out my stupid mistake. And it didn’t require any of the suggestions that were made to do it.
Okay, so you built it originally and didn’t understand how to do it properly. There is nothing wrong with trial by error, the code I wrote a year ago is not the same I would write today. But attempting to justify a bad past decision doesn’t make it a good decision now.
There is no possible reason to change a records id. The fact that you don’t understand this shows your grasp of core concepts. You are defeating the purpose of an auto incrementing column for one thing. Another thing is doing this in code at all, a trigger is far more useful. But if you want to explain your reasoning have at it? And no you don’t have to, but people that have designed far more used and complex systems than what this, have called you on flawed design. There is a reason for that too.
Example:
[php]
CREATE TABLE notifications (
ID int(11) NOT NULL AUTO_INCREMENT,
Title varchar(255) DEFAULT NULL,
Description varchar(255) DEFAULT NULL,
Status varchar(255) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
insert into test.notifications
(Title, Description, Status)
values
(‘test2’, ‘desc2’, ‘active’),
(‘test3’, ‘desc3’, ‘active’),
(‘test4’, ‘desc4’, ‘active’),
(‘test5’, ‘desc5’, ‘active’),
(‘test6’, ‘desc6’, ‘active’),
(‘test7’, ‘desc7’, ‘active’),
(‘test8’, ‘desc8’, ‘active’),
(‘test9’, ‘desc9’, ‘active’),
(‘test10’, ‘desc10’, ‘active’);
SELECT * FROM test.notifications order by ID desc limit 8;
[/php]
Returns:
[php]
10 test10 desc10 active
9 test9 desc9 active
8 test8 desc8 active
7 test7 desc7 active
6 test6 desc6 active
5 test5 desc5 active
4 test4 desc4 active
3 test3 desc3 active
[/php]
Are you sure? I remember saying this…
Now In PDO, a sql statement is a sql statement, if a sql statement works in mysqli that same statement will work in PDO. I don't think the problem is what the sql statement, I think the problem is what how you are trying to execute your PDO statements.
That suggestion didn’t get the wheels turning in you head? because it sure sounds like I nailed the issue.