Edit query in php

PHP MySQL UPDATE Query

In this tutorial you’ll learn how to update the records in a MySQL table using PHP.

Updating Database Table Data

The UPDATE statement is used to change or modify the existing records in a database table. This statement is typically used in conjugation with the WHERE clause to apply the changes to only those records that matches specific criteria.

The basic syntax of the UPDATE statement can be given with:

Let’s make a SQL query using the UPDATE statement and WHERE clause, after that we will execute this query through passing it to the PHP mysqli_query() function to update the tables records. Consider the following persons table inside the demo database:

+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 4 | John | Carter | johncarter@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+----------------------+

The PHP code in the following example will update the email address of a person in the persons table whose id is equal to 1.

Example

 // Attempt update query execution $sql = "UPDATE persons SET email='peterparker_new@mail.com' WHERE $sql)) < echo "Records were updated successfully."; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

connect_error); > // Attempt update query execution $sql = "UPDATE persons SET email='peterparker_new@mail.com' WHERE === true) < echo "Records were updated successfully."; >else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt update query execution try< $sql = "UPDATE persons SET email='peterparker_new@mail.com' WHERE $pdo->exec($sql); echo "Records were updated successfully."; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

After update the persons table will look something like this:

+----+------------+-----------+--------------------------+ | id | first_name | last_name | email | +----+------------+-----------+--------------------------+ | 1 | Peter | Parker | peterparker_new@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 4 | John | Carter | johncarter@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+--------------------------+

Warning: The WHERE clause in the UPDATE statement specifies which record or records should be updated. If you omit the WHERE clause, all records will be updated.

Источник

Update Data in SQL Table with Form Using PHP & MYSQL

Hello Developer, In this tutorial, You will learn to edit and update data in SQL Table with form using php & mysql. Even You will know more to update existing records of an table using MySQLi procedure, MySQLi Object-oriented, PDO & prepared statement with a new concept & an example.

Steps to Edit and Update Data using HTML Form with PHP & MYSQL

In this step, you will learn to update data dynamically with an advanced coding concept that will help you to improve your coding skills for writing smart & standard code in PHP.

Before getting started, make sure that the local server (xamp/wamp) must be installed in your system and start it if it is not being started already.

Learn Also –

You can test yourself to update data with the following folder structure –

codingstatus/ |__database.php |__table.php |__form.php |__developers.php |

1. Connect PHP to MySQL database

You can use the following database connection query to connect PHP to the MySQL database

• $hostName – It contains host name
• $userName – It contains database username
• $password – It contains database password
• $databaseName – It contains database name.

connect_error) < die("Connection failed: " . $conn->connect_error); > ?>

3. Create Edit Button in HTML Table

Before creating Edit button , you have insert from data and display them in the HTML Table. You you have not done it, you can do it with the help of the following tutorials –

Then you have to add thr following code in the HTML Table in which you have already show records.

2. Edit and update Data using PHP & MySQLi

Now, go through the next points –

You have to uderstand the following points to edit data –

• First of all, Include database.php file
• First of all, include a database connection file database.php
• assign $conn to a new variable $db and table name to another variable $table
• access edit id with $_GET[‘edit’] and check it is set or not using isset() method. then implements the next point within if condition. this step will be executed when you click edit button in HTML table.
• validate $_GET[‘edit’] using validate() function and assign it to the $id.
• declare $id in an associative array and assign to the $condition. but array key name must be same as column name i.e id
• call edit_data() function & asiign to the $editData.

edit_data() – This fuction accepts three parameters like $db, $tableName, $column & $condition and It will be executed while you click edit button and return data based on the specified id.

Update Data –

You have to uderstand the following points to update data –

• First of all, define extract($_POST) and $_GET[‘edit’] is set using isset() method. If it is true then implements the next all points within the if condition.
• get input data and declare them into an associative array then assign to the variable $inputData
• access and validate $_GET[‘edit’] then assign to the $id.
• declare $condition in an associative array then assign to the $condition
• call update_data() and assign it to the $result
• After that, redirect to the form.php

update_data() – This fuction accepts three parameters like $db, $tableName, $inputData & $condition and It will be executed while you submit the form after changing the input value. Also, It will update data successfully based on the specified id and return an message.

$id]; $columns= ['id', 'fullName','gender','email','mobile', 'address','city','state']; $editData = edit_data($db, $tableName, $columns, $condition); > function edit_data($db, $tableName, $columns, $condition)< if(empty($db))< $msg= "Database connection error"; >elseif (empty($columns) || !is_array($columns)) < $msg="columns Name must be defined in an indexed array"; >elseif (!is_array($condition)) < $msg= "Condition data must be an associative array"; >elseif(empty($tableName))< $msg= "Table Name is empty"; >else < $columnName = implode(", ", $columns); $conditionData=''; $i=0; foreach($condition as $index =>$data) < $and = ($i >0)?' AND ':''; $conditionData .= $and.$index." = "."'".$data."'"; $i++; > $query = "SELECT ".$columnName." FROM $tableName"; $query .= " WHERE ".$conditionData; $result = $db->query($query); $row= $result->fetch_assoc(); return $row; if($row== true)< if ($result->num_rows > 0) < $row= mysqli_fetch_all($result, MYSQLI_ASSOC); $msg= $row; >else < $msg= "No Data Found"; >>else < $msg= mysqli_error($db); >> return $msg; > // update data extract($_POST); if(isset($update) && isset($_GET['edit'])) < $updateDate = date("Y-m-d H:i:s"); $inputData = [ 'fullName' =>validate($fullName) ?? "", 'gender' => $_POST['gender']?? "", 'email' => validate($email) ?? "", 'mobile' => validate($mobile) ?? "", 'address' => validate($address) ?? "", 'city' => validate($city) ?? "", 'state' => validate($state)?? "", 'created_at' => $updateDate ?? "" ]; $id = validate($_GET['edit']); $condition= ['id' =>$id]; $result= update_data($db, $tableName, $inputData, $condition); header("location:form.php"); > function update_data($db, $tableName, $inputData, $condition)< $data = implode(" ",$inputData); if(empty($db))< $msg= "Database connection error"; >elseif(empty($tableName))< $msg= "Table Name is empty"; >elseif(trim( $data ) == "")< $msg= "Empty Data not allowed to update"; >elseif(!is_array($inputData) && !is_array($condition))< $msg= "Input data & condition must be in array"; >else < // dynamic column & input value $cv=0; $columnsAndValue=''; foreach ($inputData as $index =>$data) < $comma= ($cv>0)?', ':''; $columnsAndValue .= $comma.$index." = "."'".$data."'"; $cv++; > // dynamic condition $conditionData=''; $c=0; foreach($condition as $index => $data)< $and = ($c>0)?', ':''; $conditionData .= $and.$index." = "."'".$data."'"; $c++; > // update query $query = "UPDATE ".$tableName; $query .= " SET ".$columnsAndValue; $query .= " WHERE ".$conditionData; $execute= $db->query($query); if($execute=== true)< $msg= "Data was updated successfully"; >else < $msg= $query; >> return $msg; > function validate($value) < $value = trim($value); $value = stripslashes($value); $value = htmlspecialchars($value); return $value; >?>

4 .Update data using HTML Form

Now, you create a new HTML form to update data. But You can use only a single form to insert & update data. So, Here I have used form.php that is created using the following bootsrap 4 CDN.

You have to inlcude PHP script file in which you have written code for editing & updating data

When you click edit button in HTML table then form.php will be appeard with existing input value based on passing id by the query string.

Input values will be fetched from the SQL table and stored to the variable $editData in the form of an array. So, You will have to get each input data values like the following code

Input Fields	Input Values
Full Name	$editData[‘fullName’]
Gender	$editData[‘gender’]
Email	$editData[’email’]
Mobile	$editData[‘mobile’]
Address	$editData[‘address’]
city	$editData[‘city’]
State	$editData[‘state’]

      
 
 
 
 
 
HTML Form to Insert Data
 
  
 
">
 
 
>Male
 
>Female
 
 
">
 
">
 
 
">
 
">
 Save  
 
   

5. Test Yourself to update data

After Implementing previous steps, You can test it yourself to open the following URL in your web browser

https://localhost/codingstatus/table.php

More methods to update data using PHP & MYSQL

Update data using MySQLi Procedure

Edit data using MySQLi Procedure –

Update data using MySQLi Procedure

else< echo $conn->error; > echo $conn->error; > ?>

Update Data using MySQLi Object-oriented

Edit Data using MySQLi Object-oriented

query($query); $editData= $result->fetch_assoc(); $fullName = $editData['fullName']; $email = $editData['email']; $gender = $editData['gender']; $mobile = $editData['mobile']; $address = $editData['address']; $city = $editData['city']; $state = $editData['state']; >

update data using MySQLi Object-oriented

query($query); if($execute== true)< header("location:display-data.php"); >else< echo $conn->error; > echo $conn->error; > ?>

Update Data Using Prepared Statement & MySQLi

Edit data using prepared statement & MySQLi

prepare($query); $prepared->bind_param('i', $id); $prepared->execute(); $result= $prepared->get_result(); $editData= $result->fetch_assoc(); $fullName = $editData['fullName']; $email = $editData['email']; $gender = $editData['gender']; $mobile = $editData['mobile']; $address = $editData['address']; $city = $editData['city']; $state = $editData['state']; > ?>

Update data using prepared statement & MySQLi

prepare($query); $prepared->bind_param('i', $id); $execute= $prepared->execute(); if($execute== true)< header("location:display-data.php"); >else< echo $conn->error; > echo $conn->error; > ?>

Источник