sort order

fifeclub posted this at 16:17 — 9th October 2002.

Joined: Feb 2001

sorry for filling up this forum with all my questions. I'm just a the very beginning of both php and mySQL, but I just learned how to add ORDER BY.

<?php
SELECT * FROM alumni2 ORDER BY lastname, firstname\",$db);
?>

My problem is that if there are blank entries in the column being ordered, then those rows with the blanks always come up on top. My question is how do I keep the desired sorting order (ascending or descending) but somehow tell it "if field is blank then put at bottom" (or maybe to not list at all)?

Thanks

Mark Hensler posted this at 17:47 — 9th October 2002.

He has: 4,048 posts

Joined: Aug 2000

You'll have to run two queries. The first query will omit the blank entries (with a WHERE clause), and the optional second query would retrieve the blank entries.

Mark Hensler
If there is no answer on Google, then there is no question.

mairving posted this at 17:52 — 9th October 2002.

They have: 2,256 posts

Joined: Feb 2001

Try

<?php
SELECT * from alumni2 
WHERE lastname <> 'NULL'
ORDER BY lastname, firstname;
?>

You really have a couple of options. You can try using something like WHERE lastname > '0', which works okay some of the time. I tend to prefer to set a default value 'NULL' for the field so that I can exclude by WHERE lastname <> 'NULL'. I guess the other question would be why do you have fields like lastname, firstname that are blank.

You could also make a conditional statement with PHP once you have all of your data.

Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states

fifeclub posted this at 04:39 — 10th October 2002.

He has: 688 posts

Joined: Feb 2001

Here's the experimental stuff I'm teaching myself with (although the actual data is real)

pstvalumni.com/directory/display2.php

None of the lastname or firstname fields are blank but a lot of the additonal columns contain blank fields.

Thanks for pointing me in the right direction. I'll try one of these suggestions next time I work on it.

Peter J. Boettcher posted this at 00:45 — 11th October 2002.

They have: 812 posts

Joined: Feb 2000

Mike,

You might want to start experimenting with paging, once you start getting too much data in your database your scripts will start timing out or take too long to execute.

PJ | Are we there yet?
pjboettcher.com