WHERE date='Nov. 30, 2007' equals error?

[br3nn4n]

I'm trying to be able to make an archives section on my site, and it's for a monthly newspaper publication.

 

So, for each month, every article has the same date (date the paper is put out). Thus, for the archives, I would like to be able to do something like this:

 

Archives:

September | November | December

 

and for each one call from the database ONLY the articles from with the specific date associated with that month.

 

When I enter in the articles for the month November, I set the date to "Nov. 30, 2007" but when I try to call only articles from that date, MySQL outputs an error. Here's the general query idea:

 

SELECT * FROM news WHERE date = 'Nov. 30, 2007'

 

What am I doing wrong?

 

Thanks!

[PFMaBiSmAd]

Posting the error you get would help to find what the problem is.

[Gamic]

If you are storing the date in a date data type, then the date should be in the form of "yyyy-mm-dd". This means that Nov. 30, 2007 would be 2007-11-30.

 

If you are using php to do the search you could do something like this:

 

<?php
  $strDate="Nov. 30, 2007";
  if($dbDate=strtodate($strDate)){
    $dbDate=date('Y-m-d',$dbDate);
    //search by date $sql="SELECT * FROM news WHERE date = '$dbDate' ;";
  }else{
    echo "Date was not entered in a format that the function strtodate can understand";
  }
?>

 

This would then give you the option of entering the dates a number of different formats.

 

This all, however, assumes that your dates are stored as the mySql date format.

[br3nn4n]

Okay, I think I get what you're saying...right now, for each article (row), one of the cells is named "date" and has the date in this format: Nov. 30, 2007

 

The field is a TEXT field, are you suggesting I make it a DATE field?

 

From there...sorry, what would I do? I'm still really learning PHP and some of the stuff (such as your example) just doesn't make sense 

 

A couple questions about your code though. 1) is strtodate a built in PHP function? 2) in the following code are you simply fulfilling an optional value in the date() function where you put the "$dbDate"?

<?php
$dbDate=date('Y-m-d',$dbDate);
?>

 

 

[wrong_move18]

I think this may help you.

 

<?php
$dbdate  = 'Nov. 30, 2007';
$qdate = date("Y-m-d", strtotime($dbdate));
$query = "SELECT * FROM news WHERE date = '{$qdate}'";
?>

 

strtotime is a PHP built function, it will convert any format of date in a string to a timestamp.

[wrong_move18]

When I enter in the articles for the month November, I set the date to "Nov. 30, 2007" but when I try to call only articles from that date, MySQL outputs an error.

 

I think you wanted to view all articles for Nov. 2007, you can use this query.

<?php
$dbdate = "Nov. 2007";
$month = date("m", strtotime($dbdate));
$year = date("Y", strtotime($dbdate));

$query = "SELECT * FROM news WHERE MONTH(date)={$month} AND YEAR(date)={$year}"; 
?>

[Gamic]

Ah yes, I'm  sorry. I was assuming that your date was stored in the date format. If it is stored in text I am not sure how to help. I would suggest that all dates be in the date format (although there might be very good reasons to not have dates stored in this way).

 

It also did not help that I used strtodate instead of strtotime (strtodate does not exist, but strtotime does--as an aside, I'm sure strtodate existed at one point, which is why I used it).

 

$timeStamp=strtotime($dateString) takes the contents of $dateString and either returns a php/unix style timestamp or false.

Then date($formatString,$timeStamp) returns a string that represents your date stored in $timeStamp in any format that specify in $formatString. (A quick read of the manual may help your understanding of this one ).

 

I've repeated the code here with slightly different variable names, and comments that try to explain what each line is doing.

<?php
  $strDate="Nov. 30, 2007";//the date entered as a string format.

  //if the date entered can be converted to a timestamp
  if($dbDate=strtotime($strDate)){
    //create a string from that timestamp that represents the date in mySQL date format
    $mySQLdate=date('Y-m-d',$dbDate);
    //search by date $sql="SELECT * FROM news WHERE date = '$mySQLdate' ;"; (or, where I would put my search)
  }else{
    //otherwise the date entered could not be converted to a time stamp.
    echo "Date was not entered in a format that the function strtodate can understand";
  }
?>

If you have any further questions about this then keep asking, this is the place to do it