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
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- 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))
begin
delete from lock_attempt
where p_now >= convert(end_lock_time USING utf8) ;
end$$
I created the following php code to call the procedure , as follows:
<?php
define('DB_SERVER', 'dbserver');
define('DB_USERNAME', '****');
define('DB_PASSWORD', '***');
define('DB_DATABASE', '****');
define("BASE_URL", "******");
$dbhost=DB_SERVER;
$dbuser=DB_USERNAME;
$dbpass=DB_PASSWORD;
$dbname=DB_DATABASE;
try {
$dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
$dbConnection->exec("set names utf8");
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$username='1681';
$sdate=date('Y-m-d H:i:s');
$stmt = $dbConnection->prepare('CALL delTimeIfEquel(?)');
$stmt->bindParam(1, $sDate,PDO::PARAM_STR);
$stmt->execute();
$count=$stmt->rowCount();
$db = null;
if (!empty($count))
{
var_dump ($count);
}
else
{
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,
