[SOLVED] SORT/While/Query Problem

[dweb77]

 

I am trying to sort a query by the state_name column for viewing. I have a table where I SELECT a city and state_id for a user. Once I have the city and state_id I am able to SELECT characteristics (including the state_name) relevant to each city and state from another table (the data has to be in a separate table). I want to display the data in rows that are sorted, but also have the cooresponding city and state listed with the characteristics.

 

Because I am taking data from two different tables, and running a 'while loop' for one query dependent on the other, the ORDER BY function does not work (at least that is what I think is the problem). The data IS being displayed with the appropriate fields associated, but I cannot sort the way I want. I have included code below.

 

Any thoughts?  Thanks.

 

//Initial query

 

$query = "SELECT state_id, city_id FROM cb_save_url WHERE user_id = '$user' LIMIT $start, $display";

$state_result = mysql_query ($query);

 

//based on the query above query the other data

 

while ($my_sch = mysql_fetch_array($state_result, MYSQL_ASSOC)) {

 

$my_sc = $my_st['state_id'];

 

$query = "SELECT state_name, state_id, state_site FROM s_states WHERE state_id='$my_st' ORDER BY state_name";

$my_st_result = mysql_query ($query);

 

while ($my_st_final = mysql_fetch_array($my_st_result, MYSQL_ASSOC)) {

 

//display the data

 

echo '

<tr>

<td width="325" height="20">

<p align="left"><font face="Verdana" size="2">

 

<a href="' . $my_st_final['state_site'] . ' " target="_blank">' . $my_st_final['state_name'] . ' </a>

 

</font></td>

<td width = "45" align="center"><font face="Verdana" size="1">

 

<a href="state.php?st=' . $my_st_final['state_id'] . '&stid=' . $my_st['st_id'] . '">INFO</a>

 

</font>

</td>

 

';

 

}

}

 

[Devine]

Maybe don't use the same variable for $query? It's happened to me that it got screwed up because I used the same variable twice for a while statement.. but thats all the suggestions I can give.. heh..

[dweb77]

I had checked that. The issue has to do with the fact that there are the two while loops.

 

[thedarkwinter]

are their multiple results to both queries...

 

will second query return one result for each result of the first query?

if so then you should just us one query:

 

SELECT cb_save_url.state_id, cb_save_url.city_id, s_states.state_name, s_states.state_site
FROM cb_save_url 
LEFT JOIN s_states ON cb_save_url.state_id = s_states.state_id
WHERE user_id = '$user'
ORDER BY s_states.statename;
LIMIT $start, $display"

 

does that make sense?

 

[dweb77]

Thanks. It does...I am new to this so while I knew of the Join, I was not familiar with the specific use.