Does phpmysqlselect support explode fields and sort them by values?

1. for example, the format of a field in the table is as follows: Data 1 ||||| Data 2 |||| data 32. now we want to split the data into $ data [0], $ data [1], $ data [3], and then orderby $ data [3] 3. can it be implemented? 4. the description may be non-standard, but the meaning should be clear. 1. for example, the format of a field in the table is as follows: Data 1 | Data 2 | data 3
2. now we want to split the data into $ data [0], $ data [1], $ data [3], and then order by $ data [3]
3. can it be implemented?
4. the description may be non-standard, but the meaning should be clear.

Reply content:

1. for example, the format of a field in the table is as follows: Data 1 | Data 2 | data 3
2. now we want to split the data into $ data [0], $ data [1], $ data [3], and then order by $ data [3]
3. can it be implemented?
4. the description may be non-standard, but the meaning should be clear.

Yes. if it is implemented at the MySQL level, the database may be under great pressure.
You can arrange the order in the PHP program, and then retrieve data from MySQL based on the ID sequence.

  Query ('select id, field FROM table')-> fetch_all (MYSQLI_ASSOC ); // assume that the obtained data structure is as follows $ arr = array ('id' => 1, 'field' => 'data 1 |||| Data 2 ||||| 256 '), array ('id' => 2, 'field' => 'data 1 |||| Data 2 ||||| 128 '), array ('id' => 3, 'field' => 'data 1 |||| Data 2 ||||| 512 '),); foreach ($ arr as $ k =>$ v) {// convert the field content into an array, for example, array ('data 1', 'data 2', '123 '), facilitate subsequent sorting $ arr [$ k] ['field'] = explode ('|', $ v ['field']);} // based on the third element in the field Sort uasort ($ arr, function ($ a, $ B) {if ($ a ['field'] [2] ===$ B ['field'] [2]) return 0; // The following sentence means, if a is greater than B, 1 is returned, and 1 is returned, indicating the order of exchange. that is, a small number is arranged in ascending order. else return ($ a ['field'] [2]> $ B ['field'] [2])? 1:-1 ;}); foreach ($ arr as $ k =>v v) {$ ids [] = $ v ['id'];} $ ids = implode (',', $ ids); // The ID sequence is 2, 1, 3 // query MySQL by id sequence // the SQL statement is SELECT * FROM table WHERE id IN (, 3) ORDER BY FIELD (id, 3) $ SQL = "SELECT * FROM table WHERE id IN ($ ids) order by field (id, $ ids)"; // $ ret = $ db-> query ($ SQL) -> fetch_all ();

If the data volume is large, you can cache the ID sequence prepared by PHP.
Order by field (id, $ ids) also causes performance problems when the data volume is large,
In this case, you can split $ ids according to the actual situation. for example, 20 ids are sorted from 0 to 19 on the first page, and 20 ids are sorted on the second page, in this way, the pressure on MySQL is much lower for custom sorting of small data volumes.

This has nothing to do with php. You can use mysql to create a function and then sort it by function:

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)RETURNS VARCHAR(255)RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),       LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),       delim, '');

SELECT * FROM test_table order by SPLIT_STRING(test_tablecol, '|||', 3);

Can be implemented, it is very troublesome, and the efficiency is very low, the common solution for this requirement is to modify the table structure.

In terms of logic alone, SQL can actually be very complicated, but do you think if a query needs to run for several minutes, it will be known by the family?

We recommend that you use php to sort data after Query. do not use mysql to sort data.

Substring_index of mysql