CODEWITHSUNDEEP
phpsqlpdo
Benjamin
Benjamin

Reputation: 196

Can't get PDO SQL query to work

I can't get this query to work. It only shows the first DB entry. Any ideas greatly appreciated.

/* prepare */

$sql = "SELECT personal.firstName, personal.lastName, etc. 
        FROM personal 
        LEFT JOIN exam 
        ON personal.P_ID=exam.P_ID 
        LEFT JOIN contact 
        ON exam.P_ID=contact.P_ID 
        WHERE exam.Level = ? 
        AND exam.Centre!='' ORDER BY exam.Centre";

$stmt = $db->prepare($sql);

/* Execute */ $stmt->execute(array($level));

/* Fetch */
            $row = $stmt->fetch(PDO::FETCH_ASSOC);    





/*  Display  */
                    echo '<table>
                         <tr>
                    <td>Name</td>
                    <td>Surname</td>
                    <td>Paid?</td>
                    <td>Etc</td>
                    <td>Etc</td>
                    <td>Etc</td>
                        </tr>';

if ($row) { foreach ($row as $key => $value) 

              {
                echo '<td>';
                echo $value;
                echo '</td>';
            }
               echo '</tr>';
          }
       echo '</table>';

Upvotes: 0

Views: 220

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 157862

A simple tutorial for you to get it right.

  1. Take your mysql query that works

     $sql = "SELECT * FROM t WHERE a = $a AND b = '$b'";
  2. Make sure it works. If not - make it work first.

  3. Take out every variable you interpolate in it (along with all corresponding formatting, including slashes and quotes), and put a question mark in place of them.

     $sql = "SELECT * FROM t WHERE a = ? AND b = ?";

  4. Move these variables into execute() in the form of array

     $sql = "SELECT * FROM t WHERE a = ? AND b = ?";
 $stm = $db->prepare($sql);
 $stm->execute(array($a,$b));

  5. Everything works.

  6. If still not - configure your PDO and PHP in order to see errors if any, read the error message and take appropriate action.

To get your data in the proper format you have to use proper fetch method

     $data = $stm->fetchAll();
     foreach ($data as $row) {
       echo $row['id'];
       // do whatever
     }

Please refer to tag wiki for other methods

Upvotes: 3

SSaaM
SSaaM

Reputation: 108

I'm new at PDO as well, but from your code I noticed that there is no :centre in the query there, so that parameter won't bind?

Another thing is in WHERE exam.Level:level, are you sure you meant this and not: WHERE exam.Level = :level ?

Upvotes: 0

Related Questions