mysqli_query($con,“UPDATE current_messages SET id=id+1 ORDER BY id DESC”);
I can’t figure out a way to add to the primary index to move rows down one to insert a new one in position 1 (of 8 displayed). I first delete slot 8 and with mysqli I can move the index up by one and then follow that with an insert. I can figure out how to use pdo to pass variables to it but I can’t figure out how to add 1 to it like this mysqli_query does. Does anyone know how do a similar operation but with PDO?
It sounds like you might have a bad database design or for what your end goal is, may be going about it the wrong way. It would be helpful if you posted your DB schema and more detail of what you are doing rather than the way you are trying to do it.
It is not poor design. It works fine with mysqli fine.
It is a database table that displays the eight most current messages putting the most current message at position 1 (id=1). So when I add a message #8 gets deleted. Then id 7 needs to be id 8, id 6 becomes 7, etc.
until 1 becomes 2. This second step of the three that the mysqli-query does is something I can’t figure out how to do with a PDO. Can you think of a way to do that? The only field that needs to change is the id (primary key which is 1 thru 8). The third step adds the new message in id 1.
Actually it is a bad design. You should be using a time stamp, not incrementing ID numbers.
If you aren’t going to answer my question why do you even bother to reply to this? There is nothing wrong with the design. It isn’t blog posts it is eight rows that displays links to questions and messages in pdf and word files, videos and initials of the speaker with a link to their picture. Don’t reply to this unless you have something constructive to say instead of just condemning something as bad design when you don’t even know what the design is.
I did answer your question and gave you the correct information. What you previously posted makes it quite clear it is a bad design. If your not willing to learn how to do things right then so be it. Don’t expect anyone else to help you.
you didn’t answer my question at all. you just said it is a bad design. I didn’t ask for your opinion of my design. I asked you how to do what the mysqli-query does with pdo. just answer that question whether or not my design is bad in your opinion or not. And you didn’t give me any suggestions on what you call good design.
Ok, create a time stamp column and set it up the DB to automatically insert the current time stamp when your data is inserted. Then when you select your data, you select * FROM yourtablename ORDER BY timestamp_column DESC LIMIT 8
That will always give you the most recent 8 records.
As far as using PDO, you can download my PDO bumpstart Database from the link in my signature. That will get you up and running with PDO in minutes.
I don’t want the database table to hold all the information. I only want it to hold the last eight messages. Your solution displays the last eight messages but holds the information for everything which is NOT what I want to do. And the table that holds all the information has things that were created years ago and I have no way of giving it a timestamp. So that solution WILL NOT WORK for what I am trying to do.
I tried following your link and I couldn’t figure out how to access your pdo bumper attachment.
Here’s how I would do it…
I would have a table with ID (AutoNumber) and then your current_messages field.
I would just do normal inserts into the table, then have a trigger in mysql that fire after the insert, deleting all the rows that are not in the last 8.
[php]CREATE TRIGGER Delete_mesages
AFTER INSERT ON 'current_messages ’ for each row
begin
delete * from current_message where ID not in (select ID from current_message order by ID Desc limit 8)
END;[/php]
This should keep the table to always 8 rows and you can just insert into it without having to worry about updating id’s.
There are only eight rows in this table. it is a queue of the last eight questions/messages/speaker initials. You are not answering my question you are trying to redesign my table, which isn’t what I want to do. Why does everyone who answers this think I want to redesign things. Surely there has to be a way to add 1 to the id like the mysqli command does. There are no timestamps like the first person suggested because many of the things in the database table with all the information was created years before I created the website without any accurate information on when it was created. That is why I created the small table like a queue. The last element is deleted the rest incremented and then the new element is added in position 1. The table with all the information has around 1000 rows so I’m not about to add more columns to it.
First I just like to say that people here are just trying to help, they are giving the best answer that they can. Just because it doesn’t fit you “exact” needs or wants doesn’t mean they didn’t try to give you a “possible” solution.
While this isn’t going to resolve your issue totally, I wouldn’t worry about moving ids or rearranging them.
I am in the process of developing a trivia game and if a question along with its possible answers gets deleted, I’m not going to rearrange the whole database table, just think of what a nightmare that would be if I get a lot of questions. What I simply do is ignore the the empty record (id in this case), by doing something like the following
[php] try {
$this->query = “SELECT id, question, answer1, answer2, answer3, answer4, correct FROM nasaTrivia WHERE id>=:id ORDER BY id ASC LIMIT 1”;
$this->stmt = $pdo->prepare($this->query);
$this->stmt->execute([’:id’ => $id]);
return $this->stmt->fetch(PDO::FETCH_OBJ);
} catch (Exception $ex) {
echo $ex->getMessage();
}[/php]
The id is an integer and with PDO it’s easy to detect the EOF if I should happen to get to it, for the final result will be false (0).
First off this is just plain silly
[php]mysqli_query($con,“UPDATE current_messages SET id=id+1 ORDER BY id DESC”);[/php]
There is no point in even having an order by, unless you’re going to use a limit. Since you’re updating everything just remove it.
[php]mysqli_query($con,“UPDATE current_messages SET id=id+1”);[/php]
Next I gave you a viable solution that’s more elegant and less error prone to what you’re trying to accomplish. You think in terms of only 1 person using you application and again that might be the case, but you never specified. I think in terms of many people using the application at once and which all your code and the way you have it designed isn’t going to work properly and occasionally you’re id’s sequence is going to be corrupted.
2 people use your application…
You do your little update statement, all Id’s are updated by 1. Then person 2 triggers the same routine, and then id’s are updated by 1 again. Now the first routine does the insert and then the second routine does the insert.
Now you have have a table with these ids (1,1,3,4,5,6,7,8) - You’re id’s are messed up with your 8, or perhaps your code errors out if your primary key is unique and crashes for the second user.
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.
[member=4121]phphelp[/member] experts, This guy has repeatedly shown by his responses that he is unwilling to learn and take advice from people much more knowledgeable. The answer [member=69011]Topcoder[/member] provided, whether using a trigger or code is the right answer and the same one I was going to give without using a trigger until I realized it was pointless to try to help this person.
FYI: There is also another way to do it using alter, delete and orderby to the keep the id’s 1-8 which really doesn’t matter since this just deals with a total of 8 records that really dont depend on the numbers being 1-8 and another way using a temp table. [member=69011]Topcoder[/member]'s response is the cleanest, trigger activated or not.
It is not a matter of not wanting to learn. You are answering a question I did not ask. I did not ask you to redesign my database, I asked you how to do the equivalent of the mysqli_query command with pdo. Why won’t you just answer my question instead of trying to redesign my setup. It is a simple queue stored in a database table. Is this too difficult for you to understand? Why do you think I want to redesign my database? I just want to access it with pdo’s instead of mysqli_query.
I referred you to my signature link to the post with the PDO DB download. I dont know how you can not find the link in the post. The link clearly says “* pdo_bumpstart_ver1.3.zip (214.25 kB - downloaded 93 times.)”.
Later I got the link with the .zip file to download but i didn’t find that file to be of any use to me. I don’t have a problem connecting to databases with pdo’s or passing variables or values to them but I can’t find anything that can do the simple addition of adding 1 to a value. Are PDO’s, prepares, or bindParam incapable of doing that?
Then simply do it, someone already stated that the query string wouldn’t be different. An this link should help you get started in switching over to pdo http://php.net/manual/en/pdostatement.fetch.php (just for starters) and run into problems someone here might help out. ;D Why you want to mess around with the database table is beyond me, specially for 8 measly records. There are a whole bunch of database schemes out there like CRM, but they usually deal with [size=24pt]HUGE[/size] database tables.
[member=76116]jogjer[/member] You can do everything with PDO that you can with mysqli, post your PDO code that you attempted and we’ll fix it for you.
I think I figured out what I was doing wrong:
I’m trying to pass a parameter to something that doesn’t need a parameter.
So the code should be(after establishing a connection):
$stmt = $pdo->prepare(“UPDATE current_messages SET id=id+1 ORDER BY id DESC”);
$stmt->execute();
Problems when you overthink things.