I am completely new to utilizing php and pdos. A close associate of mine has actually put together a pdo class along with a few examples detailing how it can be employed, and so far, everything has been running smoothly for me. However, I have encountered an issue while attempting to perform a query that involves the utilization of the BETWEEN mysql keyword. Unfortunately, this particular query ends up returning no results at all. After setting up the mysql_query.log file, my analysis suggests that the query is successfully prepared but not executed as expected. Before delving into the specifics outlined in the log, allow me to share my code snippet:
$newSlot = array(
"fromDate" => $db->mysql_escape_mimic($startDate->format('Y-m-d H:i:s')),
"untilDate" => $db->mysql_escape_mimic($endDate->format('Y-m-d H:i:s'))
);
$query = "SELECT * FROM schedule_slot WHERE (startDate BETWEEN :fromDate AND :untilDate) OR (endDate BETWEEN :fromDate AND :untilDate);";
$result = $db->prepare($query);
$slot = null;
if($result == 1) {
$result = $db->execute($newSlot);
if($result == 1) {
$slot = $db->fetch();
}
}
print "slot: " . $slot["startDate"];
Here's a portion of the relevant log data (slightly tidied up):
161010 20:59:31
2 Connect root@localhost as anonymous on test
2 Prepare SELECT * FROM schedule_slot WHERE (startDate BETWEEN ? AND ?) OR (endDate BETWEEN ? AND ?)
2 Close stmt
2 Quit
Additionally, here is an instance from the log demonstrating a query that executed without any issues:
161010 21:01:07
3 Connect root@localhost as anonymous on test
3 Prepare INSERT INTO schedule_slot(startDate, endDate) VALUES(?,?)
161010 21:01:08
3 Execute INSERT INTO schedule_slot(startDate, endDate) VALUES('2016-10-11 13:35:00','2016-10-11 14:35:00')
3 Close stmt
3 Quit
Please let me know if you would like me to amend the pdo code or make any other types of adjustments. As far as I am aware, the pdo class adheres to the standard conventions. Kindly inform me why my query fails to yield any outcomes.
Note: Below is the pdo class found in the dbpdo.php file:
<?php
class dbpdo {
private $_connection;
private $_statement;
public function __construct( ) {}
public function connect( $host, $username, $password, $database ) {
$connect = 'mysql:host='.$host.';dbname='.$database.';charset=utf8mb4';
$this->_connection = new PDO($connect, $username, $password);
$this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->_connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
public function __destruct() {
if ($this->_connection)
$this->_connection = null;
}
public function query($query){
try {
return $this->_connection->query($query);
} catch(PDOException $e) {
return "Error: " . $e->getMessage();
}
}
public function fetch(){
try {
return $this->_statement->fetch();
} catch(PDOException $e) {
return "Error: " . $e->getMessage();
}
}
public function prepare($query) {
try {
$this->_statement = $this->_connection->prepare($query);
return 1;
} catch(PDOException $e) {
return "Error: " . $e->getMessage();
}
}
public function execute($array) {
try {
$this->_statement->execute($array);
return 1;
} catch(PDOException $e) {
return "Error: " . $e->getMessage();
}
}
public function mysql_escape_mimic($inp) {
if(is_array($inp))
return array_map(__METHOD__, $inp);
if(!empty($inp) && is_string($inp)) {
return str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $inp);
}
return $inp;
}
}