Counting Rows Incrementally using a query via PHP

[jdubwelch]

In phpMyAdmin my query works just fine, however when I paste it into my php code it doesn't work because I don't think I can set user defined variable in MySQL from a PHP query.

 

There's got to be a way to do this.  Can anyone help?

 

Here's my MySQL query:

set @rank = 0;
SELECT FLOOR( (h.clicked / ( (UNIX_TIMESTAMP( ) - h.date_posted ) * 0.000277777778 )) *24) AS clicksPerHour, 
h.headline_id as id, 
@rank := ( @rank +1 ) AS rank
FROM headlines h
WHERE h.date_posted >= ( UNIX_TIMESTAMP( DATE_SUB( NOW( ) , INTERVAL 2 DAY ) ) ) 
ORDER BY  `clicksPerHour` DESC 
LIMIT 0 , 20

 

 

[br0ken]

I think you need to write a stored procedure or user-defined function to accomplish this but that's just a guess.

[jdubwelch]

How does one do that?

[br0ken]

By searching Google and doing some reading/learning 

[fenway]

By searching Google and doing some reading/learning 

Not a particular helpful comment, I suppose.

 

You have to issue your SET statement separately from your SELECT statement.

 

Alternatively, you can use a CASE statement to declare your variable in-line to save the 2nd statement.