Sort and replace positions in bd mysql

Question

Sort and replace positions in bd mysql

Navigation

#1 by (1 votes)

1

I have a table with these values

Juan    5
Pedro   7
Luis    6

I want to replace the consecutive numbers that are listed from least to largest starting from number 1 ie a php script that detects that the table does not have the number 1 then assign to joan that is the lowest value 5 the number 1 ,
I ask again, there is number 2, then I assign the next child that would be Luis to change the 6 for a 2
I ask again, there is number 3 if it is not then I assign the number to Pedro change the number 7 to the 3

The final result would be as follows

Juan    1
Pedro   3
Luis    2

The code is as follows:

foreach($idEventsToChangePosition as $ids) {
    $position = $wpdb->get_var($wpdb->prepare("SELECT position FROM $db_table_name WHERE idSiteEvent = %d AND idBlock = %d", $ids, $idBlock));

    $wpdb->update(
        $db_table_name,
        array('position' => $position - 1),
        array('idBlock' => $idBlock, 'idSiteEvent' => $ids)
    );
}

It only works if the tables have position from 1 but if one does not have the position 1 it is not possible

The table contains two fields:

idSiteEvent     position
Juan            5
Pedro           7
Luis            6

Where should I assign to these idSiteEvent numbers from least to greatest but starting at 1, that is, the script for mysql in php must detect who is the smallest number and change it by 1 then the next one and so on until achieving

idSiteEvent   position
Juan          1
Luis          2
Pedro         3

mysql comparaciones

asked by Ivan Diaz Perez 05.04.2017 в 13:29

source

1 answer

save meta width in variable session Javascript Avoid registering duplicates in database with Codeigniter

score 1 · Accepted Answer

An SQL query to sort and modify the indices simultaneously looks like the following:

UPDATE test AS t2 
SET t2.id = (SELECT COUNT(*) 
             FROM (SELECT * FROM test) AS t1 
             WHERE t1.id < t2.id) + 1;

The structure is like the following:

id  name
8   123
34  Oe23M
1   O5555AM
4   OpenIDM
6   OpenAM
9   OpenDJ

After executing the query:

id  name
4   123
6   Oe23M
1   O5555AM
2   OpenIDM
3   OpenAM
5   OpenDJ

You can see a test in SQLFiddle

What it does is modify id of the table counting all those that have lower index. This means that if the lowest index is 8, count returns 0, plus the unit that is added to the end (+1), remains as the first element and so on. I do not recommend using this query in real time, because it could be too slow, but it serves to do what you want.

PS: DO NOT USE THIS CONSULTATION BEFORE BACKING YOUR DATA BASE, SINCE IT IS YOUR RESPONSIBILITY