how to is_record_exists using PDO?

jrahma

Hi,

I would like yo know how can I check if record is exist when trying to INSERT then read the is_record_exists to display (Already exists) message?

this is the code I am using to INSERT:

$mysql_command = "CALL sp_add_new_member(:param_member_email, :param_member_first_name, :param_member_last_name, :param_member_password)";

$mysql_query = $mysql_connection->prepare($mysql_command);
// $mysql_connection->beginTransaction();
$mysql_query->bindParam(':param_member_email', $member_email, PDO::PARAM_STR);
$mysql_query->bindParam(':param_member_first_name', $member_first_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_member_last_name', $member_last_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_member_password', $member_password, PDO::PARAM_STR);

$mysql_query->execute();

Thanks

bradgrafelman

Where and/or what is "is_record_exists" ?

EDIT: This StackOverflow answer mirrors my first instinct; catch the 1062 (duplicate data for key) error code and set some OUT status variable if it happens.

NogDog

This is the gist of a quick fix I put into a piece of code where there was a tiny chance of a race condition causing a duplicate key error during an insert:

if($insertStmt->execute() == false) {
	$err = $insertStmt->errorInfo();
	if($err[0] == 23505) { // dup key error
		// do some stuff to deal with it
	}
	else {
		// generic DB error-handling here
	}
}

jrahma

bradgrafelman;11032639 wrote:
Where and/or what is "is_record_exists" ?

EDIT: This StackOverflow answer mirrors my first instinct; catch the 1062 (duplicate data for key) error code and set some OUT status variable if it happens.

I know that from the MySQL level but I want to know how to do it in the PDO.

using the same example you posted here from stackoverflow, how can I return the out_status value in my PDO?

CREATE PROCEDURE sp_do_insert(
   IN in_param1    int,
   IN in_param2    int,
   OUT out_status  tinyint
)
BEGIN
   DECLARE CONTINUE HANDLER FOR 1062 SET out_status = 1;

   SET out_status = 0;

   INSERT INTO tbl(field1, fiel2)
   VALUES (in_param1, in_param2); 
END;

NogDog

If that's all the procedure is returning, I think you could just do:

$mysql_query->execute();
$result = $mysql_query->fetchColumn();

jrahma

NogDog;11032655 wrote:
If that's all the procedure is returning, I think you could just do:
$mysql_query->execute();
$result = $mysql_query->fetchColumn();

what if I have multiple values such as:

OUT out_status_1  tinyint
OUT out_status_2  tinyint 
OUT out_status_3  tinyint

NogDog

Then just do a regular fetch:

$result = $mysql_query->fetch(PDO::FETCH_ASSOC);
print_r($result); // should be an associative array of the returned values

(I think: haven't tried such things with a stored procedure yet.)

bradgrafelman

For a prepared statement with a stored proc, you'd still bind the output variables - see: [man]pdostatement.bindparam[/man]. From that PHP manual page:

PHP Manual wrote:
To return an INOUT parameter from a stored procedure, use the bitwise OR operator to set the PDO:😛ARAM_INPUT_OUTPUT bits for the data_type parameter.