Showing everything from the database from the current month.

Napster

I have a script that shows the top 10 games on my site that have been added in the last 30 days. The mysql query goes:


$tmonth = time() - 60 * 60 * 24 * 30;

$top102 = mysql_query("SELECT * FROM games WHERE whenadded >= $tmonth ORDER BY rating DESC,name LIMIT 10") or die(mysql_error());

Now this is fine, but I want it to show the top games added from this month, like from March 1st to March 31st. How can I do this? The field 'whenadded' is a timestamp.

Thanks in advance.

Weedpacket

I don't know what functionality MySQL provides for converting Unix timestamps into its own date/time type (if it has any, you could use it and MySQL's other date/time functions for selecting by month). In the absence of such functionality you'd probably end up having to suck the whole lot into PHP and testing the timestamps there.

Napster

Would something like this work?

$top102 = mysql_query("SELECT * FROM games WHERE date("mY",whenadded) = " . date("my", time()) . "  ORDER BY rating DESC,name LIMIT 10") or die(mysql_error());

SD-Syndicate

Well you could try this if your using a timestamp

$time = time("U");

$top102 = mysql_query("SELECT * FROM games WHERE $time-whenadded <=2629743 ORDER BY rating DESC,name LIMIT 10") or die(mysql_error());

Hope it helps 🙂

EDIT: Ah thats actually for the last 30 days, Ill get back to you on this o_0

tekky

//sloppy pseudo/real code
$start = strtotime("01 $curmonth $curyear")
$end = strtotime(date("t", $start) . " $curmonth $curyear")
//I dont really need to code anymore...

Just use those 2 timestamps in a BETWEEN statement in MySQL now 😉

Napster

Ok thank you, now I just find the values of $curmonth and $curyear? Now for the SQL query do I just go:

$var = mysql_query("SELECT * FROM games WHERE whenadded >= '$start' AND whenadded <= '$end'  ORDER BY rating DESC,name LIMIT 10") or die(mysql_error());

Is this correct?

Napster

I'm currently going:


  $start = mktime(0, 0, 0, date("m"), 1, date("Y"));

  $end = mktime(23, 59, 59, date("m"), date("t"), date("Y"));

  ...

  $top102 = mysql_query("SELECT * FROM games WHERE whenadded >= '$start' AND whenadded <= '$end' ORDER BY rating DESC,name LIMIT 10") or die(mysql_error());

  ...

Nothing is showing up in the code the is below this in the while loop. I added something in the database today so it should be showing something.

tekky

I actually meant for you to use the BETWEEN statement 😉


  $start = mktime(0, 0, 0, date("m"), 1, date("Y"));

  $end = mktime(23, 59, 59, date("m"), date("t"), date("Y"));

  ...

  $sql = "SELECT * FROM games WHERE whenadded BETWEEN '$start' AND '$end' ORDER BY rating DESC,name LIMIT 10";
  echo 'SQL = ' . $sql;
  $top102 = mysql_query($sql) or die(mysql_error());

  ...

is your database entry inside of March?

setup an echo statement like I did to see if your query is being completed like you expect... do you get a mysql_error at all?