MySQL/PHP/PDO +如何在IN()类别中为每个(重复的)条目获取一行? [英] MySQL/PHP/PDO + How to get a row for each (duplicate) entry in IN() clasue?

问题描述

语言:PHP

数据库:MySQL

我的理解是，如果您在IN()子句中有重复的条目，那么它们将被跳过...我需要为IN()子句中的每个项目返回/行.不是.

My understanding is that if you have duplicate entries in the IN() clause.. they will be skipped... I need a return/row for each item in the IN() clause.. regardless if they are dups or not.

从阅读中...我相信[self] JOIN()是我正在寻找的方法/解决方案..但我不清楚如何在同一张桌子上进行此JOIN? ..尤其是查询，我已经在下面了. (这可以满足我的需要..期望不返回IN()子句中重复条目的行)

From reading... I believe a [self] JOIN() is the approach/solution I am looking for.. but I am not clear on how to do this JOIN on the same table? .. especially with the query I already have going below. (which works how I need it to.. expect for not returning a row for the duplicate entries in the IN() clause)

但是..我也已经阅读了EXISTS()的信息..也许是更好的方法? (我不是在这里寻找速度.我正在寻找一个可行的解决方案)

However.. I have also read about EXISTS() as well.. perhaps a better approach? (I'm not looking for speed here.. I'm looking for a working solution)

我有一个rextexter示例，用于显示同一张表..(但是此查询只能在rextester上运行，但不能在现实世界或WAMP..etc中使用)

I have a rextexter example for displaying a same table.. (however this query DOES work on rextester but not int he real world or WAMP..etc)

http://rextester.com/SZRIAN96972

所以我不清楚它是否实际上是查询本身? (我读这将是问题，因为将在IN()子句中跳过重复项)...因为它在WAMP和REXTESTER中起作用" ...

So I'm not clear if its actually the query itself? (which I read WOULD be the issue, as dups would be skipped in the IN() clause)... because it 'works' in WAMP and REXTESTER...

那么也许它是PDO操作的一部分?将数组传递给execute()函数时，也许发生了什么事?

So perhaps its part of the PDO operations? Maybe something is happening when passing in the array to the execute() function?

PDO片段:

$qMarks = str_repeat('?,', count($brandlist) - 1) . '?'; //create '?' mark placeholders for query, remove last comma and replace with '?'
//preserve IN() order
$displayList_sql = "SELECT * FROM $tablename WHERE CONCAT(brandname, ' ', dosage) IN ($qMarks) ORDER BY FIELD(CONCAT(brandname, ' ', dosage),'". trim(implode("','", $brandlist))."')";             
$displayList_stmt = $conn->prepare($displayList_sql);
$displayList_stmt->execute($brandlist);//make note of passing in array as param to execute() call

传入$ brandList数组的示例:(带有重复项)

Example of the $brandList array being passed in: (with dups)

Zyflo CR Extended-release tablet 600 mg','Zyflo CR Extended-release tablet 600 mg',' SEE NOTES BELOW'

所以在这一点上..我不清楚是否是IN()子句吗?还是在将数组传递给execute()命令时正在做某些事情? 是否正在剥离/跳过IN()子句中的重复项?我该如何避免呢?我希望这是某种自我连接..但是我看到的所有示例都具有硬编码的值(不能是)，然后位于初始IN()子句中的重复条目在第二个查询中使用. (我很关注，因为我看不到这如何适用于我的情况)

So at this point.. I'm not clear if the IN() clause is the issue? Or if something is being done while passing in the array to the execute() command? Something is stripping/skipping the duplicate(s) in the IN() clause? How can I avoid that? I was hoping it was some sort of self join.. but all examples I saw had hardcoded values (which can not be)..and then the duplicate entries located in the initial IN() clause were used in a second query or something. (I was quite following as I couldnt see how this applied to my situation)

这是我在本地WAMP安装上运行的工作示例的快速转储:(需要您自己的连接包含文件)

here is a quick dump of a working example, that I have been running on my local WAMP install: (need you own connection include file)

require_once('../db_wamp_pdo.php');
$tablename = 'xxx';

//hard coded list for testing
$brandList_og = array('Zyflo CR Extended-release tablet 600 mg','Zyflo CR Extended-release tablet 600 mg',' SEE NOTES BELOW', 'Alvesco HFA 80mcg');

function get_displayList($tablename, $conn, $brandlist){    
    $rowcount = 0;
    if($brandlist != '' && count($brandlist) > 0){
        $qMarks = str_repeat('?,', count($brandlist) - 1) . '?'; //create '?' mark placeholders for query, remove last comma and replace with '?'       

        //$displayList_sql = "SELECT * FROM $tablename WHERE CONCAT(brandname, ' ', dosage) IN('Zyflo CR Extended-release tablet 600 mg','Zyflo CR Extended-release tablet 600 mg',' SEE NOTES BELOW', 'Alvesco HFA 80mcg') ORDER BY FIELD(CONCAT(brandname, ' ', dosage),'Zyflo CR Extended-release tablet 600 mg','Zyflo CR Extended-release tablet 600 mg',' SEE NOTES BELOW', 'Alvesco HFA 80mcg')";    

        $displayList_sql = "SELECT * FROM $tablename WHERE CONCAT(brandname, ' ', dosage) IN($qMarks) ORDER BY FIELD(CONCAT(brandname, ' ', dosage),'". trim(implode("','", $brandlist))."')";  

        //$displayList_sql = "SELECT * FROM $tablename WHERE CONCAT(brandname, ' ', dosage) FIND_IN_SET($qMarks) ORDER BY FIELD(CONCAT(brandname, ' ', dosage),'". trim(implode("','", $brandlist))."')";   
        //$displayList_sql = "SELECT * FROM $tablename WHERE EXISTS(SELECT CONCAT(brandname, ' ', dosage)) ORDER BY FIELD(CONCAT(brandname, ' ', dosage),'". trim(implode("','", $brandlist))."')";             

        $displayList_stmt = $conn->prepare($displayList_sql);
        $displayList_stmt->execute($brandlist);//make note of passing in array as param to execute() call
        $displayList_stmt->setFetchMode(PDO::FETCH_ASSOC);
        $_displayList = $displayList_stmt->fetchAll(); //returns multi-dimensional array (and correct count)
        $colcount = $displayList_stmt->columnCount();
        $rowcount = $displayList_stmt->rowCount(); 
    }   

    if($rowcount <= 0){
        //nothing returned
    }else{  
        return $_displayList;
    }       
}
$my_displayList = get_displayList($tablename, $conn, array_values(array_filter($brandList_og)));//array_filter() added to get count of only non empty indexes

echo 'BRAND LIST: <br>';
echo var_dump($my_displayList);
echo '<br>';

我一直在输出调试文本..并且'array'可以正常使用，直到它从查询中返回为止.

I have been outputting debug text along the way.. and the 'array' is fine until it comes back from the query..

那么它是IN()子句(我当前的理论)，还是我需要以某种方式向此动态查询添加自我联接的帮助?还是(令人难以置信的)将数组传递给execute()行时..事物被解析/删除了?

So its either the IN() clause (my current theory) and I need help somehow adding a self join to this dynamic query? Or (less believable) when passing in the array to the execute() line.. things are getting parsed out/removed?

推荐答案

在您使用单词这个意义上，并没有真正跳过in()子句中的重复项.确实会跳过重复的字段，但这无关紧要，因为IN()语句的计算结果为OR语句.

Duplications in the in() clause aren't really skipped in the sense that you're using the word. The duplicated field is indeed skipped, but that doesn't matter because IN() statements evaluate to an OR statement.

例如:

select * from table
where table.thing in ('a', 'b')

评估为

select * from table
where 
   table.thing    = 'a'
   or table.thing = 'b'

如果我们向该IN()语句添加另一个'a'，则它会被忽略"，因为原始的'a'值已经与第一个匹配.让优化器再次将其写出毫无意义.

If we add another 'a' to that IN() statement, it gets 'ignored' because the original 'a' value already matched the first one. Having the optimizer write it out again would be pointless.

这篇关于MySQL/PHP/PDO +如何在IN()类别中为每个(重复的)条目获取一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！