CODEWITHSUNDEEP
phppdoforeachbindvalue
user2812532
user2812532

Reputation: 95

Foreaching PDO bindValue / BindParam

I wanted to make my life a bit easier like in this post:

PDO::bindParam in a foreach loop, all values are being set as the same?

And several others...

I tried several variants to get my code work (to see messages) but no result i have to methods in my class:

public function getMessages($locationId, $name) {
    if(!empty($name)) {

        $query = 'SELECT * FROM (SELECT * FROM sms WHERE location_id=:locationId ORDER BY id DESC LIMIT 5) AS SOURCE ORDER BY id ASC';
        $parameters = array(':locationId' => $locationId);

        $row = $this->returnDataObject($query, $parameters);

        while ($row) {
            $this->fetchData($row, $biggestId, $name);
        }

        $this->updateSessionKey($locationId, $name);
    }
}

And the other:

public function returnDataObject($query, $parameters) {
    var_dump($query); 
    var_dump($parameters);

    $dataObject = $this->dbh->prepare($query);

    foreach ($parameters as $key => &$value) {
        $dataObject->bindParam($key, $value);
    }

    $dataObject->execute();
    $row = $dataObject->fetch(PDO::FETCH_OBJ);

    return $row;

}

I tried:

foreach ($parameters as $key => &$value) {
and:
foreach ($parameters as $key => $value) {

I tried:

$dataObject->bindParam($key, $value);
and:
$dataObject->bindValue($key, $value);

I also tried in getMessages method to die('I am in getMessages method'); But i couldn't get that far...

What should i do differently to get this code work?

I appreciate any help!

EDIT:

My bad with biggestId but declaring biggestId: 

$biggestId = $this->getBiggestId($locationId);

But fixing that didn't fix the whole problem. This first method works perfictly if it is:

public function getMessages($locationId, $name) {
    if(!empty($name)) {
        $biggestId = $this->getBiggestId($locationId);

        $messages = $this->dbh->prepare('SELECT * FROM (SELECT * FROM sms WHERE location_id=:locationId ORDER BY id DESC LIMIT 5) AS SOURCE ORDER BY id ASC');
        $messages->bindParam(':locationId', $locationId);
        $messages->execute();

        while ($row = $messages->fetch(PDO::FETCH_OBJ)) {
            $this->fetchData($row, $biggestId, $name);
        }

        $_SESSION[$name] = $biggestId;
    }
}

But i want to use the method so i don't have to bindParam / BindValue manualy, the functionality of returnDataObject method would make my life easier...

Upvotes: 0

Views: 236

Answers (1)

meda
meda

Reputation: 45490

Make the following Changes:

  • use fetchAll() instead of fetch()
  • Since fetchAll() is used, use a foreach rather than a while loop.

returnDataObject:

public function returnDataObject($query, $parameters) {
    $dataObject = $this->dbh->prepare($query);

    foreach ($parameters as $key => &$value) {
        $dataObject->bindParam($key, $value);
    }

    $dataObject->execute();
    $rows = $dataObject->fetchAll(PDO::FETCH_OBJ);

    return $rows;
}

getMessages:

public function getMessages($locationId, $name) {
    if(!empty($name)) {

        $query = 'SELECT * FROM (SELECT * FROM sms WHERE location_id=:locationId ORDER BY id DESC LIMIT 5) AS SOURCE ORDER BY id ASC';
        $parameters = array(':locationId' => $locationId);

        $rows = $this->returnDataObject($query, $parameters);

        foreach($rows as $row) {
            $this->fetchData($row, $biggestId, $name);
        }

        $this->updateSessionKey($locationId, $name);
    }
}

Upvotes: 2

Related Questions