query all rows for each username starting just before first of month

[dadamssg87]

i have a table with a 'created' current_timestamp and a username fields. Can anyone help me write a query that would get the last row that got created before the first of the current month and onward for each username?

 

For instance, for user 123, if they had rows created on the 2011-07-28, 2011-07-30, 2011-08-01, and 2011-08-02 the query would grab the 30th, 1st, and 2nd for user 123.

 

For user 124, if they had rows created on the 2011-05-02, 2011-07-19, 2011-08-01, and 2011-08-05 the query would grab the 19th, 1st, and 5th for user 124.

 

<?php
$month = date("n");
$year = date("Y");

$query = "SELECT * FROM Activity WHERE ???";
?>

[gizmola]

No php required: will get result one username.  There is no query that could do this for all usernames at once, afaik. 

 

(select * from activity WHERE created UNION
(select * from activity WHERE created >= CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', '1') AND username = 'theuser')

 

 

[dadamssg87]

hmm...I'm trying to connect to the database as little as possible so i've been pulling all of the rows in the table and putting them in array and letting php sort through them based on the username. Thought that doing it that way would be better optimized. I'm wondering if something will break if/when there are thousands of rows being put into a single array.

 

Would it be better if i pull all the users into one array and do a foreach through the users and then use the query below foreach user? I wouldn't worry be worrying about the memory being stored into a single array then...id be worrying about bogging the server down with a bunch of database queries.

 

I should mention this script will be a used in a cron job, probably at 2am when there will be little activity on my site.

[gizmola]

Neither situation is ideal.  Using a mysql query per user is more reliable because the basic memory constraint would be the total number of users, and you actually don't need to fetch them all into an array first -- you can fetch a row then issue the aforementioned query.  You do want to make sure you have an index on username, created.