Having trouble selecting rows with specific date information

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CUclimber
Forum Newbie
Posts: 2
Joined: Tue Feb 22, 2005 5:40 pm

Having trouble selecting rows with specific date information

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
CUclimber
Forum Newbie
Posts: 2
Joined: Tue Feb 22, 2005 5:40 pm

Post by CUclimber »

Outstanding. Thank you VERY much! It was the EXTRACT part that was throwing me off.
Post Reply