Display value from database inside a Multiple Select Box

Question

This is the basic multiple select box:

<select name="State" size="5" multiple="multiple">

    <option value="1">Kedah</option>
    <option value="2">Kelantan</option>

</select>

The question is, instead of manually typing an option value, how can I get the data from database to be use as option value.

My db looks like this :

    mysql> select sta_id,sta_name
    -> from sys_state
    -> where sta_status = '1';

+--------+----------------------------------+
| sta_id | sta_name                         |
+--------+----------------------------------+
|     01 | JOHOR                            |
|     02 | KEDAH                            |
|     03 | KELANTAN                         |
|     04 | MELAKA                           |
|     05 | NEGERI SEMBILAN                  |
|     06 | PAHANG                           |
|     07 | PULAU PINANG                     |
|     08 | PERAK                            |
|     09 | PERLIS                           |
|     10 | SELANGOR                         |
|     11 | TERENGGANU                       |
|     12 | SABAH                            |
|     13 | SARAWAK                          |
|     14 | WILAYAH PERSEKUTUAN KUALA LUMPUR |
|     15 | WILAYAH PERSEKUTUAN LABUAN       |
|     16 | WILAYAH PERSEKUTUAN PUTRAJAYA    |
+--------+----------------------------------+

tried to call function containing sqlselect inside thos option value, well, it failed — Ryurial
– Ryurial, Commented Jan 27, 2015 at 8:54
What kind of db connection do you use? mysql_XXX, MySqlI, PDO? You need to provide some more information. Otherwise people won't be able to provide helpful answers. — Markus Müller
– Markus Müller, Commented Jan 27, 2015 at 9:05

RBS · Accepted Answer · 2015-01-27 09:14:33Z

4

Firstly, you should connect to DB PHP Mysql Connection

Next step is to fetch those rows and write data to the variable(it can be an array or any object, in my case it's an array) PHP MySQL Data Select:

$sql = "SELECT sta_id,sta_name FROM sys_state WHERE sta_status = '1'";
$result = $conn->query($sql);
$resultData = [];
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
         $resultData[$row['sta_id']] = $row['sta_name'];
    }
}

Finally, print them on html page:

<select name="State" size="5" multiple="multiple">
<?php
foreach ($resultData as $key => $value) {
    echo '<option value="'.$key.'">'.$value.'</option>';
}
?>
</select>

answered Jan 27, 2015 at 9:14

RBS

1086 bronze badges

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

Comments

Rakesh Sharma · Accepted Answer · 2015-01-27 09:02:47Z

0

Try

<?php $qry = mysql_query("select sta_id,sta_name from sys_state where sta_status = '1'");?>

<select name="State" size="5" multiple="multiple">
<?php while($row = mysql_fetch_assoc($qry)){?>
    <option value="<?php echo $row['sta_id'];?>"><?php echo $row['sta_name'];?></option>
 <?php }?>
</select>

Note :- mysql_* has been deprecated use mysqli_* or PDO

answered Jan 27, 2015 at 9:02

Rakesh Sharma

13.7k5 gold badges41 silver badges44 bronze badges

Comments

Sougata Bose · Accepted Answer · 2015-01-27 09:05:01Z

0

Try with -

$mysqli = new mysqli("localhost", "user", "password", "database");
$res = $mysqli->query("select sta_id,sta_name from sys_state where sta_status = '1'");

?>
<select name="State" size="5" multiple="multiple">
<?php

while ($row = $res->fetch_assoc()) {
    echo "<option value='".$row['sta_id']."'>".$row['sta_name']."</option>";
}
?>
</select>

answered Jan 27, 2015 at 9:05

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Comments

Fevly Pallar · Accepted Answer · 2015-01-27 09:07:58Z

0

Well, basically you can just echo them

$dsn = "mysql:host=localhost;dbname=x"; 
$user="root";
$password="";
try{
$connect = new PDO($dsn,$user,$password); // PDO= PHP Data Object
$connect->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){echo $e->getMessage();}


$select = $connect->query("select sta_id,sta_name from sys_state where sta_status = '1';"); 

echo "<select name=\"State\" size=\"5\" multiple=\"multiple\">";

foreach ($select as $temp){


   echo "<option value=\"1\">".temp["sta_name"]."  </option>";
    ......
    ....


}
echo "</select>";

$connect=null;// CLOSE CONNECTION

but constructing them into a function would be better

answered Jan 27, 2015 at 9:07

Fevly Pallar

3,1092 gold badges17 silver badges20 bronze badges

Comments

Ryurial · Accepted Answer · 2015-01-28 04:19:55Z

0

I managed to do it this way though:

<?php     // MULTI SELECTION 



    $sql1 = " SELECT sta_id,sta_name FROM dof_perkhidmatan.sys_state WHERE sta_status = '1' ";

    $result1 =  $db->sql_query($sql1,END_TRANSACTION);

    if($db->sql_numrows($result1) > 0)
    {

    ?>

     <select id="State" name="State" size="16" multiple="multiple">
     <?php

    // output data of each row
    while($row_select = $db->sql_fetchrow($result1))        
        {
         echo '<option value="'.$row_select['sta_id'].'">'.$row_select['sta_name'].'</option>';
        }


    ?>
    </select>
    <?php
    }
    ?>

answered Jan 28, 2015 at 4:19

Ryurial

111 gold badge1 silver badge2 bronze badges

Comments

LaideLawal · Accepted Answer · 2017-05-09 23:12:25Z

0

Try this, it worked fine for me

<?php
<!-- Connect to your database-->

require 'dbconnect.php';

<!--  Fetch required data from the database-->

$sql="SELECT sta_id,sta_name from sys_state WHERE sta_status = '1' ";

$result = mysqli_query($con, $sql);

?>
<select multiple size="6">

<?php while($row = mysqli_fetch_assoc($result)){?>
          <option value="<?php echo $row['sta_id']; ?>"> <?php echo $row['sta_name']; ?></option>

 <?php
}
?>
 </select>

answered May 9, 2017 at 23:12

LaideLawal

771 silver badge10 bronze badges

1 Comment

Mm-Art-In Over a year ago

This is a code only answer which doesn't add anything that other answers have more completely covered. (This answer also features non best practise coding )

Mm-Art-In · Accepted Answer · 2017-05-09 23:53:36Z

0

<?php $qry = mysql_query("select sta_id,sta_name from sys_state where sta_status = '1'");

$dropdown ='<select name="State" size="5" multiple="multiple">';
while($row = mysql_fetch_assoc($qry)){
    $dropdown .='<option value="'.$row['sta_id'].'">'.$row['sta_name'].'</option>';
 }
$dropdown .= '</select>';
echo $dropdown;

And this will echo a dropdown with dynamic values

edited May 9, 2017 at 23:53

Mm-Art-In

23.1k13 gold badges81 silver badges144 bronze badges

answered Jan 27, 2015 at 9:06

Veerendra

2,6202 gold badges25 silver badges39 bronze badges

Collectives™ on Stack Overflow

Display value from database inside a Multiple Select Box

7 Answers 7

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related