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' => 'email@example.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' => 'firstname.lastname@example.org' ); $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.