PHP bind in for loop

Question

I have rewritten the PDO fuction following advise from my previous question at PHP function/procedure to bind question marks dynamically

The problem I have is the result set returned is empty. The SQL query is correct int he sense that, when I run it manually, it does return data.

My suspicion is that the binding in the for loop is incorrect.

Could I please request guidance on

1) How to bind data in a for loop with question marks? 2) How to bind LIKE cases if the way I'm doing now is incorrect.

sample_sql_1="select f_name, age, address from table1 where l_name=? and dob >= ? and cty =?"

sample_sql_2="select * from table2 where cty LIKE ?"

$locn= "'" . $location . "%'";

pdo_db_query($sql_run,array(':empname'), array($locn));

function pdo_db_query($query, $bindnames = array(), $bindvals = array()) {
    try {
    # MySQL with PDO_MYSQL
    $DBH = new DbConn();

    $DBH->query($query);

    foreach ($bindnames as $key => &$bindname) {
        $DBH->bind( $bindname,$bindvals[$key]);  // bind the value to the statement
    }

    $result=$DBH->resultset();

    if($result){
        var_dump($result);
    }

    # Close the connection
    $DBH->CloseConnection();
    } catch (PDOException $e) {
    echo $e->getMessage();
    var_dump($e->getMessage());
    }
}

Here's the resultset function

public function resultset() {
$this->execute();
return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
}

score 0 · Answer 1 · answered Dec 18 '15 at 00:10

I got to the end of this post before I realized $DBH was actually an instance of a custom class. I'm leaving everything here for now in case I'm wrong and any of it helps. BUT

I think you only problem is in the for loop foreach ($bindnames as $key => &$bindname) should be foreach ($bindnames as $key => $bindname).

Original Answer

Have you tried a simpler example. It looks like you are doing many things incorrectly.

$DBH->query($query);

If you want to bind params to this later you should be preparing: $sth = $DBH->prepare($query);

foreach ($bindnames as $key => &$bindname) {

Are you sure that's doing what you think. I see no reason to us &$bindname it should be $bindname.

$DBH->bind( $bindname,$bindvals[$key]);

The function is actually PDOStatement::bindParam(), so you should be calling $sth->bindParam($bindname,$bindvals[$key]);. where $sth is the return value from $DBH->prepare($query);

$result=$DBH->resultset();

I need to understand the context of the resultset method better. Are you extending PDO or something?

Hello Dan - Thank you very much for the pointers. I think I caused confusion by not including the database handling class here. I apologize for this. The code works as is. The only thing I had to do was remove the single quotes for the LIKE statement. I'll post it tin the answer. — usert4jju7, Dec 18 '15 at 00:51

score 0 · Accepted Answer · answered Dec 18 '15 at 00:52

0

Had to change

$locn= "'" . $location . "%'";

TO

$locn= $location . "%";

I realized that with binding, there is no need for single quote.

answered Dec 18 '15 at 00:52

usert4jju7

1,653
3
27
59

PHP bind in for loop

2 Answers2