Viewed   74 times

I'm trying to pass a MySQL's NOW() function into PDO's assigned value through PHP and it's failing. I found that I must pass this directly into MySQL statement. But in my case, sometimes the datetime field can be empty.

Is it even possible to pass NOW() as PHP's assigned value?

Some code:

I'm building query dynamically and the datetime is dependent on some other variable's value.

if(isset($accountStatus)&&$accountStatus!=""){
    $tmp[':account_status']=$accountStatus;
    if($accountStatus==0){
        $tmp[':vCodeExpire']="NOW() + INTERVAL 1 WEEK";
        $tmp[':verified']=0;
    }else{
        $tmp[':verified']=1;
    }
}

Building SQL query:

$sql="";
foreach($tmp as $k=>$v){
    $sql.=str_replace(":","",$k)."=".$k.",";
}
$sql=substr($sql,0,strlen($sql)-1);

Then, I run PDO query:

$db=$pdo->prepare("UPDATE users SET $sql WHERE id=:id");
$db->execute($tmp);

I tried replacing double-quotes with single-quote around NOW() + INTERVAL 1 WEEK with no luck. I also tried with single-quote around PDO query, but then $sql is passed directly, not using an assigned values.

 Answers

3

is it possible?

No.

There are 2 solutions.

  1. Calculate expiration date using PHP. Something like date('Y-m-d',strtotime('+1 week'))
  2. Create a conditional part for the query

    if(isset($accountStatus)&&$accountStatus!=""){
    $tmp[':account_status']=$accountStatus;
    if($accountStatus==0){
        $accSql = "NOW() + INTERVAL 1 WEEK,";
        $tmp[':verified']=0;
    }else{
        $accSql ='';
        $tmp[':verified']=1;
    }
    $db=$pdo->prepare("UPDATE users SET $accSql $sql WHERE id=:id");
    
Monday, November 7, 2022
1

lastInsertId() is a method of the PDO class, not the PDOStatement class.

This should work:

$groupID = $dbo->lastInsertId();
Friday, November 18, 2022
 
4

If you're using PDO, you can just catch the exception and check the status code, eg

// make sure you're set to throw exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO `user` (`email`) VALUES (?)');
try {
    $stmt->execute([$email]);
} catch (PDOException $e) {
    $errorInfo = $stmt->errorInfo(); // apparently PDOException#getCode() is pretty useless
    if ($errorInfo[1] == 1586) {
        // inform user, throw a different exception, etc
    } else {
        throw $e; // a different error, let this exception carry on
    }        
}

See http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html#error_er_dup_entry_with_key_name

The process would be similar if using MySQLi

$stmt = $mysqli->prepare('INSERT INTO `user` (`email`) VALUES (?)');
$stmt->bind_param('s', $email);
if (!$stmt->execute()) {
    if ($stmt->errno == 1586) {
        // inform user, throw a different exception, etc
    } else {
        throw new Exception($stmt->error, $stmt->errno);
    }
}
Tuesday, December 6, 2022
 
sakin
 
4

If the transaction fails for whatever reason, the code does stop at the very line where error occurred end then the execution jumps directly to the catch block. So it is sufficient the way you have it written in Code Block 2.

Note that you should always re-throw the Exception after rollback. Otherwise you will never have an idea what was a problem. So it should be

try{
    $stmt = $db->prepare(... 1 ...);
    $stmt->execute();

    $stmt = $db->prepare(... 2 ...);
    $stmt->execute();

    $stmt = $db->prepare(... 3 ...);
    $stmt->execute();

    $db->commit();

    return true;
}catch(Exception $e){
    $db->rollBack();
    throw $e;
}
Wednesday, September 14, 2022
 
akamike
 
2

Just put the result of fetch into a variable:

if($row = $q->fetch()) {
    // $row contains first fetched row
    echo $row['coloumn_name'];
}
else
    // no results
Sunday, December 25, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :