displaying results

gljones

Hi,

I'm able to query a table and display the results in a particular order, e.g. by City:

$query = "SELECT * FROM table where state = '$state' ORDER BY city asc";

i want to order by whether a certain field has something in it, so that i can order the returned list by those that have an image in the image field and also by 'city'.

thanks for any help.

okuto1973

Originally posted by gljones
Hi,

I'm able to query a table and display the results in a particular order, e.g. by City:

$query = "SELECT * FROM table where state = '$state' ORDER BY city asc";

i want to order by whether a certain field has something in it, so that i can order the returned list by those that have an image in the image field and also by 'city'.

thanks for any help.

$query = "SELECT * FROM table where state = '$state' and imagefield=1 ORDER BY city asc";

(These query give you records that have he field 'imagefield' to 1 ordered by city)

$query = "SELECT * FROM table where state = '$state' ORDER BY imagefield,city asc";

(These query order all records first in imagefield then in city field!)

ID|CITY|IMFIELD

1|aaaaa|1
2|aaaab|2
3|aaaac|1

You will have these sequence: 1,3,2

gljones

would it be possible to query where a field is not empty? something like

$query = "SELECT * FROM table where state = '$state' and imagefield!='' ORDER BY city asc";

where imagefield is ordered by whether or not it has data in it,
[fields w/ data would show first]?

i'm using a field called 'photo' that has the actual name of the image in it. it has text not numerical data in it.

thanks again.

gljones

I just tried the code sample you posted.

i was unable to use:

$query = "SELECT * FROM table where state = '$state' and imagefield=1 ORDER BY city asc";

because it has the 'and imagefield=1' in the query. i would need it to pull all rows whether or not imagefield=1 or anything else.

also,

i tried:

$query = "SELECT * FROM table where state = '$state' ORDER BY imagefield,city asc";

made the field in the table imagefield, then set some rows =1. however it made no difference to the order of the rows displayed. they are still ordered by 'city'

thanks.

okuto1973

To check if a field is empty or not use:

where field IS NOT NULL

where field IS NULL

Post how you have created the db so i can tell you which query do u need to obtain the correct records!