1

I need this query:

SELECT ItemID, Price 
        FROM table
        WHERE ItemID = %d
        GROUP BY Price
        ORDER BY COUNT(*) DESC LIMIT 1

-run once a day, and have the results from it stored in another table with the time stamp of the query.

Is there a way I can automatically query all the available ItemID values? For example, if there are 20 ItemID values available, I need 20 separate queries.

Kreation
  • 327
  • 3
  • 10

1 Answers1

2

You can use the mysql event scheduler for this. Here's an example with your current query inserting data into a table called "new_table", starting at 3 AM on March 28. 

DELIMITER $$

CREATE 
    EVENT `daily_backup` 
    ON SCHEDULE EVERY 1 DAY STARTS '2015-03-28 03:00:00' 
    DO BEGIN

        INSERT INTO new_table (ItemID, Price, Time) 
            SELECT ItemID, Price, NOW()
            FROM table
            WHERE ItemID = %d
            GROUP BY Price
            ORDER BY COUNT(*) DESC LIMIT 1;


    END */$$

You can do the same thing with your other queries; they can be put into the same event, before the END

nomistic
  • 2,902
  • 4
  • 20
  • 36
  • Thanks for the answer, how can I have this query dynamically get all the most commonly occuring values for each ItemID in a given table, and store those values in `new_table`? I would like to avoid creating a new query for each ItemID if possible. – Kreation Mar 29 '15 at 07:36
  • well, your query should pretty much do it for all of the items you have in this table. If you remove the line `WHERE ItemID = %d` it will just backup the entire table. – nomistic Mar 29 '15 at 21:13
  • Would I need to remove anything else? I just want to be sure as, the query as it stands now limits the results to only the top row from the result. I need the most commonly occurring value for each of the ItemIDs stored in this new table. – Kreation Mar 29 '15 at 21:24
  • why don't you test out the query itself and see what rows it returns before you run it regularly? In other words, try everything you want to do between (and not including) the `BEGIN` and the `END`. You could also test out the event to make sure it is running. There are other parameters you can use other than `DAY`. It will accept any of these intervals: `HOUR|MONTH|WEEK|DAY|MINUTE`. Play with it until you get a feel for it. Here's the documentation: https://dev.mysql.com/doc/refman/5.1/en/create-event.html – nomistic Mar 29 '15 at 21:31