Solved: PHP PDO query via execute

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

andynic

Thread Starter
Joined
May 25, 2007
Messages
425
Hi,

I'm new to PDO. In the past I've been using mysql_ procedural and mysqli_ oo functions.

Can anyone spot what I might be doing wrong in the following code:
Code:
    $dbHndl     = new PDO("mysql:host=".DB_HOSTNAME."; dbname=".DB_NAME."; charset=utf8", DB_USERNAME, DB_PASSWORD);
    $sqlCmd     = "select id_tit, titel, substr(korte_omschrijving, 1, 10) korte_omschrijving from t_titels where id_tit in (:id0, :id1, :id2, :id3 )";
    $sqlCmdHndl = $dbHndl->prepare($sqlCmd);
    $id = array(34, 35, 36, 37);
for ($i=0; $i<sizeof($id); $i++) { echo "id[$i] = {$id[$i]}]<br />"; }  // CHECK THE ARRAY CONTENTS

    $sqlCmdHndl->bindParam(':id0', $id[0]);
    $sqlCmdHndl->bindParam(':id1', $id[1]);
    $sqlCmdHndl->bindParam(':id2', $id[2]);
    $sqlCmdHndl->bindParam(':id3', $id[3]);
    $sqlCmdHndl->execute();
    //$sth->execute(array(':calories' => $calories, ':colour' => $colour));
    $row = $sqlCmdHndl->fetch(PDO::FETCH_ASSOC);

    while ($row = $sqlCmdHndl->fetch(PDO::FETCH_ASSOC))
    {
      $id    = $row['id_tit'];
      $titel = $row['titel'];
      $ko    = $row['korte_omschrijving'];
      echo "id = $id ... titel = $titel ... korte_omschrijving = $ko <br /><br />";
    }
exit();
The results from the above code are missing the first row. There should be four rows.

The above returns:
id[0] = 34]
id[1] = 35]
id[2] = 36]
id[3] = 37]
id = 35 ... titel = En wie doet Tess? ... korte_omschrijving = Een moedig
id = 36 ... titel = Een eigen plek ... korte_omschrijving = De ontwikk
id = 37 ... titel = Toen ik bang was ... korte_omschrijving = Een moeder

While the SQL command executed from a command window returns:
mysql> select id_tit, titel, substr(korte_omschrijving, 1, 10) korte_omschrijving from t_titels where id_tit in (34, 35, 36, 37)\G
*************************** 1. row ***************************
id_tit: 34
titel: Scheur je Gek
korte_omschrijving:
*************************** 2. row ***************************
id_tit: 35
titel: En wie doet Tess?
korte_omschrijving: Een moedig
*************************** 3. row ***************************
id_tit: 36
titel: Een eigen plek
korte_omschrijving: De ontwikk
*************************** 4. row ***************************
id_tit: 37
titel: Toen ik bang was
korte_omschrijving: Een moeder
4 rows in set (0.00 sec)

Thanks for your help.
Andynic
 

andynic

Thread Starter
Joined
May 25, 2007
Messages
425
I think I may have it solved, but am not sure why.
Code:
    $dbHndl     = new PDO("mysql:host=".DB_HOSTNAME."; dbname=".DB_NAME."; charset=utf8", DB_USERNAME, DB_PASSWORD);
    $sqlCmd     = "select id_tit, titel, substr(korte_omschrijving, 1, 10) korte_omschrijving from t_titels where id_tit in (:id0, :id1, :id2, :id3 )";
    $sqlCmdHndl = $dbHndl->prepare($sqlCmd);
    $id = array(34, 35, 36, 37);

    $sqlCmdHndl->bindParam(':id0', $id[0]);
    $sqlCmdHndl->bindParam(':id1', $id[1]);
    $sqlCmdHndl->bindParam(':id2', $id[2]);
    $sqlCmdHndl->bindParam(':id3', $id[3]);
    $sqlCmdHndl->execute();

    $sqlCmdHndl->setFetchMode(PDO::FETCH_ASSOC);

    while ($row = $sqlCmdHndl->fetch())
    {
      $id    = $row['id_tit'];
      $titel = $row['titel'];
      $ko    = $row['korte_omschrijving'];
      echo "id = $id ... titel = $titel ... korte_omschrijving = $ko <br /><br />";
    }
The kernel difference is this:
Instead of using
$sqlCmdHndl->execute();
$row = $sqlCmdHndl->fetch(PDO::FETCH_ASSOC);
while ($row = $sqlCmdHndl->fetch(PDO::FETCH_ASSOC))

The code that works is:
$sqlCmdHndl->execute();
$sqlCmdHndl->setFetchMode(PDO::FETCH_ASSOC);
while ($row = $sqlCmdHndl->fetch())

Andynic
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top