Method that calls bind_param multiple times

Question

I'm trying to build a function that takes 2 parameters : a table name and a list of params and build a SELECT query.

Here's an example :

$params['user'] = 'myTestUsername';
$params['pass'] = 'myTestPassword';
echo $dbCon->countBy('users', $params);

This should build (and execute) this query :

SELECT * FROM users WHERE user='myTestUsername' AND pass='myTestPassword'

and then returns the number of selected rows...

I have these 2 methods :

1) the main method (where the SQL magic should happen) :

public function countBy($table, $params) {
    $query = $this->buildQuery($table, $params);

    $mySelect = $this->dbh->prepare($query);
    $params = array_merge(array(count($params)), array_values($params));
    call_user_func_array(array(&$mySelect, 'bind_param'), $params);

    if ($mySelect) {
        $mySelect->execute();
        return $mySelect->rowCount();  
    }
    return 0;
}

2) the method that builds the query with the given parameters :

public function buildQuery($table, $params) {
    $i = 0;
    $where = '';

    if (count($params) > 0) {
        $query = 'SELECT * FROM ' . $table . ' WHERE ';
        foreach(array_keys($params) as $pList) {
            $i++;
            $where .= $pList . '=?' . ($i<count($params) ? ' AND ' : '') ;
        }
        $query .= $where;

        // output is like : SELECT * FROM users WHERE user=? AND pass=?

        return $query;
    }
    return null;
}

The 2nd method works great, but I have some issues at the 1st one. And it's related to the use of the "call_user_func_array" method.

I really don't see what I did wrong... Any ideas?

The "solution" with call_user_func_array was taken from here, but I'm not sure about this part :

$params = array_merge(array(str_repeat('s', count($params))), array_values($params));

In my code I tried using it like this :

$params = array_merge(array(count($params)), array_values($params));

but still nothing...

Hey, call_user_func_array first param should be a callable (callback), not array type. — felipsmartins, Jun 16 '13 at 08:58

Niko · Accepted Answer · 2013-06-16T09:03:20.040

3

With PDO, the binding works a bit different:

$mySelect = $this->dbh->prepare($query);
if ($mySelect) {
    $mySelect->execute(array_values($params));
    return $mySelect->rowCount();
}

edited Jun 16 '13 at 09:03

answered Jun 16 '13 at 08:55

Niko

26,166
8
90
108

I don't get why you use bindParam. WHile you can use the params on execute. `$mySelect->execute($params)` – MKroeders Jun 16 '13 at 08:57
I tried to do this, but it doesn't work... that's why I searched a little bit online and found that the usage of [code]call_user_func_array[/code] may be a solution... – chris_so Jun 16 '13 at 08:57
Yeap... Great point! Thank you guys! This was the issue... I was in a hurry and I didn't pay attention to that mysqli vs PDO issue... – chris_so Jun 16 '13 at 09:03

score 0 · Answer 2 · answered Jun 16 '13 at 08:56

0

bind_param is method of mysqli object, for PDO it is called bindParam and it does not accept multiple parameters at once. You have to iterate through your $params and add them separately.

answered Jun 16 '13 at 08:56

dev-null-dweller

28,932
3
63
85

I tried this, I verified the sent values, but I have no idea why the query it is not executed... Here's what I did: foreach($params as $param) { $selectQuery->bindParam($i++, $param); var_dump($query . '|||' . $i . '|||' . $param); // to check } – chris_so Jun 16 '13 at 08:59

Method that calls bind_param multiple times

2 Answers2