Viewed   72 times

I want to be able to fetch results from mysql with a statement like this:

  FROM table 
 WHERE amount > 1000 

But I want to fetch the result constrained to a certain a month and year (based on input from user)... I was trying like this:

  FROM table 
 WHERE amount > 1000 
   AND dateStart = MONTH('$m')   

...$m being a month but it gave error.

In that table, it actually have two dates: startDate and endDate but I am focusing on startDate. The input values would be month and year. How do I phrase the SQL statement that gets the results based on that month of that year?



You were close - got the comparison backwards (assuming startDate is a DATETIME or TIMESTAMP data type):

  FROM table 
 WHERE amount > 1000 
   AND MONTH(dateStart) = {$m}


  • Mind that you are using mysql_escape_string or you risk SQL injection attacks.
  • Function calls on columns means that an index, if one exists, can not be used


Because using functions on columns can't use indexes, a better approach would be to use BETWEEN and the STR_TO_DATE functions:

WHERE startdate BETWEEN STR_TO_DATE([start_date], [format]) 
                    AND STR_TO_DATE([end_date], [format])

See the documentation for formatting syntax.


  • YEAR
Wednesday, December 7, 2022

Use new Date() to generate a new Date object containing the current date and time.

var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();

today = mm + '/' + dd + '/' + yyyy;

This will give you today's date in the format of mm/dd/yyyy.

Simply change today = mm +'/'+ dd +'/'+ yyyy; to whatever format you wish.

Saturday, September 24, 2022

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
    $term = trim($term);
    if (!empty($term)) {
        $searchTermBits[] = "bucketname LIKE '%$term%'";


$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");

this will give you a query like:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.

Thursday, September 1, 2022

Since (around) PHP 5.2, PHP has had a built in class/object for dealing with Dates and Times, called DateTime. In a void, it's always better to use a built-in than to wrangle with the messy details yourself.

The DateTime constructor (or the date_create function) accepts a date in any format understood by strToTime. All you need to know about strToTime is it's magic voodoo that will correctly recognize a date in almost any string format. When I first encountered strToTime I had the same internal reaction you're having now ("that's bullshit/seems unreliable"). It's not. It Just Works in a way that your own fragile understanding of dates never will (and if you think you understand dates, you don't. Trust Me.)

So, pull the information from MySQL as a Date/Time string, and immediately create a PHP date Object. Use the date_format method (with some handy constants) when/if you need the date again as a string.

Monday, November 21, 2022

Monday, November 7, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :