tux-world tux-world - 4 months ago 11
PHP Question

PHP create MySql command to use multi where clause from some conditions

I have some conditions such as :

productId = 1 and marketId = 1
productId = 2 and marketId = 2
productId = 3 and marketId = 3
productId = 4 and marketId = 4
productId = 5 and marketId = 5


and I want to fetch that which they are in array to create single mysql command, count of this conditions are custom, I want to get result with one sql command like with:

SELECT * FROM images
WHERE productId = 1 AND marketId = 1
OR productId = 2 and marketId = 2
OR productId = 3 and marketId = 3


how can I do that? My code is not correct and that sum all values:

<?php
$arr = [
[
'productId' => '1',
'marketId' => '1',
],
[
'productId' => '2',
'marketId' => '2',
],
[
'productId' => '3',
'marketId' => '3',
],
];

$command = "SELECT * FROM productImages ";
for ($i = 0; $i < count($arr); $i++) {
$command .= "WHERE productId = '" . $arr[$i]['productId'] . "'" . " AND marketId = '" . $arr[$i]['marketId'] . "'";
}

print_r($command);

?>


result is 12 not sql command

Answer
 <?php
    $arr = [
        [
            'productId' => '1',
            'marketId' => '1',
        ],
        [
            'productId' => '2',
            'marketId' => '2',
        ],
        [
            'productId' => '3',
            'marketId' => '3',
        ],
    ];

    $command = "SELECT * FROM productImages WHERE ";

    for ($i = 0; $i < count($arr); $i++) {
        $command .= "(productId = '".$arr[$i]['productId']."' AND marketId = '".$arr[$i]['marketId']."')";
        if ($i < count($arr) - 1) {
            $command .= ' OR ';
        } else {
            $command .= ';';
        }
    }

    print_r($command);

?>

Result:

SELECT * FROM productImages WHERE (productId = '1' AND marketId = '1') OR (productId = '2' AND marketId = '2') OR (productId = '3' AND marketId = '3');
Comments