group results keeping entries separate

jonki

How could I group the results of a mysql query by the data in one column, but keeping the individual results separate?

Let me explain;

A second hand car website for a country, which outputs the adverts based on a search criteria. I have a cookie which tells me what state/province each user is from, and I want to display their state first, and then the other states afterwards, so that the most relevant results come first.

The only way I can think of doing this is with 2 mysql queries
1. select from xxxx where xxxx and state ='$mystate'
2. select from xxxx where xxxx and state !='$mystate'

And put one after the other.

Something tells me that this is bad programming, and I am sure, if there is a better way then someone here would point me in the right direction.

If the above is the only way to to this; is there a way to combine the results of the two queries, so that my post processing of all the data isn't repeated too? An array perhaps?

Many many thanks

Jon

kakki

maybe this( if thats the right table )

SELECT * FROM xxxx WHERE xxxx AND state ='$mystate'
UNION
SELECT * FROM xxxx WHERE xxxx AND state <>'$mystate'

but that "feels" not good ...
maybe you can use "ORDER BY" to see the best-results

kakki

an another way would be:

SELECT *, IF( state ='$mystate', 1, 0 ) AS _sortcol
FROM xxxx 
WHERE xxxx 
ORDER BY _sortcol

jonki

Very interesting. I have not seen that before.

I will investigate.

Many thanks for your help.

Very much apreciated.

kakki

no problem,
pls mark the thread "resolved"

Sxooter

Note that MySQL's IF isn't portable, but case is. It's pretty easy to do it that way

SELECT *
FROM xxxx
WHERE xxxx
ORDER BY case when state='$mystate' then 0 else 1 end

jonki

Hi guys. Thanks for your help. Still haven't quite got it working.

$result = mysql_query("select *, IF(state LIKE '%.$mystate.%', 1, 0) AS _sortcol, DATE_FORMAT(fecha, \"%d-%m-%Y\") AS mydate from datebase 
					WHERE fecha >= $ultimos_fecha AND
					nivel > '0' 					
					order by _sortcol desc, $orderby desc 
					limit $lim,$numero_anuncios") 	
					or die (mysql_error() );

This gives me no error, but the order by doesn't work, they still come up jumbled,

and this

$result = mysql_query("select *, DATE_FORMAT(fecha, \"%d-%m-%Y\") AS mydate from tenercoche_privado 
					WHERE fecha >= $ultimos_fecha AND
					nivel > '0' 
					order by $orderby desc case when state LIKE '%".$mystate."%' then 0 else 1 end
					limit $lim,$numero_anuncios") 	

				or die (mysql_error() );

The use of LIKE is compulsory in my case, and I also have to order by another parameter selected by the user. (Price, date of ad etc).

Anyone spot the error?

Many thanks.

Jon

jonki

I spotted the missing comma in the second PHP , but it also does not sort out the adverts into states.

jonki

Why is it that after posting something you spot, in 2 minutes, what you didn't spot in an hour?

Something about writing a post that clears your head I think.

This works perfectly:

$result = mysql_query("select *, DATE_FORMAT(fecha, \"%d-%m-%Y\") AS mydate from database 
					WHERE fecha >= $ultimos_fecha AND
					nivel > '0' 
					order by case when state LIKE '%".$mystate."%' then 0 else 1 end, $orderby desc 
					limit $lim,$numero_anuncios") 	

				or die (mysql_error() );

The only things is that I do get a bit of mixing after a while, but I am guessing that this is to do with the way the two order by statements interact.

Many thanks for both of your helps.