I have a Mysql table with records like this: mytable

 ------ ---- ----- 
| id   | val1 | val2|
 ------ ---- ---- 
|    1 |  4 | 1 |
|    2 |  5 | 2 |
|    3 |  1 | 6 |
|    4 |  2 | 4 |
|    5 |  2 | 8 |
|    6 |  3 | 6 |
|    7 |  4 | 9 |
|    8 |  4 | 7 |
|    9 |  3 | 5 |

I want to use below code to find the rows with combination of values. I'm stuck on Mysql recursive query using array of values.

<?php
$db = new mysqli('localhost','root','password','database');
function query(){
    global $db;
    $idx = [[1,10],[2,4],[3,5]];
    $x = array_column($idx,0);
    $y = array_column($idx,1);

    <!--  Need looping code here to run recursive queries on $x and $y values -->

    $sql = $db->query("select id,b1,b2 from dlt where b1=$x and b2=$y order by id desc limit 1");   
}

CodePudding user response：

Your use query in array for example b1 IN (1,2)

$x = array(1,10);
$y = array(2,4);
$sql = $db->query("select id,b1,b2 from dlt where b1 IN (".$x.") and b2 IN (".$y.") order by id desc limit 1");

CodePudding user response：

I think the following will answer your question. You'll need to use the IN operator in the SQL query.

<?php
$idx = [[1,10],[2,4],[3,5]];
$x = array_column($idx,0);
$y = array_column($idx,1);

$string_y = '';
$string_x = '';

if(count($x) == count($y)){
    $i = 0;
    foreach($x as $key => $value){
        if ($key === array_key_last($x)) {
            $string_y .= $y[$key];
            $string_x .= $x[$key];
        }else{
            $string_y .= $y[$key] . ',';
            $string_x .= $x[$key] . ',';
        }
    }
    $sql = $db->query("SELECT id, b1, b2 FROM dlt WHERE b1 IN ($string_x) AND b2 IN ($string_y) ORDER BY id DESC LIMIT 1");   
}

I think you should remove the LIMIT 1 from the query.

You can check a demo here : https://3v4l.org/OAsnv#v7.4.28