CODEWITHSUNDEEP
phpmysqlpdo
Cody_T
Cody_T

Reputation: 81

Set Database Row Order

I am wanting to make a list of database results on my website. The max number of results that will come back is 5, and I want the user to be able to chose the order in which they are displayed. To do this, I have an "order" column in my database table that allows the order to be set at 1, 2, 3, 4, or 5. When the list is retrieved, 1 will show first, etc.

Well, what I am trying to do is make it where if you delete the column that is ordered as "3", then the two columns ordered at 4 and 5 will automatically change to 3 and 4. Then when the user is entering a new result into the database, it will check how many entries are in there and then know if it finds 4, the next one needs to be order as "5". Or if it finds 2 results, the next one needs to be order as "3".

This last part is easy enough and I can do that, what I am unable to do is have the columns adjust to where it does not skip any numbers.

Right now, I'm using the typical way to delete columns... Is there something I could add that could solve what I want to do?

$sql = $this->dbh->prepare("DELETE FROM ". $this->config['db']['table4'] ." WHERE ". $this->config["db"]["columns"]["id"] ."= '$shift'");

Upvotes: 0

Views: 47

Answers (1)

Tony Dong
Tony Dong

Reputation: 3313

use row number instead of column number, so you don't need to manage the numbers

SELECT 
    @i:=@i+1 AS iterator, 
    t.*
FROM 
    tablename AS t,
    (SELECT @i:=0) AS foo

Upvotes: 2

Related Questions