1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: PHP PDO query via execute

Discussion in 'Web Design & Development' started by andynic, Feb 24, 2015.

Thread Status:
Not open for further replies.
  1. andynic

    andynic Thread Starter

    Joined:
    May 25, 2007
    Messages:
    386
    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
     
  2. andynic

    andynic Thread Starter

    Joined:
    May 25, 2007
    Messages:
    386
    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
     
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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1143707

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice