Php Update Db Tutorial

We will now use our users table inside the database examples:

Our users table has 3 columns: id, name, surname.

To UPDATE some data in our table we will:

1. connect to the server (localhost)
   
2. select a database (examples)
3. create a SQL UPDATE query
4. run the query and store the result
   
5. check if the query has been successful

<?php #updateData.php
  //connecting to server
  $dbc = mysqli_connect('localhost','root','') or 
                    die('could not connect: '. mysqli_connect_error() );

  //selecting the database
  mysqli_select_db("examples") or die('could not select db');

  // OLD DATA: SUPPOSE WE HAVE A ROW WITH... 
/*
*  id = 15;
*  name = "jane";
*  surname = "doe";
*/

  // AND WE WANT TO CHANGE IT TO... 
  $name = "Janet";
  $surname = "Doe";

  //THE ID OF THE ROW TO UPDATE 
  $edit_id = 15;

  //CREATING AN UPDATE QUERY
/*
* UPDATE `DB`.`TABLE` SET `FIELD` = 'value'  WHERE `TABLE`.`FIELD` = value; 
*/


$q = "UPDATE `examples`.`users` 
		 SET `name` 	 	= '$name',
		 	`surname` 	= '$surname' 
	   WHERE `users`.`id` 	=  $edit_id
 ";

  //EXECUTING THE QUERY AND STORING THE RESULT IN A VARIABLE
  $result = mysqli_query($dbc, $q);

  //CHECKING IF THE QUERY AFFECTED A ROW USING mysqli_affected_rows()
  if(mysqli_affected_rows($dbc) >0 ){
	//positive response
	echo "data updated";
  } else {
	//negative response
	echo "could not update data";
  }

//CLOSING DATABASE CONNECTION
mysqli_close($dbc);
?>

PLEASE NOTE: the UPDATE command is quite powerful, if you do not specify which row you want to update, ALL the rows will be updated. That's why you should always use a WHERE clause.

Printer friendly page

Watch Video Tutorial