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();
?>

You should look into building a small API that you can access for the two different objective you are looking to do. One would be a put request and one would be a patch request. — dsadnick
– dsadnick, Commented Mar 31, 2017 at 20:04
The above code presents a serious security flaw - you are inserting $_POST values directly into the database without filtering them first. This will expose your database to SQL injections. — Yoav Kadosh
– Yoav Kadosh, Commented Mar 31, 2017 at 20:06
Your question too complex and too easy for the site. At first, read tutorial tutorialspoint.com/php/index.htm After read abut SQL w3schools.com/sql/sql_update.asp P.S. For production code Prepared Statements for DB requests are preferred. — Vitalii Pro
– Vitalii Pro, Commented Mar 31, 2017 at 20:09
@YoavKadosh It doesn't need to be secure, it's only for a college project, functionality is the key thing here. Is it as simple as changing my current code to the code from the W3Schools link above? — MonBoy175
– MonBoy175, Commented Mar 31, 2017 at 20:11

Jpsh · Accepted Answer · 2017-04-01 16:55:19Z

2

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)

edited Apr 1, 2017 at 16:55

answered Mar 31, 2017 at 20:39

Jpsh

1,72612 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

MonBoy175 Over a year ago

Just referring to the first part of your answer, there is already a unique identifier column called 'ID' in the database that is auto incremented, so the first person registered is 1, second is 2 and so on. Thanks for the detailed answer!

Jpsh Over a year ago

a unique identifier or a primary key is fine, what the unique constraint would prevent from inserting duplicate records and enforce it at a database level, so if you try to update the notes for a patient instead of inserting the exact same record with different notes it would update the existing notes.

MonBoy175 Over a year ago

Okay thanks. So should the code in my original question be as follows: $MySQL_qry = "ALTER TABLE patients ADD UNIQUE unique_index(patient_name, check_in_date, room_number, bed_number);" and remove the line: $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')"; ?

Jpsh Over a year ago

no the alter query would be something you would run one time, while it could be run in php it would be easier to run it once in PHPAdmin since you mentioned you're using it

MonBoy175 Over a year ago

Oh okay thanks, I'll run that in PHPMyAdmin now and then do I just change the insert statement to an update statement?

|

Hayden · Accepted Answer · 2017-04-01 19:36:43Z

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

A. Mes · Accepted Answer · 2018-10-26 09:02:46Z

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;
}

Collectives™ on Stack Overflow

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

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related