Populating a drop down box using PDO Mysql

[ebutt13]

I'm trying to use this code to populate my drop down menu from a mysql table:

<select name="fileselect">
<?php
$pdo = new PDO('mysql:host=localhost;dbname=contisec_portal', 'root', '');
$stmt = $pdo->prepare("SELECT dateid FROM date_header");
$stmt = $pdo->execute();


while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<option value='" . $row['dateid'] . "'>" . $row['dateid'] . "</option>";
}
?>
</select>

Does anybody know what could be wrong?

[veedeoo]

Hi,

You can easily free yourself from this problem by trying to change your codes to this.. WARNING! Not tested though.. I am just looking at your codes and possible corrections to make it work..

    <select name="fileselect">
<?php
$pdo = new PDO('mysql:host=localhost;dbname=contisec_portal', 'root', '');
$stmt = $pdo->query("SELECT dateid FROM date_header");
//$stmt = $pdo->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<option value='" . $row['dateid'] . "'>" . $row['dateid'] . "</option>";
}
?>
</select>

If you really need to use prepared statement, please let me know so that I can test it. For now, the above codes should work for your purpose..

[diafol]

I'd just add that you'd probably do better to separate your php and html as much as possible. Mixing them up can make pages hard to maintain, even if it looks more logical to do so.

<?php
$ops='';
$pdo = new PDO('mysql:host=localhost;dbname=contisec_portal', 'root', '');
$stmt = $pdo->query("SELECT dateid FROM date_header");
//$stmt = $pdo->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $ops.= "<option value='" . $row['dateid'] . "'>" . $row['dateid'] . "</option>";
}
?>

<!--later on-->
<select name="fileselect">
<?php echo $ops;?>
</select>

This means that you can place your code into a function, a class, an include file, whatever. No big deal though.

[simplypixie]

There are a couple of errors in your code ( to execute you need to prepare first and your execute should be $stmt->execute(), not $stmt = $pdo->execute())

I actually find using foreach with PDO tends to work better for some reason. I would also agree with diafol that you should separate your PHP and HTML but I would go even further than he has

<?php
$pdo = new PDO('mysql:host=localhost;dbname=contisec_portal', 'root', '');
$sql = "SELECT dateid FROM date_header";
$stmt = $pdo->prepare($sql);
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

if ($stmt->rowCount() > 0) { ?>
  <select name="fileselect">
    <?php foreach ($results as $row) { ?>
      <option value="<?php echo $row['dateid']; ?>"><?php echo $row['dateid']; ?></option>
    <?php } ?>
  </select>
<?php } ?>

This is not tested

[diafol]

Yes - the foreach should be used with fetchAll(). +1