Pdo and MySQL 'Between'

PDO and MySQL 'between'

Don't wrap the values with single quote.

$STH = $DBH->prepare("SELECT * FROM `table` WHERE `start_date` BETWEEN :start_date AND :end_date");

PHP PDO usage with MySQL BETWEEN and INET_ATON()

The issue ended up being with a separate section of code. The values being passed into the SQL query were correct, and the bindValue operation was working successfully.

To anyone who reviews this question in the future, who believes they are having a similar problem, if set up correctly there should be no issues with using the INET_ATON/NTOA() functions with BETWEEN.

I believe the first comment, left by @MarcB, was probably the best answer to this actually, "PDO has absolutely NO knowledge of MySQL functions. as long as the MySQL parser doesn't reject the query string, PDO will happily shove in whatever you want, wherever you want it."

PHP/PDO/MySQL if statement in BETWEEN clause

So you can do it like this:

select
   store_name,
   daily_sales
from
   mytable
where
   date between (select if(open_date > :start_date, open_date, :start_date)) and :end_date

DateTime between dates pdo

use date_sub or INTERVAL

SELECT * FROM money_transfer WHERE send_date 
BETWEEN date_sub(now(),INTERVAL 1 WEEK) and now();

What is the difference between MySQLi and PDO?

PDO is an interface for accessing databases:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server. (source)

MySQLi is an extension for accessing MySQL databases:

The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. (source)

Which one you should use is primarily opinion-based and not particularly well suited for the Stack Overflow format.

Querying DATE in MySQL using PHP PDO

As I suggested in my comment you can use this code:

$date_start = date('Y-m-d', strtotime($start_date));
$date_end = date('Y-m-d', strtotime($date_end));

To convert your variable contents to a format which MySQL needs

Now when you say that you have done it before and it was not working, I assume somewhere between this conversion and binding them to the sql statement you where changing them in your code, so either you can convert them immediately before binding them or as you suggested just convert them in the bind like this:

$this->bind(':start_date', date('Y-m-d', strtotime($start_date)));

And then your query will look like this

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN :start_date
                      AND :end_date";

So to sum everything up, you can use one of these two ways. either of them should work, but you can use any of them you are more comfortable with:

1. Convert the variables before binding them

$date_start = date('Y-m-d', strtotime($start_date));
$date_end = date('Y-m-d', strtotime($date_end));

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN :start_date
                      AND :end_date";
$this->prepare($this->sql);
$this->bind(':id', 0);
$this->bind(':start_date', $date_start);
$this->bind(':end_date', $date_end);
// rest of your code

2. Convert the variables during binding

$this->sql = "SELECT COUNT(id) AS number_of_items FROM item_table
                      WHERE id > :id AND date_visited
                      BETWEEN :start_date
                      AND :end_date";
$this->prepare($this->sql);
$this->bind(':id', 0);
$this->bind(':start_date', date('Y-m-d', strtotime($start_date)));
$this->bind(':end_date', date('Y-m-d', strtotime($date_end)););
// rest of your code

PDO - Getting error while trying to bind values in BETWEEN on mysql

binding is for SINGLE values. you're trying to bind multiple values. You need:

... WHERE foo BETWEEN :start AND :end

and two bind calls.

Remember, a placeholder is basically kind of like a variable, except the DB knows that this "variable" can only represent a single value. it will NOT pick apart the value you pass in and try to make it fit the statement, so

foo BETWEEN :thingie

is no different than

foo BETWEEEN '$var'

as far as number-of-values-being-inserted is concerned.

---

Related Topics