Once tables contain some data, you can edit and change those existing records with UPDATE statement.
The UPDATE command is used to change existing records in a table.

  - Syntax:
UPDATE table_name
 SET column1=value, column2=value2,...
 WHERE some_column=some_value
- The WHERE clause is important in a UPDATE query, it tells MySQL which record or records should be updated. If you omit the WHERE clause, all rows will be affected!
- The UPDATE statement is sent to the MySQL server with the query() method of the mysqli object.

  - Example
In the previous lessons was created a table named "users" and we added some data in it. Here is how it looks the first two records.
idnamepasswordemailreg_date
1 Marius faith name@domain.net 2011-03-24 09:51:46
2 MarPlo peace user@domain.com 2011-03-24 10:10:27

In this example, we'll change the email for the user with the "name" MarPlo.
<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', 'pass', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// UPDATE sql query
$sql = "UPDATE `users` SET `email`='new_mail@domain.net' WHERE `name`='MarPlo' AND `id`=2";

// perform the query and check for errors
if (!$conn->query($sql)) {
  echo 'Error: '. $conn->error;
}

$conn->close();
?>
As you can see, the WHERE clause sets two conditions with the "AND" operator (WHERE `name`='MarPlo' AND `id`=2), this tells MySQL to update only the rows that have "name='MarPlo'" and "id=2". Setting these two conditions, we are shure that only that row will be updated, not other row with the same name.
Also, you can apply the LIMIT option to set how many rows to be updated.
Example:
                $sql = "UPDATE `users` SET `email`='new_mail@domain.net' WHERE `name`='MarPlo' AND `id`=2 LIMIT 1";

After the update, the first two rows in "users" table will look like this:
idnamepasswordemailreg_date
1 Marius faith name@domain.net 2011-03-24 09:51:46
2 MarPlo peace new_mail@domain.com 2011-03-27 10:20:58
- Becouse "reg_date" is a TIMESTAMP column (with attribute:   on update CURRENT_TIMESTAMP ), MySQL server has updated its value too, changing it with the current date and time when the update was performed.
If you want an update to not affect the value of a TIMESTAMP column, when you create the table you must not add the "on update" attribute, only the DEFAULT ( `column_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ).

- If you perform an update query that doesn't actually change any values (becouse the WHERE condition doesn't match any row), you won't see any errors but no rows will be affected.
- It's indicated to not change the value of a primary-key column (in the example above, the "id" column).

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which HTML5 tag is indicated to be used as container for menu with navigation links in Web site?
<section> <nav> <article>
<nav><ul>
 <li><a href="http://coursesweb.net/css/" title="CSS Course">CSS Course</a></li>
 <li><a href="http://www.marplo.net/jocuri/" title="Flash Games">Flash Games</a></li>
</ul></nav>
Which CSS property shifts an item horizontally to the left or right of where it was?
text-align clear float
.some_class {
  width: 30%;
  float: left;
}
Click on the Math object method which returns x, rounded downwards to the nearest integer.
Math.ceil(x) Math.abs(x) Math.floor(x)
var num = 12.34567;
num = Math.floor(num);
alert(num);       // 12
Indicate the PHP function which returns the number of characters in string.
mb_strlen() count() stristr()
$str = "string with utf-8 chars åèö";
$nrchr = mb_strlen($str);
echo $nrchr;        // 30
PHP MySQL - UPDATE

Last accessed pages

  1. Register and show online users and visitors (22699)
  2. Subclass with extends and Inheritance (27)
  3. Common PHP Errors and Solutions (6566)
  4. Numbers and Math in ActionScript 3 (3117)
  5. JavaScript base64 encode decode (3644)

Popular pages this month

  1. Read Excel file data in PHP - PhpExcelReader (303)
  2. PhpSpreadsheet - Read, Write Excel and LibreOffice Calc files (250)
  3. Register and show online users and visitors (241)
  4. Courses Web: PHP-MySQL JavaScript Ajax HTML CSS Flash-AS3 (237)
  5. Insert, Select and Update NULL value in MySQL (149)