Welcome Guest, Not a member yet? Register   Sign In
help with mysql query

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?


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

8 week not weeks

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

Thanks guys, yes I just realized it was a silly mistake too. Tongue

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

hello all,

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

something like this might work
$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";

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

Theme © iAndrew 2016 - Forum software by © MyBB