Page 1 of 1

Having trouble selecting rows with specific date information

Posted: Tue Feb 22, 2005 5:46 pm
by CUclimber
I'm sort of new at this, so please bear with me here.

I'm trying to construct a mySQL query that selects a few bits of information from some specific rows in a table (heh, is that generic enough for ya?). The query looks like this right now:

Code: Select all

$query_certs_per_rep = "select sum(price) from certs where rep_id=$current_rep_id";
$result_certs_per_rep = mysql_query($query_certs_per_rep)
or exit(mysql_error());
Also in those rows is a column with date information in the yyyy-mm-dd format. I want to add to my where part of the query to get it to only select rows where the date falls within the current month of the year. How on Earth do I do this?

Posted: Tue Feb 22, 2005 6:18 pm
by feyd

Code: Select all

SELECT
  SUM(`price`)
FROM
  `certs`
WHERE
  `rep_id` = '$current_rep_id'
  AND
  EXTRACT(YEAR_MONTH FROM `date_field`) = EXTRACT(YEAR_MONTH FROM NOW())
GROUP BY
  `rep_id`

http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html

Posted: Tue Feb 22, 2005 6:39 pm
by CUclimber
Outstanding. Thank you VERY much! It was the EXTRACT part that was throwing me off.