CODEWITHSUNDEEP
phpmysqldrop-down-menu
Owen
Owen

Reputation: 431

dropdown from two tables, php mysql

Say I have the two following tables:

country_table:

---------------------------------
|ID|country_code|country_name   |
|01|CA          |Canada         |
|02|US          |United States  |
---------------------------------

user_table:

----------------------------------------------
|id|user_id|country_code|email_address       |
|01|oswalsh|CA          |[email protected]    |
|02|jlong  |US          |[email protected]   |
----------------------------------------------

I am creating my user signup/edit account forms, and have a drop down for country. Originally I had simply stored the country_name in the user_table instead of using a country_code, however I have also decided to implement a map script...which is a massive mess of javascript files which make use of the country_code.

Thus I created the country_table so I could match the country_code and the country_name so I could map user interactions...However I am not sure how to go about creating the sql statement for the drop down.

What I have so far:

<?php
    include ('../../connect.php');

    $account_panel = mysql_query("SELECT * FROM user_table WHERE id=1")
            or die(mysql_error());

            while($row1 = mysql_fetch_array( $account_panel )) 
    {
?>
<select name="event_country" id="event_country"  required />
        <option selected="selected" value="<?php echo $row1['user_country']; ?>"><?php echo $row1['user_country']; ?></option>

        <?php           
            $countries = mysql_query("SELECT * FROM country_table")
            or die(mysql_error());

            while($row = mysql_fetch_array( $countries )) 
            {
                echo "<option value='". $row['value'] ."'>". $row['name'] ."</option>";
            }   
                echo "</select>";
        ?>

        <?php
        }
        mysql_close($connection);
        ?>

So as you should be able to see from above what is happening, is that the list is populated from the country_table, but I am trying to get the selected value from the user table. This does seem to work, but my problem is that the country code is what is stored and being displayed. So say I am grabbing the info for oswalsh...me...the country returned is CA, and I would like it to display Canada.

Upvotes: 0

Views: 1819

Answers (1)

Lazar Vuckovic
Lazar Vuckovic

Reputation: 808

If I understood you right, you need to familiarize yourself with SQL Joins

To join those two tables and get the country_name along with the user data from the user_table, you'd need an SQL statement like this:

SELECT ut.*, ct.country_name
FROM user_table ut
INNER JOIN country_table ct
ON ut.country_code = ct.country_code

In your case, this would result in the following:

1   oswalsh CA  [email protected]    Canada
2   jlong   US  [email protected]   United States

Also, you should consider using mysqli_ and stop using mysql_ functions. Here is a discussion on this topic on SA.

Upvotes: 1

Related Questions