SQL Help !!!

[steph php]

Hi

i am writing an sql statement to search a table of users. in the users table are the fields location1 location2 and location3. when someone searches for 'london' i need it to check each field. which i have done, however, what i really really want is for all the people who have 'london' in location1 to come up 1st then location2 people and so on. (prioritised search results)

all this in one query would be ideal but any help with this would be much appreciated !!!

Cheers guys/gals.

[sde]

i don't think this is possible. my guess is that you would have to use 3 queries and create the sort login in the programming language. (php i'm guessing by your name)

someone please correct me if i'm wrong.

[steph php]

I thought i might have to do that, and yes i am using php.

i'll maybe look at an alternative solution maniplating the results from three seperate queries using php instead.

thanks

[sde]

if you were to use php to do it that way, it might look something like this:

PHP Code:

<?
$contacts = array();

$result = mysql_query("select * from table where location1='london'");
while ($row = mysql_fetch_assoc) {
  $contacts[] = $row;
}

$result = mysql_query("select * from table where location2='london' AND location1 != 'london");
while ($row = mysql_fetch_assoc) {
  $contacts[] = $row;
}

$result = mysql_query("select * from table where location3='london' AND location1 != 'london" AND location2 != 'london');
while ($row = mysql_fetch_assoc) {
  $contacts[] = $row;
}

// then maybe some more logic to check for duplicates
?>

[technobard]

If you normalize the structure of the database, you can do this with a single query. In other words, break out location1,location2,location3.... into its own table. A side benefit is that you can have location4, etc. without changing anything.

TABLE1 (your existing table)
ID_T1 (primary key)
...

TABLE2 (new location table)
ID_T1 (part 1 of primary key - foreign key to TABLE1)
SEQ_NUM (part 2 of primary key)
LOCATION

The query would look something like:
select table1.field1, table1.field2,...table2.location from table1, table2
where table1.id_t1 = table2.id_t1
and table2.location = 'LONDON'
order by table2.seq_num

I don't do a lot of MySQL, so I'm a little rusty on ANSI JOIN syntax. You may have to modify the above to conform to those rules. Not a big deal.

The "order by..." will sort by seq_num which will be 1,2,3,.... so that when 'LONDON' shows up in seq_num = 1 (the same as LOCATION1), it occurs before seq_num = 2, etc. The catch, of course, is that you have to modify your code for adding new entries to handle the second table.