help with mysql query - Printable Version

help with mysql query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: help with mysql query (/showthread.php?tid=21802)

help with mysql query - El Forum - 08-20-2009

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

help with mysql query - El Forum - 08-21-2009

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

help with mysql query - El Forum - 08-21-2009

[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/date-and-time-functions.html#function_date-add

help with mysql query - El Forum - 08-21-2009

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

help with mysql query - El Forum - 08-21-2009

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

help with mysql query - El Forum - 08-21-2009

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

help with mysql query - El Forum - 08-21-2009

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

}

help with mysql query - El Forum - 08-21-2009

[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