1,105,534 Community Members

Populating a drop down box using PDO Mysql

Member Avatar
ebutt13
Newbie Poster
20 posts since Dec 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
veedeoo
Veteran Poster
1,016 posts since Oct 2011
Reputation Points: 314 [?]
Q&As Helped to Solve: 166 [?]
Skill Endorsements: 17 [?]
 
0
 

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

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
1
 

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.

Member Avatar
simplypixie
Practically a Master Poster
673 posts since Oct 2010
Reputation Points: 123 [?]
Q&As Helped to Solve: 122 [?]
Skill Endorsements: 6 [?]
 
1
 

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

Member Avatar
diafol
Where are my eyes?
12,977 posts since Oct 2006
Reputation Points: 1,821 [?]
Q&As Helped to Solve: 1,848 [?]
Skill Endorsements: 92 [?]
Moderator
Featured
Sponsor
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: