Check if the row exists and update, if not insert

Beginners - Learning PHP
#1

Hi all,

With this code im trying to see wether the row exists by matching the session id, and if it exists update but if it doesn’t, it inserts the row…

    if($_GET['email_quote']) {
	

$a = session_id();
	$quote1 = $_GET['quote1'];
		
		$data = [
    'id' => $a,
	'quote_picked' => $quote1
];	

$sql = "IF EXISTS (SELECT * FROM final_quote WHERE quote_id=:id) UPDATE final_quote SET quote_picked=:quote_picked WHERE quote_id=:id ELSE
    INSERT INTO final_quote (quote_id, quote_picked) VALUES (:quote_id, :quote_picked)";
	$stmt= $link->prepare($sql);
	$stmt->execute($data);
	
		}

And my brain is fried…any pointers will be appreciated.

Thank you.

#2

The query to do this would be an INSERT … ON DUPLICATE KEY UPDATE …, with the quote_id column defined as a unique index.

Note: you have three place-holders defined, two of which are the same and should be named the same. This problem will go away with the INSERT … ON DUPLICATE KEY UPDATE … query, since you will only have place-holders in the INSERT … VALUE (…) part of the query. To get the value from the insert’s quote_picked column into the UPDATE part of the query, use the VALUES() function (which is not the same as the VALUES keyword in an INSERT query.)

Edit: I see that the VALUES() function is deprecated starting with MySql 8.0.19, in favor of an alias method, though not in MariaDB.

#3

Hi,

I’ve worked on it a little further and the script below works, probably needs to be cleaned up…

if($_GET['email_quote']) {

    //START

    $a = session_id();

    $count=$link->prepare("SELECT quote_id FROM final_quote WHERE quote_id=:quote_id");

    $count->bindParam(":quote_id",$a);

    

    $count->execute();

    

    $no=$count->rowCount();

    

    if($no >0 ){

        $a = session_id();

        $quote_picked = $_GET['quote1'];

        

        $data = [

    'id' => $a,

    'quote_picked' => $quote_picked

];  

            

$sql = "UPDATE final_quote SET quote_picked=:quote_picked WHERE quote_id=:id";

$stmt= $link->prepare($sql);

$stmt->execute($data);  

}else{

    $a = session_id();

        $quote_picked = $_GET['quote1'];

                                

        $statement = $link->prepare('INSERT INTO final_quote (quote_id, quote_picked)

    VALUES (:quote_id, :quote_picked)');

$statement->execute([

    'quote_id' => $a,

    'quote_picked' => $quote_picked

]);

}

    //END

    }
#4

No. A race condition exists with concurrent requests, such as a duplicate form submission or a hacker intentionally trying to trigger errors, where all the requests will find that the data doesn’t exist and will try to insert it. The database must enforce uniqueness, by defining the appropriate column(s) as a unique index, then make use of the query I cited in the previous reply above. This also simplifies the code to one single query.

#5

So to make a column unique such the ‘quote_id’ i would need to do this via PHPmyAdmin;
such as; ALTER TABLE final_quote ADD UNIQUE (quote_id);

Sponsor our Newsletter | Privacy Policy | Terms of Service