Can't PDO::FETCH_ASSOC data from <option> tags

[LastMitch]

Hi

I am learning PDO. I already got my PDO-DB ready.

The query works because it does fetch data when I echo out the data but I don't know how to echo out the data from the database to the dropdown list.

I try to echo out this way (it didn't work):

        <select name="PostCats">
        <?php
        function getCats() {
        global $conn;
        $stmt = $conn->query('SELECT * FROM category');
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "<option value=".$row['id'].">".$row['title']."</option>";
        }}?>
        </select>

I also try to echo out this way too (it didn't work either):

        <select name="PostCats">
        <?php
        function getCats() {
        global $conn;
        $stmt = $conn->query('SELECT * FROM category');
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {?>
        <option value="<?php echo $row['id']; ?>"><?php echo $row['title']; ?></option>
        <?php }} ?>
        </select>

I want to learn the correct way of echoing out the data from the database with PDO or Mysqli to the dropdown list.

I have a habit of combining html and php and now I realized it's not good to combine both together.

I tend to do something like this:

<?php
$query = "SELECT * FROM category";
$result = mysql_query ($query);
echo "<select name=post value=''>POST NAME</option>";
while($mi = mysql_fetch_array($result)){
    echo "<option value=$mi[id]>$mi[title]</option>";
}echo "</select>";
?>

Then I realized PDO & Mysqli doesn't work.

Any Suggestions and explanations will help. I appreciate it. Thanks!

[pritaeas]

<select name="PostCats">
<?php
    function getCats() {
        global $conn;
        $stmt = $conn->query('SELECT * FROM category');
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo "<option value=".$row['id'].">".$row['title']."</option>";
        }
    }
?>
</select>

As you can see above, you define your code in a function, but it is never called, so no execution, no output.

[LastMitch]

@pritaeas

Thanks for the reply and explanation.

You mean something like this:

<select name="PostCats">
<?php
function getCats() {
global $conn;
$stmt = $conn->query('SELECT * FROM category');
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<option value=".$row['id'].">".$row['title']."</option>";
$stmt = $conn->prepare($row);
$stmt->execute();
}
}
?>
</select>

It didn't work either.

OK these are the scenario I try but it didn't work either:

<select name="PostCats onchange="this.form.submit();">
<?php
function getCats() {
global $conn;
$sql = "SELECT * FROM category";
if ($stmt = $conn->query($sql)) {
if ($stmt->fetchColumn() > 0) {
$sql = "SELECT * FROM category"; ?>
<?php foreach ($conn->query($sql) as $row){ ?>
<option value="<?php echo $row['id']; ?>"><?php echo $row['title']; ?></option>
$stmt = $conn->prepare($sql);
$stmt->execute();
<?php }?>
<?php }
}
$stmt = null;
$conn = null;
}?>
</select>

I also try this:

<select name="PostCats">
function getCats($id,$title) {
global $conn;
$id = $row['id'];
$title = $row['title'];
$sql = "SELECT * FROM category";
$stmt = $conn->prepare($sql);
$stmt->execute(array($id,$title));
echo "<option value=".$row['id'].">".$row['title']."</option>";
$stmt = null;
$conn = null;
}
</select>

and this:

<select name="PostCats">
function getCats($id,$title) {
global $conn;
$id = $_POST['id'];
$title = $_POST['title'];
$sql = "SELECT * FROM category";
$stmt = $conn->prepare($sql);
$stmt->execute(array($id,$title));
echo "<option value=".$id.">".$title."</option>";
$stmt = null;
$conn = null;
}
</select>

Can tell me which example is the closet solution so I can focus on that one.

Any more Suggestions and explanations will help. I appreciate it. Thanks!

[LastMitch]

I actually got it to working without the function:

<select name="PostCats"> 
<?php
global $conn;
$sql = "SELECT * FROM category";
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC); 
if ($stmt->rowCount() > 0) { ?>
<?php foreach ($results as $row){ ?>
<option value="<?php echo $row['id']; ?>"><?php echo $row['title']; ?></option>   
<?php }?>
<?php }?>
</select>

I think the function is interfering with the connection or echoing out the data I don't know why it does that.

[diafol]

I realise that this is solved, but do you really need a global variable? It looks needless to me.

[pritaeas]

I think the function is interfering with the connection or echoing out the data I don't know why it does that.

No. What you made is a function. The code within a function only runs when you call it. The solution you have is fine, what you could've done was this (not recommended though):

<select name="PostCats">
<?php
  function getCats() {
    // ...
  }
  getCats();
?>
</select>

[LastMitch]

I realise that this is solved, but do you really need a global variable? It looks needless to me.

Yes, you are correct. I don't need the global variable but I put there to remind me it's connected to the database.

No. What you made is a function. The code within a function only runs when you call it. The solution you have is fine, what you could've done was this (not recommended though):

OK, I got a better understanding how the code works now. Thanks for the explanation.