PHP MySQL: Update, non OOP style

This entry is part 8 of 17 in the series PHP & MySQL

We have used mostly OOP way of making connection to the DB and executing the query string.  Since we are using PHP 5, it is recommended to use OOP method.

By now, you should be able to see how easy it is to execute a query string using PHP.  For this mysql update, we will set up the connection using non OOP way.

Instead of using new mysqli we use mysqli_connect().

<?php
$link = mysqli_connect("localhost", "root", "", "allartist");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s", mysqli_connect_error());
    exit();
} else {
    printf("Connection to DB successful");
}

$sql = "update artist set year = 1990 where id = 2";

if (mysqli_query($link, $sql) === TRUE) {
    echo "Record updated successfully<br>";
} else {
    echo "Error updating record into table with error no: " . $mysqli->errno;
}
mysqli_close($link);

?>

After updating, you can use Toad to check if the record has been updated correctly.

toad_update

SQL: Update

This entry is part 14 of 20 in the series SQL Tutorial

Update allow us to modify data in a table.

In the achronym CRUD, we have looked at Create (insert), Retrieve (select).  Next in the list will be Update (update).

In sqlfiddle.com, we put insert statement on the left box.  The same goes to update and delete statement.

Syntax: update table set column = {value} where condition

create table artiste
	(
 id int auto_increment primary key, 
 artiste varchar(30), 
 album varchar(50),
 year int(10) 
 );

insert into artiste 
(artiste, album, year)
values
('Rod Stewart', 'Maggie May', 1973),
('New Order', 'Substance', 1987),
('Nirvana', 'Smell Like Teen Spirit', 1992),
('Rod Stewart', 'Out of Order', 1988);

update artiste set album='Every Picture Tells A Story' where artiste='Rod Stewart';

CAUTION:

If the where clause is omitted in the update command, ALL ROWS will be updated.

Hence, it is a good practice to issue a select query, using the same criteria, to check the result set before issuing the update. This also applies to the delete statement in the upcoming posts.

The SQL fiddle without setting the where clause can be seen here.