Tutorials MySQL PDO Insert Update Delete

PDO Insert Update Delete

This time you will learn how to insert, update and delete rows using PDO. Prepared statements quote and escape strings for you so there's no worry about SQL injections.

Insert a new row

To insert a row, you need to have all the data first. You can use PHP variables to fill in data. When you insert the data, the order has to be the same.

$array = array(
  'name'  => 'Mike',
  'phone' => 1234567890,
  'email' => 'mike@mail.com'
);
$sql   = 'INSERT INTO table (name,phone,email)
  VALUES (:name,:phone,:email)';
$result = $link->prepare($sql);
$result->execute($array);

Update an existing row

To update a row, you use the update query.

$array = array(
  'name'  => 'Mike',
  'phone' => 1234567890,
  'email' => 'mike@mail.com'
);
$sql = 'UPDATE table
  SET name = :name, phone = :phone, email = :email
  WHERE id = 10 LIMIT 1';
$result = $link->prepare($sql);
$result->execute($array);

Delete an existing row

To delete a row, you have to use the delete query.

$sql = 'DELETE FROM table WHERE id=10 LIMIT 1';
$link->query($sql);

Filtering MySQL results

The WHERE clause is as important to MySQL as the IF function is to PHP. It compares the data. If you don't want to grab the whole table, you need to use WHERE. If you need to manipulate specific rows, you have to use WHERE to choose which ones. You might also want to add a limit to your query. Adding a limit will tell MySQL to stop after the quota has been reached. Unless you want a mass purge, you will want to refine your results.

That's all for today. Try to fill up your database. Next lesson will be how to grab information from your database.

Posted by on . Category: MySQL


Comments

No comments posted yet

You need to register or login to post new comments.