Asked  2 Years ago    Answers:  5   Viewed   66 times

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

SELECT * 
  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:

SELECT * 
  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?

 Answers

2

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

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

Caveats:


  • 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

Alternatives:


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.

Reference:


  • MONTH
  • YEAR
  • BETWEEN
  • STR_TO_DATE
Wednesday, December 7, 2022
 
4

For some reason, changing the first line of the query:

SELECT SQL_CALC_FOUND_ROWS p.*

To:

SELECT SQL_CALC_FOUND_ROWS p.id

And then looping through the results getting the data I needed from each row in separate queries got the result I was after.

Friday, December 23, 2022
 
4

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;
document.write(today);

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
1

mysqli_stmt::bind_param can only bind a specific variable, not an expression. The supplied variable is passed to 'bind' by reference, and not by value, meaning that the underlying SQL gets whatever value that variable has at the time the command is executed, not when it's bound.

Use:

WHERE field LIKE CONCAT('%', ?, '%")

or do:

$brand = '%' . $brand . '%'

immediately before the command is executed.

What you can't do is:

WHERE field LIKE '%?%

because the ? bound variable must correspond to a single string or numeric value, not to a substring (or field name).

EDIT in this case, your real problem appears to be mixing up prepared statements (as supported by mysqli::prepare and mysqli_stmt::execute()) with plain old queries (as done with mysqli::query()). You should also just ask for the number of rows directly from the DB server, rather than pull the data and use num_rows:

$countQuery = "SELECT COUNT(ARTICLE_NO) FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s",  $brand);
    $numRecords->execute();
    $numRecords->bind_result($num_rows);
    $numRecords->fetch();
    $numRecords->free_result();
    $numRecords->close();
    $last = ceil($rowcount/$page_rows);
} else {
    print_r($con->error);
}
Thursday, October 13, 2022
 
2

You get the selected value from the SelectedDate property of the DatePicker.

<DatePicker x:Name="dp" />

DateTime? selectedDate = dp.SelectedDate;
if(selectedDate.HasValue)
{
    string formatted = selectedDate.Value.ToString("dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture);
}
Thursday, September 15, 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 :
 

Browse Other Code Languages