php/mysql query ORDER results problem

[majocmatt]

Hi there, I was wondering if someone could enlighten me as to how to fix a small problem I'm having.

What I'm doing is creating a search module and am having problems with ordering the results properly. How i have the database setup is a users table, country(list of countries) table, territories table and finally a linking table that has user id, country id and territory id, all with foreign keys.

Now when someone makes a search for say.. Florida, USA, they get say.. 3 users as their results.

Well how i constructed this query is selecting everything from the linking table where country=usa and state=florida to find the user id's. Then a while loop to start getting the results, and inside the while loop i queried the user table to find the human readable details (name, address, etc, etc). Well then I had to make a nested while loop to then properly extract the details. But my query to get the details, where i queried the users table, I set ORDER BY name.

The problem here is the order has already been made during my first while loop. How can I avoid this and maybe re-think my queries to get it where I can order them right?

Tables:
reps_coverage (country_id, state_id,rep_id)
reps (nickname,name, address, etc, etc)

Note that I have already captured the POST's before this code starts, so what country/state they picked is already assumed.

[code]
$get_reps = "SELECT * FROM reps_coverage " . $say_where; // $say_where is just a dynamic WHERE clause
$get_reps_query = mysql_query($get_reps) or die("Could not get reps");
$matching_rows = mysql_num_rows($get_reps_query);
echo "<table width='100%' cellpadding='0' cellspacing='0'>\n";
echo "<tr>\n";
echo "<td class='listingResults'>There are $matching_rows Representatives matches.</td>\n";
echo "</tr></table>\n";
echo "<table width='100%' cellpadding='0' cellspacing='0'>\n";
while($get_reps_row = mysql_fetch_array($get_reps_query)) {

    $getreps = "SELECT * FROM reps WHERE rep_id = '$get_reps_row[rep_id]' ORDER BY nickname ASC";
    $getreps2 = mysql_query($getreps) or die(mysql_error());
    while($getreps3=mysql_fetch_array($getreps2)) {
        
        // print details... name, address, etc.....

        }
}
[/code]

Currently, they are ordered by how the come out of the first while loop, which is in not particular order, simply the order it found them in the DB.

I need it to print alphabetical by nickname.

All the queries work just fine, except the ORDER BY part

[karthikeyan_coder]

ORDER BY wont working. coz you are in combo loops. so relult will be unordered manner. thinking which will be good

[majocmatt]

so, theres pretty much nothing i can do to fix this problem, eh?

[arifsor]

Hi
plz try this

<?
$query = "SELECT * FROM reps_coverage r INNER JOIN country c ON r.cnt_code = c.cnt_code ".
"INNER JOIN state s on r.state_id = s.state_id INNER JOIN user u on r.user_id = ".
"u.user_id WHERE r.state_id = 'NY' AND r.cnt_code = 'us' ORDER BY u.user_name ASC";
//echo $query;
$result = mysql_query($query) or die(mysql_error());
?>
<table cellpadding="0" cellspacing="0" border="1">
<? while($row = mysql_fetch_array($result,MYSQL_BOTH)){?>
<tr>
<td><?=$row['user_name']?></td>
</tr>
<? } ?>
</table>


Arif Sorathia

[LIJI]

you forgot the `s
and you better don't use array values in "s
try
[code]$rep_id=$get_reps_row[rep_id][/code]
before the query and replace it by
[code]"SELECT * FROM reps WHERE rep_id = '$rep_id' ORDER BY `nickname` ASC"[/code]

[majocmatt]

Thank you very much everyone.

Major props to Arif, you saved my day... much appreciation!

[majocmatt]

Just to add a related question:

Assume Joe is related to florida, alabama, georgia and mississippi.

If I search USA and dont choose a specific state, which is possible, and which then searches all states within USA the script will pick up Joe for all 5 states he covers... but then it repeats his name 5 times, one for each state. Is this avoidable?

[michaellunsford]

SELECT DISTINCT rep_id FROM reps WHERE .....

but you'd have to re-query the database again to get the rest of the row...

you could also make a secondary table for coverage, but that opens a whole other can of worms.

maybe a mysql guru would have a better answer.



[!--quoteo(post=364442:date=Apr 13 2006, 09:54 AM:name=majocmatt)--][div class=\'quotetop\']QUOTE(majocmatt @ Apr 13 2006, 09:54 AM) [snapback]364442[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Just to add a related question:

Assume Joe is related to florida, alabama, georgia and mississippi.

If I search USA and dont choose a specific state, which is possible, and which then searches all states within USA the script will pick up Joe for all 5 states he covers... but then it repeats his name 5 times, one for each state. Is this avoidable?
[/quote]

[arifsor]

[!--quoteo(post=364442:date=Apr 13 2006, 09:54 AM:name=majocmatt)--][div class=\'quotetop\']QUOTE(majocmatt @ Apr 13 2006, 09:54 AM) [snapback]364442[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Just to add a related question:

Assume Joe is related to florida, alabama, georgia and mississippi.

If I search USA and dont choose a specific state, which is possible, and which then searches all states within USA the script will pick up Joe for all 5 states he covers... but then it repeats his name 5 times, one for each state. Is this avoidable?
[/quote]


You can use DISTINCT keyword in your query, DISTINCT restrict the repetation of the recordset but it necessary that your record set must be same not even an ID base difference in your record set. That is why you have to write the proper field name in your query other than using "*"

SELECT DISTINCT USER_NAME FROM ..........