I have a form which displays personal info of the person
<form>
<input type="text" name="per_name" id="name_1"/>
<input type="text" name="per_mail" id="email_1"/>
<input type="text" name="per_phone" id="phone_1"/>
</form>
Now when i submit this form then an update query is called for databse like:
if($_POST['name']!=['name from db']){
$sql = "update table_1 set name=? where int_id=?";
}
In this way i have to write three different update queries. But i want that the update query should be generated based on verifying if supplied values are different than already present values in DB.
Please guide here.
Well this could be done in one query using one update query:
$sql = "update table_1 set name=?, mail=?, phone=? where int_id=?";
3 diferent query are not required
and for validations, you can use
$name = $_POST['per_name'];
$mail = $_POST['per_mail'];
$phone = $_POST['per_phone'];
$qry = array();
if($name !== ['value from db'])
$qry[] = 'name=?';
if($mail !== ['value from db'])
$qry[] = 'mail=?';
if($phone !== ['value from db'])
$qry[] = 'phone=?';
if(count($qry) > 0) {
$qry = implode(', ', $qry);
$updateQuery = "update table_1 set " . $qry . " where int_id=?";
// Execute your query here
}
One way you could solve this is to make a little change in your database.
Practically, name can be repeating but you cannot have the same email address used by 2 different people (names), so put a constraint of unique index for email address column.
Then, instead of update, use Replace INTO:
$sql = "REPLACE INTO table_1 (name,email,phone) VALUES ({$_POST['per_name']},{$_POST['per_mail']},{$_POST['per_phone']})";
From the documentation:
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
In the above case if the user is trying to enter the same e-mail ID, it will be updated, if it's a new e-mail ID, it will be inserted.