PHP Prepared Statement insert or update If row exists

Question

I have been searching for a fix for my code below. I have searched many similar post in stackoverflow but didn't get a work around.

What I'm trying to establish is that, I want to check a query and if row exist I need to use UPDATE statement else INSERT statement. I have established this many time but without the prepared statement. Now that I'm modifying many of my previous codes with the prepared statements. Below is my code.

<?php

include'../db.php';

$exercise_days=$_POST['edays'];
$id=$_POST['id'];

if ($exercise_days!='' && is_numeric($exercise_days)) 
{
    $query=$db->prepare("SELECT * from exercisetable where id=?");
    $query->bind_param('s', $id);
    /* execute query */
    $query->execute();
    /* store result */
    $query->store_result();
    $query->bind_result($rowcheck);
    $query->fetch();
    if ($rowcheck)
    {
        $e_update=$db1->prepare("UPDATE exercisetable SET exercise_days=? WHERE id=?");
        $e_update->bind_param('ss', $exercise_days, $id);
        $e_update->execute();
        if($e_update){
        $message='Exercise Days Updated';
        $success='Success';
        echo json_encode(array('message'=>$message,'success'=>$success));
        }
        $e_update->close();

    } else {
        $e_insert=$db1->prepare("INSERT INTO exercisetable (`id`, `exercise_days`) VALUES (?,?)");
        $e_insert->bind_param('ss', $id, $exercise_days);
        $e_insert->execute();
        if($e_insert){
        $message='Exercise Days Inserted';
        $success='Success';
        echo json_encode(array('message'=>$message,'success'=>$success));
        }
        $e_insert->close();

    }
}

else  
{
    $message='Exercise Days Can Only Be Numbers';
    $success='Error';
    echo json_encode(array('message'=>$message,'success'=>$success));
} 
 ?>

Executing the above code always go into else statement where it goes to INSERT even though row exists. What I want is UPDATE if row Exists and INSERT if else. Any help appreciated.

investigate `on duplicate key` syntax to perform the update perhaps ~ https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html — Professor Abronsius, Aug 05 '19 at 17:05
Possible duplicate of [if not exists insert in MySql](https://stackoverflow.com/questions/4674849/if-not-exists-insert-in-mysql) — Martin Dimitrov, Aug 05 '19 at 17:05
You can also look [here](https://stackoverflow.com/questions/18008191/mysqli-insert-but-only-if-not-a-duplicate) — Martin Dimitrov, Aug 05 '19 at 17:06
@Martin Dimitrov , I'm very well aware to perform this without prepared statement. What I'm trying is using prepared statement. Your link take me back to non-prepared statement. Thanks anyway — theveil, Aug 05 '19 at 17:13
@theveil the first link will work with a prepared statement. This is a built in functionality. — Martin Dimitrov, Aug 05 '19 at 17:18
@Martin Dimitrov the mentioned if not exists insert do not give option to update existing, so I chose on duplicate key. Appreciate your response. — theveil, Aug 05 '19 at 17:53

score 2 · Accepted Answer · answered Aug 05 '19 at 17:44

2

Thanks @RamRaider for the hint and suggestion. I'm posting the solution that worked for me, if in case anyone would require an instant reference.

Few edits that were done to MySql column prior to change in my original PHP code were that I altered my column to UNIQUE.

<?php

include'../db.php';

$exercise_days=$_POST['exercise_days'];
$id=$_POST['id'];

if ($exercise_days!='' && is_numeric($exercise_days))  
{
        $query=$db->prepare("INSERT INTO exercisetable (`id`, `exercise_days`) VALUES (?,?) ON DUPLICATE KEY UPDATE exercise_days =?");
        $query->bind_param('sss', $id, $exercise_days, $exercise_days);
        $query->execute();
        if($query){
        $message='Exercise Days Inserted';
        $success='Success';
        echo json_encode(array('message'=>$message,'success'=>$success));
        }
        $query->close();
}

else
{
    $message='Excercise Days Can Only Be Numbers';
    $success='Error';
    echo json_encode(array('message'=>$message,'success'=>$success));
} 
 ?>

answered Aug 05 '19 at 17:44

theveil

77
1
15

Instead of duplicating bound params, you can alter your SQL's UPDATE code to use `VALUES`: ``$query=$db->prepare("INSERT INTO exercisetable (`id`, `exercise_days`) VALUES (?,?) ON DUPLICATE KEY UPDATE `exercise_days` = VALUES(`exercise_days`)");`` – MER Aug 05 '19 at 18:14
Glad that my suggestion helped you solve your problem :-) – Professor Abronsius Aug 05 '19 at 18:41
@RamRaider, is there any way to prevent the auto_increment being incremented for every ON DUPLICATE KEY UPDATE – theveil Aug 07 '19 at 15:00
I believe this is just the way it is.... https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql – Professor Abronsius Aug 07 '19 at 15:03

PHP Prepared Statement insert or update If row exists

1 Answers1