I am working on PHP Script and experienced a challenge as a below. My main Aim is to create a Select Query from my db, with different query operators. So far, I have this

public function select(array $columns, array $where)
    {
        $tableName = static::tableName();

        for ($i = 0; $i < count($where); $i  ){
            $attributes[] = $where[$i][0];
            $operators[] = $where[$i][1];
        }

        $operator = implode(" ", array_map(fn($oper) => "$oper", $operators));
        $sql = implode(" AND ", array_map(fn($attr) => "$attr $operator :$attr", $attributes));
        $columns = implode(", ", array_map(fn($att) => "$att", $columns));
        $stmt = self::prepare("SELECT $columns FROM $tableName WHERE $sql ");

        .......
    } 

$where contains an array of the Conditions with the column, operator and the value eg [["id", ">", 3], ["firstname", "=", "John"]].

The sql query should be like

SELECT username, email 
FROM table_name WHERE id >  :id AND firstname  = :firstname

However, what I get is

SELECT username, email 
FROM table_name WHERE id >= :id AND firstname >= :firstname

which is incorrect. How can I match the operators correctly?

CodePudding user response：

Using the logic of user3783243's Answer, in addition to that of the question itself. Just change the dynamic creation of the conditions to be able to use them in the array_map.

function select (array $columns, array $where) {
  for ($i = 0; $i < count($where); $i  ){
    $conditions[] = $where[$i][0] . ' ' . $where[$i][1] . ' :' . $where[$i][0];
    $params[] = $where[$i][2];
  }
  $sql = implode(" AND ", array_map(fn($cond) => "$cond", $conditions));
  $columns = implode(", ",array_map(fn($att) => "$att",$columns));
  echo "SELECT $columns FROM ".tableName()." WHERE $sql";
}

Also pass $params to the execute method.

The full example here

CodePudding user response：

I would not use array_map. I would use a foreach and just build the query dynamically:

$where = [["id", ">", 3], ["firstname", "=", "John"]];
$wherequery = ' where ';
foreach($where as $w){
    $wherequery .= $w[0] . ' ' . $w[1] . ' ? ';
    $params[] = $w[2];
}
echo "SELECT columns FROM table " . $wherequery;

then just pass $params to the execute method.

https://3v4l.org/k8F2Y

alternatively with named placeholders:

$where = [["id", ">", 3], ["firstname", "=", "John"]];
$wherequery = ' where';
foreach($where as $w){
    $wherequery .= ' ' . $w[0] . ' ' . $w[1] . ' :' . $w[0];
    $params[$w[0]] = $w[2];
}
echo "SELECT columns FROM table " . $wherequery;

https://3v4l.org/5WXK5