• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
help with mysql query

#1
[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)

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

#3
[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

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

#5
[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?

#6
[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. ?

#7
[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";
}

#8
[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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.