drwu24647 2018-05-24 18:42
浏览 284
已采纳

PHP,PDO和MySQL - 返回的查询顺序不正确

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" } } 
  • 写回答

1条回答 默认 最新

  • dongmao3131 2018-05-24 18:53
    关注

    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.)

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名