Can't get PDO SQL query to work

Question

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>';

You don't need to bind a parameter for an empty string; and the other one should be `exam.Level=:level`, I think. — andrewsi, May 16 '13 at 17:07
Thanks. I amended to exam.Level = :level. How do I use :centre if it's an empty string? — Benjamin, May 17 '13 at 09:17

score 3 · Answer 1 · edited May 23 '17 at 11:57

3

A simple tutorial for you to get it right.

Take your mysql query that works

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

Make sure it works. If not - make it work first.
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 = ?";
```

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));

Everything works.
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

edited May 23 '17 at 11:57

Community

1
1

answered May 17 '13 at 10:26

Your Common Sense

156,878
40
214
345

Also,if you have `ATTR_EMULATE_PREPARES` set to `true`, you can use [PDOStatement::debugDumpParams](http://www.php.net/manual/en/pdostatement.debugdumpparams.php) **after** the `execute()`. – Jimbo May 17 '13 at 10:49
Thanks. I got it to run, but it only shows the first entry in the database. Surely I don't need to loop to show ALL entries that meet the criteria? – Benjamin May 17 '13 at 13:48
@user2304574 just use proper fetch method. To get all entries you have to use `$stmt1->fetchAll()` instead of fetch() which gets you only one record – Your Common Sense May 17 '13 at 13:50
`fetchAll()` just wipes my data and replaces it with "Array". – Benjamin May 17 '13 at 14:21

score 0 · Answer 2 · answered May 16 '13 at 17:12

0

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 ?

answered May 16 '13 at 17:12

SSaaM

108
10

Thanks. I amended to `exam.Level = :level` but it still doesn't work. And how do I use a parameter for an empty string? e.g. `exam.Centre!=''` – Benjamin May 17 '13 at 09:28

Can't get PDO SQL query to work

2 Answers2