listing question

------=_NextPart_000_0007_01C65D72.9D57A6E0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi there everyone,

I have a little problem, I could do this with 2 seperate queries but if I
can do it with 1 then even better ;-)

I have to list items in numeric order IF the field isn't empty (ie: 0 comes
at the top, followed by 1 etc ....) and that isn't an issue as PHP with
MySQL makes that very easy - BUT here's the problem i'm having. Once it's
gone through the list, any items that do NOT have a number value are tacked
on to the end in no particular order, is it possible that those items that
do NOT have any value in the numeric field can be listed in alphabetical
order instead? So if they have 0 upwards they displays from the top, then
the ones that do not have ANYTHING in that field would display after but
alphabetically instead?

I'm kind of lost on how to do this with a single query.

Thanks everyone

Chris

------=_NextPart_000_0007_01C65D72.9D57A6E0--

RE: listing question

Hi there,

I tried something similar:

$query = "SELECT * FROM videos WHERE videomakers_website_url = '$x' AND
privatepublic = 'public' AND producer_website LIKE '%$x%' OR
videomakers_website_url LIKE '%$x%' ORDER BY videomakers_site_position ASC,
video_title ASC LIMIT $offset, $item_perpage";
$querytotal = "SELECT count(*) FROM videos WHERE videomakers_website_url =
'$x' AND privatepublic = 'public' AND producer_website LIKE '%$x%' OR
videomakers_website_url LIKE '%$x%' ORDER BY videomakers_site_position ASC,
video_title ASC";

My problem is, it displays not alphabetical videos first and then LAST it
displays the videos with numbers, and when I can get the numbers at the top
of the listing, they are in the wrong order (IE: 0,1,2 is from the bottom up
and not 0 being at the top, 1 being next and so on).

Chris

Is there something stopping you from specifying two columns to order results
by, ie:

select myNumeric , myText
from myTable
order by myNumeric asc, myText asc



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.1/308 - Release Date: 4/11/2006

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: listing question

Chris Payne wrote:
> Hi there,
>
> I tried something similar:
>
> $query = "SELECT * FROM videos WHERE videomakers_website_url = '$x' AND
> privatepublic = 'public' AND producer_website LIKE '%$x%' OR
> videomakers_website_url LIKE '%$x%' ORDER BY videomakers_site_position ASC,
> video_title ASC LIMIT $offset, $item_perpage";
> $querytotal = "SELECT count(*) FROM videos WHERE videomakers_website_url =
> '$x' AND privatepublic = 'public' AND producer_website LIKE '%$x%' OR
> videomakers_website_url LIKE '%$x%' ORDER BY videomakers_site_position ASC,
> video_title ASC";
>
> My problem is, it displays not alphabetical videos first and then LAST it
> displays the videos with numbers, and when I can get the numbers at the top
> of the listing, they are in the wrong order (IE: 0,1,2 is from the bottom up
> and not 0 being at the top, 1 being next and so on).

I don't understand what you want (maybe a sample 5 in the order you get
and the order you want will help us - include the position field if you
send it through).

Do you want alphabetical first then numbers?

change your order:
order by video_title asc, videomakers_site_position asc;


if you want numbers first:
order by videomakers_site_position asc, video_title asc;


Lastly, you don't need to order your results for a count query
($querytotal) :)

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php