CODEWITHSUNDEEP
phpmysqlsql
Vivek Makwana
Vivek Makwana

Reputation: 85

Convert NoSQL like syntax to MySQL syntax

This is mysql table structure

item_id StockNo SizeCd
1 12003 UNIT
2 12007 JOGO
3 12008 PACOTE
4 12033 JOGO
5 12034 JOGO
6 12038 UNIT

I'm using plugin called DevExtreme for remote data grid. It's API request looks like below.

{
  from: get_data
  skip: 0
  take: 50
  requireTotalCount: true
  filter: [["SizeCd","=","UNIT"],"or",["SizeCd","=","JOGO"]]
}

Where filter is what I need help with. What I want is I want to convert this string into MySQL where condition syntax.

I tried using php functions like array_merge join but couldn't get it to work. Sometimes it place quotes to both sides or sometimes quotes were missing. It should be like field name without quotes and value with quotes. Like in mysql where syntax.

Sorry for bad formatting and grammar mistakes.

Upvotes: 0

Views: 109

Answers (1)

Solonl
Solonl

Reputation: 2512

For this array:

$array = [["SizeCd","=","UNIT"],"or",["SizeCd","=","JOGO"],"or",["SizeCd","=","PACOTE"]];

For PDO

You can use the following functions:

function arrayToQuery(string $tableName, array $array) : string 
{
    $select = "SELECT * FROM `{$tableName}` WHERE ";

    foreach($array as $item) {
        if(is_array($item)) {
            $select .= "`{$item[0]}` {$item[1]} ?";
        } else {
            $select .= " {$item} ";
        }
    }

    return $select;
}

function arrayToParams(array $array) : array  
{
    $return = [];
    foreach($array as $item) {
        if(is_array($item)) {
            $return[] = $item[2];
        }
    }
    return $return;
}

var_dump(
    arrayToQuery("x", $array),
    arrayToParams($array)
);

Output:

string(66) "SELECT * FROM `x` WHERE `SizeCd` = ? or `SizeCd` = ? or `SizeCd` = ?"
array(3) {
  [0]=>
  string(4) "UNIT"
  [1]=>
  string(4) "JOGO"
  [2]=>
  string(6) "PACOTE"
}

Example using PDO

$conn = /* your conn object */;
$sql = arrayToQuery("your_table_name", $array);
$stmt = $conn->prepare($sql);
$stmt->execute(arrayToParams($array));

Update: For Mysqli

For mysqli you can use the following function:

function arrayToQueryMysqli($mysqli, string $table, array $array) : string 
{
    $select = "SELECT * FROM `{$table}` WHERE ";
    foreach($array as $item) {
        if(is_array($item)) {
            $select .= "`{$item[0]}` {$item[1]} '" . $mysqli->real_escape_string($item[2]) . "'";
        } else {
            $select .= " {$item} ";
        }
    }
    return $select;
}

$mysqli = new mysqli(/* Your settings */);
$query = arrayToQueryMysqli($mysqli, "tablename", $array);
$result = $mysqli->query($query);
var_dump($result);

Upvotes: 2

Related Questions