CODEWITHSUNDEEP
phpmysql
Saud Kazia
Saud Kazia

Reputation: 192

Populate select drop down with data from two mysql queries

I'm sure this is easy to figure out to most anyone who knows PHP and mysql but I am not able to figure it out. I will try to explain in the best way possible

Please let me know if I'm miss something

Relevant Table structure

Table | Fields
category | id,group_n (more fields exist but are not required now)
p_group | id,group_n

what I want to achieve:

Query to find currently selected group_n field in category table if available based on the current _isset id taken from the _get URL in relation to the category.

Example of work flow :

sample rows for category

ID | category             | short     | group_n
1  | Skin Whitening Pills | pills     | Skin Whitening
2  | Skin Whitening Cream | cream     | Skin Whitening
3  | Weight Gain / Loss   | wgainl    | Weight Gain / Loss

As you can see multiple categories can have a single group_n. I use the group_n to show an expert for that group.

  1. select option is populated with the field group_n from category
  2. select options are populated with all options from p_group (except selected so there is no duplicate"

category with id 2 has group_n as Skin Whitening edit_category.php?id=2 would display all input options for for id 2 that are currently in the table row in order to edit them and update the query. Now when the select options for the group_n are populated the selected should be Skin Whitening but all rows in p_group should be available to select.

code

<?php
$qry=mysql_query("SELECT group_n from category where id=$id", $con);
if(!$qry)
{
die("Query Failed: ". mysql_error());
}

?>

<select name="group" id="group">
    <?php
    // First, define the desired default value
    $default_value = $row['group_n'];
    while($row=mysql_fetch_array($qry))
    {
        // Then you can mark that one as selected in your "while" loop
        $selected = ($row['group_n'] == $default_value) ? ' selected' : '';

                $qry2=mysql_query("SELECT * from p_group", $con);

    while ($row2 =  mysql_fetch_array($qry2))
        {
                    echo "<option value='" . $row2['group_n'] . "'" . $selected . ">" . $row2['group_n'] . "</option>";
        }


    }
    ?>
</select>

I've also tried with a single query left joining the two tables but the following query will list only a single row which doesn't get auto populated but gives the right value.

$qry=mysql_query("
SELECT a.group_n,
b.group_n from p_group a 
left join category b
on a.group_n=b.group_n
group by b.group_n where b.id=$id", $con);

I am not sure whether my query needs to be changed or the php but I can only get one of the queries to work with the select at a single time. I need to know how to get either both queries to work so selected option is one from the category table and the drop down from p_group table.

Ideally I would want the category table to be associated with the id from p_group but for now I'd settle for keeping it as text.

Upvotes: 0

Views: 2217

Answers (1)

Saud Kazia
Saud Kazia

Reputation: 192

After waiting for input from the user community which is unusually quiet this week, I searched around and fiddled with code until I came to the UNION sql join. This basically takes two sets of data and presents as one which did the trick. Not sure if this is the best way and maybe there is a pure PHP way, but this did the trick.

SQL snippet 

   $qry=mysql_query("select group_n as group_a from category where id=$id union select group_n from p_group group by group_n", $con);

Basically this would select query on left union it with the query on the right and display all the records.

Limitations 1. field count selected must match 2. UNION will select no duplicates 3. UNION ALL will select duplicates

full relevant code : 

   $qry=mysql_query("select group_n as group_a from category where id=$id union select group_n from p_group group by group_n", $con);
if(!$qry)
{
die("Query Failed: ". mysql_error());
}
    // First, define the desired default value
    $default_value = $row['group_a'];
    while($row=mysql_fetch_array($qry))
    {

$selected = ($row['group_a'] == $default_value) ? ' selected' : '';

        // Then you can mark that one as selected in your "while" loop
     echo "<option value='" . $row['group_a'] . "'" . $selected . ">" . $row['group_a'] . "</option>";


 }   
?>
</select>

Upvotes: 1

Related Questions