Asked  7 Months ago    Answers:  5   Viewed   37 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

81

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, March 31, 2021
 
Gregosaurus
answered 7 Months ago
94

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.

Saturday, May 29, 2021
 
linjuming
answered 5 Months ago
41

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.

Tuesday, June 1, 2021
 
yosemite
answered 5 Months ago
74

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);
}
Saturday, July 31, 2021
 
Abdul Rehman
answered 3 Months ago
36

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);
}
Wednesday, August 4, 2021
 
Lawrence Taur
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :