Query with previous results

[SaRiD]

I am running a script each month that scores a webpage out of 30.
After each month I want to be able to display the current results compared with last months results. I have this currently working at the moment by running a query on this months, and then another individual query for each row in my PHP code. This was fine until I got more results and found that it slowed down dramatically.

So at the moment it runs the main query and 20 other small queries for example. Is there a way to combine the 20 other small queries in the main query to speed things up.

So the results would bring back something like

| website | score | last month score |
---------------------------------------------
|

http://www.google.com

| 22 | 18 |

So last month's score is in the same table, but the previous report - not necessarily the previous record as there are a few campaigns running.

Make sense???

 

[tektipsismyfavorite]

I'm assuming your data is stored:

website_id
website
score
month_or_date

Is it that you need to find last month's score based on it being the most recent score in the table (besides the current month)?

You could use SELECT DISTINCT for that and sort it by date, only if your date field is stored in a manner that can be sorted properly.

Another way you could do it is add a column to your table for: last_month. Use 0, 1 or 2, 0 being that it's old, 1 being that it was last month's score, and 2 being this months score

When you add your scores for the new month, just change all the 2s to 1s, the 1s to 0s, and when inserting a row, make that column value 2.