CODEWITHSUNDEEP
phpmysqlpdosql-order-by
whispers
whispers

Reputation: 962

PHP, PDO & MySQL - returned query order not correct

Example table layout: http://rextester.com/QSDQA17146

I'm not 100% sure.. but I believe the error is stemming from the query side of things....

Background: I have a query..that I pass an array to, to be used in retrieving data based on that value/field in the array from the table.

I was initially using an IN() clause.. but because my array can/does have duplicates (ie: 1, 2, 3, 4, 2, 3, 6, 7).. I need a row returned for each value in the IN() clause (even though there is only 1 row in the table)..

After getting some help here..I was told to update this to a UNION ALL query.. so I could actually get a row returned for each item in my array (regardless if it was a duplicate or not)

SELECT * FROM tablex
WHERE CONCAT(brandname, ' ', dosage) IN (
    'Zyflo CR Extended-release tablet 600 mg',
    ' SEE NOTES BELOW',
    'Zyflo CR Extended-release tablet 600 mg'
) ORDER BY FIELD(
    CONCAT(brandname, ' ', dosage),
    'Zyflo CR Extended-release tablet 600 mg',
    ' SEE NOTES BELOW',
    'Zyflo CR Extended-release tablet 600 mg'
);

So I updated things best I could to use PDO and UNION ALL calls.

SELECT t.* FROM (
  SELECT 1 as seq,
  'Zyflo CR Extended-release tablet 600 mg' as item
  UNION ALL
  SELECT 2, ' SEE NOTES BELOW '
  UNION ALL
  SELECT 3, 'Zyflo CR Extended-release tablet 600 mg'
) AS inList
INNER JOIN pbaap_drugs AS t ON inList.item = CONCAT(t.brandname, ' ', t.dosage);

I now do get a row returned for each item.. however the returned data is NOT adhering to the original order of the array passed into the query.

I'm calling function/query like so:

$gn_displayList = get_displayList_alt($tablename, $conn, array_values(array_filter($_POST['gn_MedicineSelect'])));//array_filter() added to get count of only non empty indexes (no bloat)

I tried to output some DEBUG code and follow where/where the array order is changing.. and it seems to be in the query return portion?

Here is the array (parsed) when it is passed into the function above:

FILTERED ARRAY CONTENTS: array(3) { [0]=> string(39) "Zyflo CR Extended-release tablet 600 mg" [1]=> string(17) " SEE NOTES BELOW " [2]=> string(39) "Zyflo CR Extended-release tablet 600 mg" }

In the actual query function where I dynamically build the UNION ALL structure..etc.. I check the array again (I can post this... but I dont want to deter from the focus on the order of things) ;)

array(3) { [0]=> string(39) "Zyflo CR Extended-release tablet 600 mg" [1]=> string(17) " SEE NOTES BELOW " [2]=> string(39) "Zyflo CR Extended-release tablet 600 mg" }

All seems well.. correct content.. and correct original order.

However.. when I get my query response/data.. the order is NOT the same. It seems to have alpha sorted it?

array(3) { [0]=> array(10) { ["id"]=> string(2) "74" ["lastupdated"]=> string(19) "0000-00-00 00:00:00" ["category"]=> string(24) "5-Lipoxygenase Inhibitor" ["brandname"]=> string(32) "Zyflo CR Extended-release tablet" ["genericname"]=> string(8) "zileuton" ["dosage"]=> string(6) "600 mg" ["image"]=> string(43) "Zyflo CR Extended-release tablet 600 mg.jpg" ["zone"]=> string(16) "green,yellow,red" ["menu"]=> string(1) "1" ["active"]=> string(1) "1" } [1]=> array(10) { ["id"]=> string(2) "74" ["lastupdated"]=> string(19) "0000-00-00 00:00:00" ["category"]=> string(24) "5-Lipoxygenase Inhibitor" ["brandname"]=> string(32) "Zyflo CR Extended-release tablet" ["genericname"]=> string(8) "zileuton" ["dosage"]=> string(6) "600 mg" ["image"]=> string(43) "Zyflo CR Extended-release tablet 600 mg.jpg" ["zone"]=> string(16) "green,yellow,red" ["menu"]=> string(1) "1" ["active"]=> string(1) "1" } [2]=> array(10) { ["id"]=> string(2) "78" ["lastupdated"]=> string(19) "0000-00-00 00:00:00" ["category"]=> string(0) "" ["brandname"]=> string(16) " SEE NOTES BELOW" ["genericname"]=> string(0) "" ["dosage"]=> string(0) "" ["image"]=> string(0) "" ["zone"]=> string(16) "green,yellow,red" ["menu"]=> string(1) "1" ["active"]=> string(1) "1" } }

Upvotes: 0

Views: 51

Answers (1)

spencer7593
spencer7593

Reputation: 108400

Without an ORDER BY clause on a SELECT, MySQL is free to return rows in any sequence it chooses. This behavior conforms with the standard.

If we need rows returned in a particular order, we provide an ORDER BY on the outermost SELECT.

For example, we can list the seq column as the first expression in the ORDER BY so that rows will be returned in seq order. We can add additional expressions to the ORDER BY to make the order of the rows more deterministic (i.e. when there are multiple rows from t that "match" a row from inList)

SELECT t.id
     , t.lastupdated
     , t.category
     , t.brandname
     , t.genericname
     , t.dosage
     , t.image
     , t.zone
     , t.menu
     , t.active
  FROM ( SELECT 1 AS seq, 'Zyflo CR Extended-release tablet 600 mg' AS item 
          UNION ALL
         SELECT 2, ' SEE NOTES BELOW '
          UNION ALL
         SELECT 3, 'Zyflo CR Extended-release tablet 600 mg' 
       ) inList
  JOIN pbaap_drugs t
    ON CONCAT(t.brandname, ' ', t.dosage) = inList.item
 ORDER
    BY inList.seq
     , t.brandname
     , t.dosage
     , ...

(Note that rows from t that match item on rows with seq=1 and seq=3 are not going to match rows with seq=2. The specification isn't clear about what the expected return from the query, aside from the order of the rows.)

Upvotes: 1

Related Questions