a better mysql select statement?

Okay, I am working on a new project. I need some guidance for a mysql select statement.
In my users table I ask for state and country, and lots of other stuff that doesn't matter for this right now. Instead of dealing with people who can not spell, or whatever, I put the states and countries in their own tables. In the users table, I have the id for the state, and country.
is there a better way to get that info instead of this: This is for parsing emails.

...
$select = "SELECT state, country FROM users WHERE id='$id' LIMIT 1";
$query=MYSQLI_QUERY($link, $select);
$info = MYSQLI_FETCH_ASSOC($query);
$state_id=$info['state'];
$country_id=$info['country'];
$select="SELECT state_name FROM state WHERE id='$state_id' LIMIT 1";
$query=MYSQLI_QUERY($link, $select);
$state = MYSQLI_FETCH_ASSOC($query);
$state_name=$state['state_name'];
$select="SELECT country_name FROM country WHERE id='$country_id' LIMIT 1";
$query=MYSQLI_QUERY($link, $select);
$country = MYSQLI_FETCH_ASSOC($query);
$country_name=$country['country_name'];
$select="SELECT * FROM users WHERE id='$id' LIMIT 1";
$query=MYSQLI_QUERY($link, $select);
WHILE($tags = MYSQLI_FETCH_ASSOC($query)) {
$tags['state']=$state_name;
$tags['country']=$country_name;
foreach($tags as $key => $value) {
$tag = strtoupper($key);
$tag = '{'.$tag.'}';
$email = str_replace($tag, $value, $email);
}
...

Code (markup):

Basically I have to select the state and country id's from the users table, then re-assign those vars to other ones, then go to the state table and get the state name, then do the same for the country table, and go back to the users table to get all the info from it. Then assign the info that would normally be the state and country ids with the names that I got earlier.

Would it be best to re-work my signup script to store the state name or abbreviation instead of the id? or is there a better way to construct a select statement to get that info. If the thought is to store the name instead of the id, doesn't that kind of go against the whole db normallization thing?

thanks,
Michael

 

OMG please format your code first to make it readable

// select users
$select     = "SELECT state, country FROM users WHERE id='$id' LIMIT 1";
$query      = MYSQLI_QUERY($link, $select);
$info       = MYSQLI_FETCH_ASSOC($query);
$state_id   = $info['state'];
$country_id = $info['country'];

// select state?
$select     = "SELECT state_name FROM state WHERE id='$state_id' LIMIT 1";
$query      = MYSQLI_QUERY($link, $select);
$state      = MYSQLI_FETCH_ASSOC($query);
$state_name = $state['state_name'];

// selet country
$select       = "SELECT country_name FROM country WHERE id='$country_id' LIMIT 1";
$query        = MYSQLI_QUERY($link, $select);
$country      = MYSQLI_FETCH_ASSOC($query);
$country_name = $country['country_name'];

// select all users?
$select = "SELECT * FROM users WHERE id='$id' LIMIT 1";
$query  = MYSQLI_QUERY($link, $select);
WHILE($tags = MYSQLI_FETCH_ASSOC($query)) 
{
    $tags['state']   = $state_name;
    $tags['country'] = $country_name;
    foreach($tags as $key => $value) 
    {
        $tag   = strtoupper($key);
        $tag   = '{'.$tag.'}';
        $email = str_replace($tag, $value, $email);
    }
}

Code (markup):

much more readable! but i still don't get what you want with it and what your idea about this all is.

 

I would use a drop-down menu for state and country so that you don't have to concern if they misspell. Then no state/country ID's
and its always spelled correctly and can be used as variable with any error potential.

drop down menus for Country/State/City
http://bdhacker.wordpress.com/2009/...ynamically-into-your-html-form-by-javascript/

 

EricBruggema,
Please explain how to make the code more readable. I used the code tags. I am blind, and thought that did all the stuff. It reads just fine for me the way I did it.
I was asking if there is a better way to get the state and country names. On the signup form I have the state and countries in a dropdown list with the value set to the id, so the id gets stored in the users table. I have sense changed it to store the state or country name in the users table, which eliminates the extra select statements.

ezprint2008,
Thanks for that, and the script might be something to use later, but I need to have it all work without using any kind of JavaScript or anything first. that kind of stuff does not tend to work well for those of us that use screen reading programs.

Changing the dropdown value to the state or country name so that is what is inserted in the users table seems to be the way to go for this one I guess.

Thanks,
Michael