Procedure doesn't return value using php, but return value using phpmyAdmin

Hello everybody,

My code idea is about blocking certain users from login for 30 minutes , So I created a table to store ( user_id / start lock time / end lock time) , the user who has record in this table will not be able to login as long as “start lock time” greater than “end lock time”
then I created a procedure to unlock users login by deleting the records which has “end lock time” less then “server current time”

The table structure & data:

-- Table structure for table `lock_attempt`

CREATE TABLE `lock_attempt` (
  `user_id` varchar(5) DEFAULT NULL,
  `start_lock_time` datetime DEFAULT NULL,
  `end_lock_time` datetime DEFAULT NULL

-- Dumping data for table `lock_attempt`

INSERT INTO `lock_attempt` VALUES('1681', '2017-06-07 09:16:11', '2017-06-07 09:46:11');

my procedure as follows:

create procedure delTimeIfEquel(IN p_now varchar(40))
delete from lock_attempt
where   p_now >=   convert(end_lock_time USING utf8) ;

I created the following php code to call the procedure , as follows:


define('DB_SERVER', 'dbserver');
define('DB_USERNAME', '****');
define('DB_PASSWORD', '***');
define('DB_DATABASE', '****');
define("BASE_URL", "******"); 


try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sdate=date('Y-m-d H:i:s');

$stmt = $dbConnection->prepare('CALL delTimeIfEquel(?)'); 
$stmt->bindParam(1, $sDate,PDO::PARAM_STR);
$db = null;
if (!empty($count))
var_dump ($count);


var_dump ($count);


catch(PDOException $e) {
echo '{"error":{"text":'. $e->getMessage() .'}}';


When execute the php code I got 0 ,But when I run the procedure from phpMyAdmin,it deletes the record as illustrated in the following image, I don’t know why ?!

mySQL version: 5.5.51
– PHP Version: 5.2

Your help would be very much appreciated.
Thank You,

Case-sensitive variable names typo?

$sdate=date('Y-m-d H:i:s');   // <- here sdate
$stmt->bindParam(1, $sDate,PDO::PARAM_STR);  // <- here, sDate

good notice, thank you :slight_smile:

I edit the code, but still get 0

Reading some other comments, I wonder whether you actually have to return the row count from the stored procedure, rather than using rowCount(). It’s hard to see for sure, but some stored procedures seem to do that and some do not.

I guess the question (which might be obvious) is, although it gives 0, does it also delete the record?

No, the record still exist.

OK, then I don’t get it. I created a table the same as yours (though I missed the _ out because I mistyped it), created the record, created the stored procedure (after I finally noticed the ‘delimiter’ box in phpmyadmin) and copied your code. Once I’d changed the $sdate to be $sDate in line 19, it worked just fine and returned ‘1’ for the number of rows affected. Browsing the table showed the record had been deleted.

ETA - only other thought is - what is the current server time? Is it the same as the time you manually entered in phpmyadmin? Another obvious one.

And that proves my comment above about rowCount() not working because of stored procedures is untrue.

1 Like

That version has been “dead” for a long time now. (~6 1/2 years

IMHO it would be a waste of time trying to debug code that may not be working simply because it is not backwards compatible with a version that is no longer supported.


ya, the server time is the same as the one I manually entered.
I tried both method several times simultaneously .

I tried on another server php version: 5.4, and it works perfectly.

Thank you guys :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.