Change an Insert statement to an update statement in PHP/MySQL

Question

I'm making an Android app that connects to a database online and lets the user edit the database from the application, I'm new to PHP and MySql but from my research I think I should be using an UPDATE statement, I've written the code below to register new users on the site from a tutorial, but I'd like to change the INSERT statement to an UPDATE statement so that instead of registering a new user, the App updates existing data that I have entered in PHPMYADMIN, could someone show me how to do this? Also, if you require the code for the app mention it in the comments and I'll add it to the question, I don't want to post too much unneccessary code. Thanks in advance.

<?php
    require "conn.php";

    $patient_name = $_POST["patient_name"];
    $check_in_date = $_POST["check_in_date"];
    $room_number = $_POST["room_number"];
    $bed_number = $_POST["bed_number"];
    $notes = $_POST["notes"];

    $mysql_qry = "insert into patients(patient_name, check_in_date, room_number, bed_number, notes) values ('$patient_name', '$check_in_date', '$room_number', '$bed_number', '$notes')";

    if($conn->query($mysql_qry) === TRUE) {
        echo "Insert successful";
    }
    else{
        echo "Error: " . $mysql_qry . "<br>" . $conn->error;
    }
    $conn->close();
?>

EDIT

The fixed code is below, it now updates records already in the database rather than adding new data.

<?php
    require "conn.php";

    $patient_name = $_POST["patient_name"];
    $check_in_date = $_POST["check_in_date"];
    $room_number = $_POST["room_number"];
    $bed_number = $_POST["bed_number"];
    $notes = $_POST["notes"];

    $mysql_qry = "UPDATE patients SET notes='$notes' WHERE patient_name='$patient_name'";

    if($conn->query($mysql_qry) === TRUE) {
        echo "Insert successful";
    }
    else{
        echo "Error: " . $mysql_qry . "<br>" . $conn->error;
    }
    $conn->close();
?>

Answer 1

Actually I was looking for a small function that converts an INSERT MySQL query to an UPDATE query. So maybe other people were looking for the same and I think this is what the original poster was looking for aswell... I couldnt find any so I made this simple function which works for my needs, ofcourse you will have to make sure your original query is safe from MySQL injection.

It will convert

INSERT INTO aaa (bbb, ccc) VALUES ('111', '222')

to

UPDATE aaa SET ccc='222' WHERE bbb='111'

Use the 2nd variable ($iColumn) to identify the WHERE statement.

function convertInsertToUpdate($sQuery, $iColumn = 1) {
$sNewQuery = "";
$iPos = strpos($sQuery, ' (');
$sTmpTable = substr($sQuery, 0, $iPos);
$iPos = strpos($sTmpTable, 'INSERT INTO ');
$sTmpTable = substr($sTmpTable, $iPos+12);
$iPos = strpos($sQuery, ') VALUES (');
$sTmpValues = substr($sQuery, $iPos+10);
$iPos = strrpos($sTmpValues, ')');
$sTmpValues = substr($sTmpValues, 0, $iPos);
$iPos = strpos($sQuery, '(');
$sTmpColumns = substr($sQuery, $iPos+1);
$iPos = strpos($sTmpColumns, ') VALUES (');
$sTmpColumns = substr($sTmpColumns, 0, $iPos);

$aColumns = explode(', ', $sTmpColumns);
$aValues = explode(', ', $sTmpValues);

if (count($aColumns)>0 && count($aColumns) == count($aValues) && $iColumn < (count($aValues)+1)) {
    $sNewQuery = "UPDATE ".$sTmpTable." SET";
    $sTmpWhere = "";
    $bNotFirst = false;
    $iX = 0;
    while ($iX<count($aColumns)) {
        if ($iColumn == ($iX+1)) {
            $sTmpWhere = " WHERE ". $aColumns[$iX]."=".$aValues[$iX];
            $iX++;
            continue;
        }
        if ($bNotFirst) {
            $sNewQuery .= ",";
        }
        $sNewQuery .= " ".$aColumns[$iX]."=".$aValues[$iX];
        $bNotFirst = true;
        $iX++;
    }
    $sNewQuery .= $sTmpWhere;
}
return $sNewQuery;
}

Answer 2

You can also use REPLACE INTO, then you don't have to change the SQL statement. Let MySQL do the work for you.

https://dev.mysql.com/doc/refman/5.7/en/replace.html

<?php
require "conn.php";

$patient_name = $_POST["patient_name"];
$check_in_date = $_POST["check_in_date"];
$room_number = $_POST["room_number"];
$bed_number = $_POST["bed_number"];
$notes = $_POST["notes"];

$mysql_qry = "REPLACE INTO patients(patient_name, check_in_date, room_number, bed_number, notes) VALUES ('$patient_name', '$check_in_date', '$room_number', '$bed_number', '$notes')";

if($conn->query($mysql_qry) === TRUE) {
    echo "Insert successful";
}
else{
    echo "Error: " . $mysql_qry . "<br>" . $conn->error;
}
$conn->close();

Also, you should really take a look at using PDO with prepared statements and parameters.

https://secure.php.net/manual/en/pdo.prepare.php

Answer 3

first of all this PHP code is vulnerable to sql injection you should, no need to update your code to use either mysqli prepared statement or PDO prepared statement

secondly the easiest way I know you accomplish your goal would make a unique constraint on some columns and then use a mysql feature ON DUPLICATE UPDATE

for this example I'll assume that the unique fields determining an update instead of an insert are patient_name, check_in_date, room_number, and bed_number (in case john smith was in the same room as john smith in seprate beds) the query to update the table would be like this

ALTER TABLE `patients` ADD UNIQUE `unique_index`(`patient_name`, `check_in_date`, `room_number`, `bed_number`);

so now to address the sql injection bit and the query, I'll update the example to use mysqli statement and will assume patient_name and notes are strings (varchar/nvarchar), room_number and bed_number are integers, and check_in_date is a date

Edit My original answer had a syntax error in the query and also passing variables to the prepared statement below is the updated answer

$mysqliConn = new mysqli("localhost", "my_user", "my_password", "mydatabase");

$stmt = $mysqliConn->prepare("insert into patients
    (patient_name, check_in_date, room_number, bed_number, notes) 
    values (?, ?, ?, ?, ?) 
    ON DUPLICATE KEY UPDATE notes=values(notes)");

$patient_name = $_POST["patient_name"];
$check_in_date = $_POST["check_in_date"];
$room_number = $_POST["room_number"];
$bed_number = $_POST["bed_number"];
$notes = $_POST["notes"];

mysqli_stmt_bind_param($stmt, "sdiis", 
    $patient_name, $check_in_date, $room_number, $bed_number, $notes);

hope this helps

Edit Regarding the unique key, a unique key means that all fields in the unique key have to be unique when combined so for the example above

if record 1 is patient_name, check_in_date, room_number, bed_number, notes 'john smith', '3/1/2017' , 413 , 2 , 'patient is sick' and record two is 'jane doe' , '3/1/2017' , 413 , 2 , 'patient has wound'

these two records will note be duplicates with the above constraint but if you do need to change the constraint you can do the following

DROP the Constraint

ALTER TABLE `patients` DROP INDEX `unique_index`;

Then recreate the constraint like this

ALTER TABLE `patients` ADD UNIQUE `unique_index`(`patient_name`, `check_in_date`, `room_number`);

also if you named your constraint something other than unique_index you can find the key_name by running the following

SHOW INDEX FROM `patients`;

the name will be in the key_name column

additionally you may want to alter the last line of the query to be this in your php if you change the unique constraint so you can change bed number

ON DUPLICATE KEY UPDATE bed_number=values(bed_number), notes=values(notes)