L. Grobler L. Grobler - 1 month ago 11
MySQL Question

php pdo prepares query but does not execute it

I'm really new to php and pdos. A friend of mine basically created a pdo class and a couple of examples of how to use it and that's been working great for me. But now I want to do a query that uses the

BETWEEN
mysql keyword and returns anything that matches the criteria but it just comes up blank. I have created
mysql_query.log
file and from what I can gather from it the query gets prepared but not executed. I'll show you my findings from the log in a second, let me quickly just show you my code:

$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 the applicable part of the log (which I tidied up a bit):

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


And here's an example from the log of a query that actually worked out fine for me:

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


Let me know if you want me to edit the pdo code or anything else in but as far as I can tell it's a standard pdo class. Please let me know why my query isn't returning anything

Edit: Here's the pdo class, filename
dbpdo.php
:

<?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;
}
}

Answer

First when things are going wrong add these 2 lines after your <?php tag as far to many people develop on LIVE servers where error reporting will of course be turned off, and assume there is nothing wrong with their code when in reality it is generating lots of errors.

<?php
error_reporting(E_ALL); 
ini_set('display_errors', 1);

Then set PDO to throw exceptions

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$newSlot = array(
                    ":f1" => $startDate->format('Y-m-d H:i:s')),
                    ":u1" => $endDate->format('Y-m-d H:i:s')),
                    ":f2" => $startDate->format('Y-m-d H:i:s')),
                    ":u2" => $endDate->format('Y-m-d H:i:s'))
                );


$query = "SELECT * FROM schedule_slot 
            WHERE (startDate BETWEEN :f1 AND :u1) 
               OR (endDate BETWEEN :f2 AND :u2)";

$result = $db->prepare($query);
// now execute the prepared query on $result not $db
$result = $result->execute($newSlot);

$row = $result->fetch_object();

print "slot: " . $row->startDate;

Now if any errors occur you will see them on the page, or you will see an exception throw, but not caught, so that will also show on the web page