help with mysql query

[eluser]megabyte[/eluser]
Trying to create a table that shows the number of users per week for the past 8 weeks.

This is going to get turned into xml for flash charts.

Here's the query, but its throwing errors.

Can anyone maybe point out where I may be going wrong?

Thanks

Code:

SELECT count(users) as user_count FROM (users) WHERE DATE_SUB(CURDATE(),INTERVAL 8 WEEKS) <= created GROUP BY week(created, 0)

[eluser]bgreene[/eluser]
8 week not weeks

[eluser]davidbehler[/eluser]
bgreene is right,
the MySQL DATE_SUB function takes the same arguments as the DATE_ADD function. See the manuel for valid values for the unit parameter: http://dev.mysql.com/doc/refman/5.1/en/d...n_date-add

[eluser]megabyte[/eluser]
Thanks guys, yes I just realized it was a silly mistake too.

[eluser]megabyte[/eluser]
if i had a date range, how would I use mysql to print out the week numbers for all weeks in that range?

[eluser]ajay009ajay[/eluser]
hello all,

Where will i write query in codeIgnetaor.Or can u know me where query is write generally in codeIgnetor project. ?

[eluser]davidbehler[/eluser]
something like this might work

Code:

$result = $this->db->query("select date_format(date_from_column, '%u') as first_week, date_format(date_to_column, '%u') last_week from table");
$row = $result->row_array();
for($i = $row['first_week']; $i <= $row['last_week']; $i++)
{
   echo "This is week $i";
}

[eluser]megabyte[/eluser]
Thanks waldmeister that should work. I'm sorta just wondering how far mysql can be pushed.

For example, execute a query that produces a table of weeks.

So psuedo code would be:

select current date - interval - 8 week group by week.

then you'd get a result set with 8 rows returned.

something like:

week 8
week 7
week 6
week 5
week 4
week 3
week 2
week 1