php Array map function

Question

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?

Matias Jimenez · Accepted Answer · 2022-02-26 02:36:30Z

1

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

answered Feb 26, 2022 at 2:36

Matias Jimenez

611 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3783243 · Accepted Answer · 2022-02-26 00:14:06Z

1

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

answered Feb 26, 2022 at 0:14

user3783243

5,2125 gold badges27 silver badges45 bronze badges

1 Comment

Stevooh Over a year ago

Yes, this is it. But it misses AND in the WHERE Condition. @Matias has solved i. Thank you all.

Collectives™ on Stack Overflow

php Array map function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related