Correct in phpMyAdmin but error in php: mysql_num_rows supplied argument is not a val

[daphilip]

Hi,

I created a query which runs perfectly in phpMyAdmin, but when in the php page it gives the "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource..."

My Query is:
SET @row =100;
SELECT STRAIGHT_JOIN ipod2000_muscol_albums.name, ipod2000_muscol_artists.artist_name, ipod2000_muscol_albums.year, SUM( ipod2000_xml_list.list_played ) , ipod2000_muscol_albums.image, ipod2000_muscol_albums.buy_link, @row := @row +1 AS row_number
FROM (
ipod2000_xml_list
INNER JOIN ipod2000_muscol_albums ON list_keywords = ipod2000_muscol_albums.keywords
)
INNER JOIN ipod2000_muscol_artists ON artist_name = list_artist
GROUP BY list_keywords
ORDER BY SUM( list_played ) DESC
LIMIT 100 , 100

The query gets a list of 100 records ordered by number of times played and it adds a ranking (first row: 101, second row: 102, and so on) to it.

The PHP code is:
$result = mysql_query("SET @row =100;
SELECT STRAIGHT_JOIN ipod2000_muscol_albums.name, ipod2000_muscol_artists.artist_name, ipod2000_muscol_albums.year, SUM( ipod2000_xml_list.list_played ) , ipod2000_muscol_albums.image, ipod2000_muscol_albums.buy_link, ipod2000_muscol_artists.letter, @row := @row +1 AS row_number
FROM (
ipod2000_xml_list
INNER JOIN ipod2000_muscol_albums ON list_keywords = ipod2000_muscol_albums.keywords
)
INNER JOIN ipod2000_muscol_artists ON artist_name = list_artist
GROUP BY list_keywords
ORDER BY SUM( list_played ) DESC
LIMIT 100 , 100");

while ($row=mysql_fetch_row($result))
{
print "...

Question:
Isn't it possible to integrate the row numbering (@row := @row +1 AS row_number) in a query on a table?. Is there an other solution?

Many thanks in advance!

Dave

[Bjom]

mysql_query can't take 2 sql statements in one go. That's why you get the "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource..." - which tells you that no result was returned.

what do you put that straight_join operator in there for, not happy with the optimizer?

there is no way that I know of to include row numbering in a complex query like this (since you would need to number in dependence of the sum(list_played) which in turn won't work).

It will work however if you make a view (let's call it vtest) out of the query above and include it in the database, then query it like below. Another assumption is that you actually give the SUM(list_played) field a name (in this case timesPlayed

select t1.*, (SELECT COUNT(`timesPlayed`) from vtest as t2 where t2.`timesPlayed` >= t1.`timesPlayed`) as row_number from vtest as t1

[daphilip]

Bjom,

Thanks for your reply. I understand your solution of making a view, but that is a bit complicated for what I want. My website makes different music charts: most played: song, album, artist, on gender, on age and on genre. And thus all different list views.

I have to find an other solution.

Thanks again!

Dave

[Bjom]

That's all I can come up with in regards to SQL.

However, you can easily do that in php, for example at the time when you prepare the html for displaying the list (you'll be looping through your results anyway? just include a counter i++). If you want to do it before that - there are plenty of array functions to play around with.

Bjom

[daphilip]

Bjom,

Vielen Dank!
The loop did the trick. A simple solution that I was not thinking of (due to my limited development experience!)

It is working perfectly now!
See http://www.ipod2000.com for the music charts

Thanks again!!!

Dave