[SOLVED] Order By Clause not working

[tpra21]

I cannot get the ORDER BY clause in the following query to work:

 

SELECT count(*) FROM ads, school WHERE ads.avg_rent >=0 AND ads.avg_rent <=99999999 AND ads.university=school.school_ID AND ads.university =1728 ORDER BY ads.expirey, ads.expirem, ads.expired DESC

 

With the above query, I tried including the ORDER BY fields in the selection with the count(*), but then my query returned no results.

 

The query itself returns the correct rows, but it doesn't order them. Also, is it possible to rearrange a field in the table to place in the ORDER BY clause? For example, I want to order the results by an ad's posted date, but it is in mm-dd-yyyy format and is a text field. Is there any way for me to re-format the text field into ISO format and then use that format in the order by clause?

 

Thanks,

 

[artacus]

This query doesn't make sense.

Without a GROUP BY clause all you would get is the count for the number of records that matched.

[tpra21]

That is what it is doing. I didn't write the code, but from what I have seen it counts all the records, and then uses a list() function to read through the result records. The code follows:

 

       $maxQR = mysql_db_query($db_name, $query, $connection);

        if(!$maxQR){
        // die - error here
        }

        list($d_max)= mysql_fetch_row($maxQR);
        if($d_max > 0){
          // if rows, list them here
        }

[artacus]

Lets leave PHP out of this for a moment and just look at your results by running the query in phpMyAdmin, MySQL Query Browser or similar.

 

You'll find there is nothing to order by or iterate thru with list.

SELECT COUNT(*) AS cnt 
FROM ads
JOIN school AS s ON ads.university=s.school_ID

 

Will return something like this:

cnt

-----

324

 

Not very interesting. Try something along these lines:

SELECT s.*, count(ads.ID) AS cnt
FROM ads
JOIN school AS s ON ads.university=school.school_ID 
WHERE ads.avg_rent BETWEEN 0 AND 99999999 
GROUP BY s.school_ID ORDER BY cnt DESC

[tpra21]

cannot get it to work. I will keep trying, but right now it isn't giving me an erro, but rather not returning any results.

[tpra21]

Found the problem, silly me. There was an existing order clause in the query tucked away out of site.