How to fill <select> with options using PHP and SQL?

Question

My question is: I want to show all SQL answers from a specific table inside the <select> using php.

I have tried to get it working, but without much success as it only shows one result.

<?php
error_reporting(E_ALL);
include 'connect.php';
include 'header.php';
set_time_limit(10);


$cat = $bdd->query('SELECT cat_id,cat_name,cat_description FROM categories');
$categories_list = $cat->fetch();


While ($categories_list = $cat->fetch()) {
$cat_name = $categories_list['cat_name'];
}


$cat->closeCursor();

echo '<form method="post" action="accès/create_topic_post.php">';
echo '<label for="sujet">Sujet :';
echo '<input type="text" name="sujet" id="sujet" required autofocus>';
echo '</label>';
echo '<label for="cat">Catégories :';
echo '<select name="topic_name">';
echo "<option value=\"".$cat_name."\" >$cat_name </option>";
echo '</select>';
echo '<input type="submit" value="Envoyer">';
echo '</form>';

?>

You have to use loop for this.

Abhishek Gurjar
– Abhishek Gurjar

2016-10-03 08:47:54 +00:00
Commented Oct 3, 2016 at 8:47 — Abhishek Gurjar
– Abhishek Gurjar, Commented Oct 3, 2016 at 8:47
Welcome to Stack Overflow.

Rax Weber
– Rax Weber

2016-10-03 08:50:07 +00:00
Commented Oct 3, 2016 at 8:50 — Rax Weber
– Rax Weber, Commented Oct 3, 2016 at 8:50

Praveen Kumar Purushothaman · Accepted Answer · 2016-10-03 08:49:10Z

2

That's totally not the way to do it. Have a separation between PHP and HTML. And make sure you have the option echoed out inside the loop. Something like this:

<form method="post" action="accès/create_topic_post.php">
  <label for="sujet">Sujet :
    <input type="text" name="sujet" id="sujet" required autofocus>
  </label>
  <label for="cat">Catégories :
    <select name="topic_name">
    <?php
    // Loop it here.
    while ($categories_list = $cat->fetch()) {
        $cat_name = $categories_list['cat_name'];
        echo "<option value=\"".$cat_name."\" >$cat_name </option>";
    }
    ?>
    </select>
  </label><!-- You forgot this -->
  <input type="submit" value="Envoyer" />
</form>

answered Oct 3, 2016 at 8:49

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Praveen Kumar Purushothaman Over a year ago

Some people wanna watch the world burn with your downvotes right? What's the reason?

RiggsFolly Over a year ago

I see no obvious reason for a DV on this answer

Praveen Kumar Purushothaman Over a year ago

@RiggsFolly True, as I said, "some people wanna watch the world burn". LoL.

devpro Over a year ago

You are right, DV without sharing reason or improvement, bad approach

devpro · Accepted Answer · 2016-10-03 08:56:10Z

There are two approach to achieve your desired output.

Approach One:

Store cat_name in a new array and use it in your HTML or anywhere, where you want to use.

<?php
$cat_name = array();
while($categories_list = $cat->fetch()) {
  $cat_name[] = $categories_list['cat_name'];
}
?>

<select name="topic_name">
<?php
foreach ($cat_name as $key => $value) {
?>
  <option value="<?=$value?>" ><?=$value?> </option>
}
?>
</select>

Approach 2:

<?php
while ($categories_list = $cat->fetch()) {
?>
   <option value="<?=$categories_list['cat_name']?>" ><?=$categories_list['cat_name']?> </option>
<?php
}
?>

Whats wrong with your code:

$cat_name = $categories_list['cat_name']; this will only store the last value of your query, you must need to store the value in an array or use <option> inside the while() loop.

Ben Hillier · Accepted Answer · 2016-10-03 09:05:46Z

2

You keep replacing cat_name every time you read data from the SQL server. You need to store it in an array

<?php
    error_reporting(E_ALL);
    include 'connect.php';
    include 'header.php';
    set_time_limit(10);


    $cat = $bdd->query('SELECT cat_id,cat_name,cat_description FROM categories');
    //$categories_list = $cat->fetch(); This line discards a row of data


    $cat_names = array();   //array for category names
    While ($categories_list = $cat->fetch()) {
        //Add newest 'cat_name' to the array
        $cat_names[] = $categories_list['cat_name'];
    }


    $cat->closeCursor();

    echo '<form method="post" action="accès/create_topic_post.php">';
    echo '<label for="sujet">Sujet :';
    echo '<input type="text" name="sujet" id="sujet" required autofocus>';
    echo '</label>';
    echo '<label for="cat">Catégories :';
    echo '<select name="topic_name">';
    //Loop and read back each category name
    foreach ($cat_names as $cat_name){
        echo "<option value=\"".$cat_name."\" >$cat_name </option>";
    }
    echo '</select>';
    echo '<input type="submit" value="Envoyer">';
    echo '</form>';

?>

edited Oct 3, 2016 at 9:05

answered Oct 3, 2016 at 8:49

Ben Hillier

2,1041 gold badge11 silver badges15 bronze badges

3 Comments

ChristianF Over a year ago

This is the best answer out of the ones posted so far. Seeing as it's the only one that clearly separates concerns, between the business logic and the generation of the output. Only things I'd change is to remove the closeCursor() call, as it's not needed in this example, and use htmlspecialchars() when echoing $cat_name to prevent XSS.

Ben Hillier Over a year ago

@YourCommonSense: Why not?

ChristianF Over a year ago

@YourCommonSense: Are you referring to the fact that it would drop the first line, due to the fetch() statement outside of the loop? Myserious comments like "it won't work, find out why yourself." is generally quite unhelpful, at least without a hint to where the author should look.

Your Common Sense · Accepted Answer · 2016-10-03 09:08:06Z

1

You don't have to loop over results manually, as PDO has a function that can do it for you already, called fetchAll(). You have to use it instead of fetch() if you want to get an array of rows.

However, beside it, you need to learn PHP and programming in general. Because your current code makes little sense. To output an array, you have to use a loop:

$cat = $bdd->query('SELECT cat_name FROM categories');
$categories_list = $cat->fetchAll(PDO::FETCH_COLUMN); // here it is

echo '<form method="post" action="accès/create_topic_post.php">';
echo '<label for="sujet">Sujet :';
echo '<input type="text" name="sujet" id="sujet" required autofocus>';
echo '</label>';
echo '<label for="cat">Catégories :';
echo '<select name="topic_name">';
foreach ($categories_list as $cat_name)
{ 
    echo "<option value='$cat_name'>$cat_name</option>";
}
echo '</select>';
echo '<input type="submit" value="Envoyer">';
echo '</form>';

edited Oct 3, 2016 at 9:08

answered Oct 3, 2016 at 8:53

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

3 Comments

Romain Sickenberg Over a year ago

Better but, it out just some int

Your Common Sense Over a year ago

Oh yeah. I blindly copy-pasted your query. It is fixed now

Romain Sickenberg Over a year ago

Thanks so mutch :D

fistuks · Accepted Answer · 2016-10-03 08:51:03Z

1

Move the fetch loop to the output section of your code.

So instead of this:

echo "<option value=\"".$cat_name."\" >$cat_name </option>";

move the loop so it will look like this:

While ($categories_list = $cat->fetch()) {
  $cat_name = $categories_list['cat_name'];
  echo "<option value=\"".$cat_name."\" >$cat_name </option>";
}

answered Oct 3, 2016 at 8:51

fistuks

4793 silver badges5 bronze badges

Comments

Alok Patel · Accepted Answer · 2016-10-03 08:52:01Z

1

Use while loop while you're outputting the category in select option.

Like this,

While ($categories_list = $cat->fetch()) {
       echo "<option value=\"".$categories_list['cat_name']."\" >$categories_list['cat_name'] </option>";
}

answered Oct 3, 2016 at 8:52

Alok Patel

8,0726 gold badges35 silver badges48 bronze badges

Collectives™ on Stack Overflow

How to fill <select> with options using PHP and SQL?

6 Answers 6

4 Comments

Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related