[Resolved] Two Table SELECTS

[rcoursey]

I have two tables. One holds the ISO country codes and the associated country name. The other holds user info which contains a field called country

The tables are user and country respectively.

I want to get all of the country codes and only the user record that that relates to a specific record # for editing. Using the country table I want to create a dynamic select box that defaults to the option matching the country field in the users table.

I am a little confused how to use the SELECT statement for this.

[laserlight]

1. Select the from the user table the country field.
2. Select all the rows from the country table.
3. Print the selection menu with a loop of the rows of the country table records returned.
   If the country matches the one recorded from the user table, make it selected.

[rcoursey]

How do you use two selects in a query?

[rcoursey]

I need all of the entries from the 'country' table and olny the country code from the 'users' table. Since this is a from to edit the record I think I need to only select a user record with a userid that matched the ?id passed when the record is selected for editing in a "browse" type from. Does this make sense? And how do I do it?

[rcoursey]

This is one way I thought to accomplish the query but it doesn't work.

SELECT c.name, c.iso FROM country as c LEFT JOIN users as u WHERE u.userid='$id'

$id is a variable passed by the form as the record_id (users.userid)

Any suggestions?

[laserlight]

Well, instead of 1 query you could simplify and use 2 queries.
Somewhat more inefficient, but it will solve your problem immediately.

[rcoursey]

This is what I don't understand.

How do I use 2 queries at once?

[laserlight]

Here's some sample code:

//assume the database has been connected and selected
//select the user's country name
$query = mysql_query("SELECT country_name FROM users WHERE user_id='$id'");
$user_details = mysql_fetch_assoc($query);
//select all the country details as needed
$query = mysql_query("SELECT * FROM countries");

//write the selection menu
echo '<select name="country">';
while ($row = mysql_fetch_assoc($query)) {
	echo '<option value="' . $row['id'] . '"';
	//check if the user's country matches the current country listed
	if ($row['name'] == $user_details['country_name']) {
		echo ' selected';
	}
	echo '>' . $row['name'] . '</option>';
}
echo '</select>';

[rcoursey]

I see how to accomplish it now! Thanks laserlight!

[rcoursey]

One more question.

Why mysql_fetch_assoc() instead of mysql_fetch_row() ?

[stolzyboy]

straight from the manual

mysql_fetch_assoc
(PHP 4 >= 4.0.3)

mysql_fetch_assoc -- Fetch a result row as an associative array
Description
array mysql_fetch_assoc ( resource result)

Returns an associative array that corresponds to the fetched row, or FALSE if there are no more rows.

mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array. This is the way mysql_fetch_array() originally worked. If you need the numeric indices as well as the associative, use mysql_fetch_array().

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row() or add alias names. See the example at the mysql_fetch_array() description about aliases.

An important thing to note is that using mysql_fetch_assoc() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value.