Dynamic dropdown lists from 2 mysql tables

Question

Im looking to create a series of drop down boxes which are populated from a MYSQL database.

I have 2 tables in the database,

id, name -
1|hot
2|cold
3|worm
id, ids from table #1 (1, 3, 4) -
1|1, 3, ...
2|2, 3, ...
3|1, 2, ...

I need to select records from table 2 and create dropdown menus using the names from table 1.

Ive used the following code to create a dropdown lists, but it displays only one dropdown list and stops.

Any help is much appreciated

    <?php
    $getRss = mysql_query("SELECT * FROM optionals_groups where resid=".$_SESSION['restaid']." order by id asc");
    while ($rss = @mysql_fetch_array($getRss)) { ?>
    <select name="a_<?=$rss['id']?>" id="a_<?=$rss['id']?>" >
      <option value="1" >Select Options</option>
      <?php
        $ot=0;
        $last_ot="";
        $goptionals=explode(', ',($rss['goptionals']));  
    $getRss= mysql_query("SELECT * FROM optionals order by id asc");
        while ($rsso = mysql_fetch_array($getRss)) {
            if (in_array($rsso['id'],$goptionals)) {
                $ot++;
                $last_ot=$rsso['optional'];    
    ?>
      <option value="<?=$rsso['id']?>" ><?=$rsso['optional']?></option>
      <?php 
      } 
    }
    ?>
    </select>
    <br />
    <?php } ?>

UPDATE

Here is where I am so far. I was able to create the dropdown lists using table 2 and I need to figured out how to get the names from table 1 for each id I have listed from table 2.

I have the feeling that something with join will do the job, but not quite sure.

Any ideas?

    <?php
      $getRss = mysql_query("SELECT * FROM optionals_groups where       resid=".$_SESSION['restaid']." order by id asc");
    while ($rsr = @mysql_fetch_array($getRss)) {
    $goptionals=explode(', ',($rsr['goptionals']));

echo "<select name='a_".$rsr['id']." id='a_".$rsr['id']."' >";
echo "<option value='1' >Select Options</option>";
    foreach($goptionals as $v)
    {
        echo "<option value=".$v." >".$v."</option>";
    }
         echo "</select><br>";       
    }?>

markus · Accepted Answer · 2012-11-15 16:25:06Z

2

The correct way of doing this is to write a join query that already gets you all the information you need and then create the dropdown. Never query your database inside a loop!

More tips:

Do NOT mix PHP and markup like that, create your markup with the help of concatenation and then send it to the view
Do NOT use the error supressor @. It will obfuscate errors you really want to know about.
Do NOT use the ancient, insecure mysql_* API anymore, use PDO with prepared statements instead.

answered Nov 15, 2012 at 16:25

markus

40.8k23 gold badges100 silver badges143 bronze badges

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

1 Comment

TimR Over a year ago

Thank you for the tips markus-tharkun. I'm new to php/mysql and still learning...I wish I can write join queries, but I'll try.

david strachan · Accepted Answer · 2012-11-15 17:19:36Z

0

In your example the INNER JOIN returns rows when there is at least one match in both tables(id)

The syntax for INNER JOIN is

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

see description of JOIN

answered Nov 15, 2012 at 17:19

david strachan

7,2282 gold badges25 silver badges33 bronze badges

Collectives™ on Stack Overflow

Dynamic dropdown lists from 2 mysql tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related