Select multiple (PHP and MySQL)

Question

I have a form with a select multiple like this:

<select name="states[]" size="5" multiple>
    <option value="2">state 1</option>
    <option value="3">state 2</option>
    <option value="4">state 3</option>
    <option value="5">state 4</option>
    <option value="6">state 5</option>
</select>

I want to have the possibility to choose more than one state, and then make the query to my database and show the description of each state chosen.

So this is what I have to make the query using PHP and MySQL:

$state = $_POST['states'];
$data = mysql_query("SELECT * from states WHERE id_state = '$state'",$db); 

while($row = mysql_fetch_array($data)){
    $result=$row['description'];
}

echo $result;

I have that code and it doesn't show anything.

How can I fix this problem?

Start using PDO for database driver. Next you can use var_dump($_POST) to see how the structure is of your incoming post-array. — Daniel
– Daniel, Commented Apr 23, 2013 at 2:15

Tamil Selvan C · Accepted Answer · 2017-01-04 08:45:18Z

7

Try this

$state = $_POST['states']; // return Array
$count_states = count( $state );

if( $count_states > 0) {
    $states = implode( ',', $state);
    $data = mysql_query("SELECT * from states WHERE id_state IN ($states)",$db); 

    while($row = mysql_fetch_array($data)){
       echo $row['description'];
    }
}

edited Jan 4, 2017 at 8:45

answered Apr 23, 2013 at 2:15

Tamil Selvan C

20.3k12 gold badges55 silver badges72 bronze badges

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

1 Comment

Daniel Soublett Over a year ago

Hi Tamil, thanks for your replay, I tried what you told me, and it works somehow, When I choose more than one option on the select, then I wrote this to show the results: echo $result; but it just display the last option that I choose.

Curtis Crewe · Accepted Answer · 2013-04-23 02:11:58Z

1

This would require a simple foreach to go through the array and get results based on each value as such,

foreach($_POST['states'] as $state) {
    $data = mysql_query("SELECT * from states WHERE id_state = '$state'",$db);
    $row = mysql_fetch_array($data);
    echo $row['description'];
}

Also since you're not protecting your query in some sort and are using mySQL which has been deprecated as of PHP 5.5.0, I suggest you looking into PDO or mySQLi Prepared statements

answered Apr 23, 2013 at 2:11

Curtis Crewe

4,3766 gold badges28 silver badges32 bronze badges

1 Comment

Daniel Soublett Over a year ago

Thanks a lot for your response, I did exactly what you told me and it worked, I really appreciate your help, I just needed to add an if before that code, because it was displaying an error. But now I solve that :)

stUrb · Accepted Answer · 2013-04-23 02:29:57Z

$_POST['states'] holds an Array with all the ID's of the selected states.

Off course you can query your database for every posted state_id, but way nicer (and faster) would it be to make a query which looks like this and uses only one query:

SELECT description FROM states WHERE id_state=1 OR id_state=2 etc etc

This also might be a good point to start using a database abstraction layer like PDO. As the number of posted states is variable, we need to make the statement also variable:

// The [connection setup][2] by PDO is done in $conn, with some proper exception handlers
// e.g. $conn = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

// Fill an array with count() number of elements with value 'id_state=?'
$place_holders = array_fill(0, count($_POST['state']), 'id_state= ?');

//implode the array 
$place_holders = implode(' OR ', $place_holders);

// prepare the query
$st = $conn->prepare("SELECT description FROM state WHERE $place_holders");

// execute to above prepared query with the $_POSTED states
$st->execute($_POST['state']);

// traverse the result
foreach($st->fetchAll() AS $r){
     // do some magic
}

Peter Mortensen · Accepted Answer · 2013-05-25 20:55:43Z

0

You could build the string by iterating through the array:

$state = "";

foreach($_POST['states'] AS $s)
{
    // Sanitize $s here
    $state .= "`id_state` = " . $s . " OR";
}

if($state)
{
    $state = substr($state, 0, -3);

    $data = mysql_query("SELECT * from states WHERE $state",$db); 

    while($row = mysql_fetch_array($data)){
        echo $row['description'];
    }
}

Of course, you should use something like MySQLi or PDO to handle database interaction. They will have ways to sanitize input easily so you can avoid obvious SQL injection.

Tamil has a pretty good IN select method as well. This is just one option.

edited May 25, 2013 at 20:55

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Apr 23, 2013 at 2:14

Kai Qing

18.9k6 gold badges41 silver badges61 bronze badges

Comments

NyC Solutions Group · Accepted Answer · 2018-01-25 05:14:50Z

Example (pages for edit):

//On select_multiple.php (Form):

<?php
//Conn
include('incl_config.php');

//Multiple data to bring
$sql = " select COD_DXS,VALOR_DXS from hc_dxsindromico where ESTADO_DXS='1' ";
$result=@mysql_query($sql);       
?>

//In the form select:
<select  multiple="multiple" size="7"  name="dxsindromico[]"> //look yes or yes brackets []
    <option value="" selected="selected">Choose one or more options</option>
        <?php
        while($row=mysql_fetch_array($result)){
    ?>
        <option value="<?php echo $row['COD_DXS']; ?>" style="color:#F00;"><?php echo $row['VALOR_DXS'];?></option> 
        <?php } ?>  
</select>

//////////// On grabar_mtr.php ///////////////

<?php
include('incl_config.php');
/*Multiple selection form in HTML5, PHP and Bootstraps
Created by: www.nycsoluciones.com
Version: 1.1*/

//we use a foreach to traverse the array (values of our select and save them in the table dxsindromico_data)

if(isset($_POST['dxsindromico'])){
    foreach( $_POST['dxsindromico'] as $insertar ) {
        //echo $insertar;
        $sqli="insert into dxsindromico_data(DXSINDROMICO_HC) values('$insertar')";
        //echo $sqli;
        //exit;
        $resulti=mysql_query($sqli);
    } 
} else{
    foreach( $_POST['dxsindromico'] as $insertar ) {
        //echo $insertar;
        $sqli="insert into dxsindromico_data(DXSINDROMICO_HC) values('$insertar')";
        $resulti=mysql_query($sqli);
    } 
}
?>

Collectives™ on Stack Overflow

Select multiple (PHP and MySQL)

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related