Get all date within a month

__berfuzz

I have a query where I wanna SELECT all rows with a special month in a date-column. The date in the sql-table is date('Y-m-d').

I don't know how to get it to work.

$month = $year.'-'.$month;  //$month and $year is set by <a href...>
echo $month;     //seems to echo ok, ex. 2007-11
$sql = mysql_query("SELECT * FROM resultat WHERE date LIKE '$month%' AND user = $user AND kontroll = 'ok!'") or die(mysql_error("Couldn't reach table'"));

I've been trying to use date LIKE $month% to get all dates with YYYY-mm without and success.

NogDog

Assuming that the date column is a DATE, DATETIME, or TIMESTAMP type column in your database table:

. . . WHERE DATE_FORMAT(date, '%Y-%m') = '$month' . . .

bradgrafelman

It appears as though you're vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a SQL query! Instead, sanitize it with a function such as [man]mysql_real_escape_string/man.

__berfuzz

NogDog wrote:
Assuming that the date column is a DATE, DATETIME, or TIMESTAMP type column in your database table:
. . . WHERE DATE_FORMAT(date, '%Y-%m') = '$month' . . .

$month = $year.'-'.$month;
echo $month;
$result = array();
$sql = mysql_query("SELECT * FROM resultat WHERE DATE_FORMAT(date, '%Y-%m') = '$month' AND user = $user AND kontroll = 'ok!'") or die(mysql_error("Det gick inte att nå tabellen 'reslutat'"));
while($resultat = mysql_fetch_array($sql)){
$date = $resultat['date'];
echo $date;  //echo nothing!?
    $day = date('w', strtotime($date)); 
    $week = date('W', strtotime($date));
    $result[$week][$day]++;
}

print_r($result);  //echos an empty array..?

It doesn't work. There are rows with dates that should have been selected from the sql. Does it matter that the date-column is of type date in the sql?

bradgrafelman> I don't know, all querys are behind a loggin, with a simple protection. It might even be mysql_real_escape_string(). ;-) Thanx for the tip.

bradgrafelman

So you didn't get any error message (e.g. from the "or die()" part) and but no rows were returned?

Try storing the query in a string and echo'ing it out before sending the query - visually inspect it and see if it makes sense. If you can't find anything, post it here for us to see.

__berfuzz

bradgrafelman wrote:
So you didn't get any error message (e.g. from the "or die()" part) and but no rows were returned?

Try storing the query in a string and echo'ing it out before sending the query - visually inspect it and see if it makes sense. If you can't find anything, post it here for us to see.

I'm not sure of what you want to see, but echo '$sql'; echos Resource id #4.

bradgrafelman

I meant you should first store the query in a string and echo that string out, e.g.

$query = 'SELECT * FROM foo..';
echo "Query: $query";
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error());

__berfuzz

query: SELECT * FROM resultat WHERE DATE_FORMAT(date, '%Y-%m') = '1995' AND user = 1002 AND kontroll = 'ok!'

NogDog

You have only supplied the year '1995' instead of a 'yyyy-mm' value for the comparison. Check which variable you are using to supply that value and that it has been properly defined in your code.

__berfuzz

Oh, my bad.

Now I get:
query: SELECT * FROM resultat WHERE DATE_FORMAT(date, '%Y-%m') = '2007-12' AND user = 1002 AND kontroll = 'ok!'
But still no rows from the sql.

Is there something wrong with: DATE_FORMAT(date, '%Y-%m')

Edit: Is there an other way to form date_format to get all day within a month if date is of type date in the sql?

bradgrafelman

Can you show us the structure of your table (ex. SHOW CREATE TABLE resultat;) as well as the result from a SELECT * FROM resultat WHERE user = 1002 ?

I ran the same query you pasted on my MySQL server and the correct row was SELECT'ed (the date column was of type DATE and had a value of '2007-12-24').

__berfuzz

print_r($result); gives: Array ( [01] => Array ( [4] => 91 ) ) with the query you wanted to see. How do I echo SHOW CREATE TABLE resultat; ???

__berfuzz

bradgrafelman wrote:
Can you show us the structure of your table (ex. SHOW CREATE TABLE resultat;)

SHOW CREATE TABLE resultat; gives: CREATE TABLE resultat (\n id int(11) NOT NULL auto_increment,\n user int(6) NOT NULL,\n telenr varchar(30) NOT NULL,\n klient varchar(30) NOT NULL,\n produkt varchar(50) NOT NULL,\n kontroll varchar(50) default NULL,\n bortfall varchar(50) NOT NULL,\n date date NOT NULL,\n PRIMARY KEY (id)\n) ENGINE=InnoDB AUTO_INCREMENT=164 DEFAULT CHARSET=latin1