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:
- connect to the server (localhost)
- select a database (examples)
- create a SQL UPDATE query
- run the query and store the result
- check if the query has been successful
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<?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.