How to use PDO to UPDATE data in MySQL using PHP

You need to get data safely updated from a web page to a MySQL table in your database

You need to sanitize that data from user input and protect against SQL injections.

The following simple example will show you how to accomplish that.

These are the ingredients you will need:

  1. A MySQL database
  2. A table to store the data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A form with fields to input the data
  6. A way to sanitize the data
  7. Safely update the data

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to UPDATE data from your table.

First, to connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to save the data using an UPDATE statement:

$stmt = $dbh->prepare('UPDATE makes SET make = :make WHERE id = :id');

This prepares a statement which we can then bind the make parameters safely too, protecting against SQL injections.

We use PHP’s filer_input() function to santize the data coming from the input fields POSTed to the server from our form:

$id = filter_input(INPUT_POST, 'id', FILTER_SANITIZE_NUMBER_INT);
and
$make = filter_input(INPUT_POST, 'make', FILTER_SANITIZE_STRING);
Then using the bindParam() function we can bind the $id variable into the statement:
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

Then the $make variable can also be bound to the prepared statement:

$stmt->bindParam(':make', $make, PDO::PARAM_STR);

Then the statement is executed to run the prepared SQL statement, resulting in the record being updated in the table:

$stmt->execute()

These are the key lines to connect to the database. Sanitize the data and UPDATE the data in the database.

Of course the above code lines don’t show the HTML need to make the form and input fields.

You can find that here on github as a simple PHP PDO CRUD Demo. Please download / fork the code and try it out.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

This post is a part of a series, you can read more about viewing the data with PDO here

How to use PDO to DELETE data from MySQL using PHP

You need to delete data safely from a web page and your MySQL table in your database

You need to sanitize that data from user input and protect against SQL injections.

The following simple example will show you how to accomplish that.

These are the ingredients you will need:

  1. A MySQL database
  2. A table to store the data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A list of records displaying your data
  6. A way to select and delete a record
  7. Safely delete the data

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to DELETE data from your table.

First, to connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to delete the data using a DELETE statement:

$stmt = $dbh->prepare('DELETE FROM makes WHERE id = :id');

This prepares a statement which we can then bind the id parameter safely too, protecting against SQL injections.

We use PHP’s filer_input() function to santize the data and make sure it’s an integer. The GET parameter comes from the query string in the URL of the delete.php page:

$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

The $id variable can then be bound to the prepared statement:

$stmt->bindParam(':id', $id, PDO::PARAM_INT);

Then the statement is executed to run the prepared SQL statement, resulting in the record being deleted from the MySQL database table:

$stmt->execute()

These are the key lines to connect to the database. Sanitize the query string variable and DELETE the record in the database.

Of course the above doesn’t show the HTML need to make the form and input field.

You can find that here, on github as a simple PHP PDO CRUD Demo. Please download / fork the code and try it out.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

This post is a part of a series, you can read more about viewing the data with PDO here. Which generates the delete link to delete.php

How to use PDO to INSERT data into MySQL using PHP

You need to get data safely from a web page to a MySQL table in your database

You need to sanitize that data from user input and protect against SQL injections.

The following simple example will show you how to accomplish that.

These are the ingredients you will need:

  1. A MySQL database
  2. A table to store the data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A form with fields to input the data
  6. A way to sanitize the data
  7. Safely insert the data

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to INSERT data from your table.

First, to connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to save the data using an INSERT statement:

$stmt = $dbh->prepare("INSERT INTO makes (make) VALUES (:make)");

This prepares a statement which we can then bind the make parameter safely too, protecting against SQL injections.

We use PHP’s filer_input() function to santize the data coming from the input field POSTed to the server from our form:

$make = filter_input(INPUT_POST, 'make', FILTER_SANITIZE_STRING);

The $make variable can then be bound to the prepared statement:

$stmt->bindParam(':make', $make, PDO::PARAM_STR);

Then the statement is executed to run the prepared SQL statement, resulting in the record being added to the table:

$stmt->execute()

These are the key lines to connect to the database. Sanitize the data and INSERT in the database.

Of course the above doesn’t show the HTML need to make the form and input field.

You can find that here though on github as a simple PHP PDO CRUD Demo. Please download / fork the code and try it out.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes

This post is a part of a series, you can read more about viewing the data with PDO here

How to use PDO to SELECT data from MySQL using PHP example

You have a MySQL table of data in your database, you want to display it on a webpage, so it can be displayed and viewed by other people.

You need to have a few things in order to accomplish this:

  1. A MySQL database
  2. A table containing data in the above database
  3. The credentials to above (the host location, username and password)
  4. Which you can use to create a connection in your PHP page
  5. A way to iterate through the records so they can be echoed out
  6. The table field names so we can display them on the page

What if you don’t have all of the above set-up?

Don’t worry at the end will be a link to some example PHP source code along with the SQL statements to create the database and table.

PHP PDO (PHP Data Objects) makes it very easy to SELECT data from your table.

To connect to the database with PDO is very simple:

$dbh = new PDO('mysql:host=localhost;dbname='. $db_name, $db_username, $db_password);

The above line creates a connection and stores it in the $dbh variable as an object

That variable can then be used to retrieve the records using a SELECT statement:

foreach($dbh->query('SELECT * FROM makes ORDER BY make ASC') as $row) {

This allows us to use the resulting rows of data returned by the query.

Using the field names from the database table, we can output the data with an echo statement:

echo "<td>". $row['make'] ."</td>";

After we’ve finished, set the variable to null to free up the connection:

$dbh = null;

So with the above few lines of code, we can connect to the database. Retrieve the data and output it.

To make everything look good, we need a few more lines of code to create a table.

The full source code is available here on github as a simple PHP PDO Crud Demo.

The demo is a simple CRUD example of using PHP, PDO and MySQL to display a list of car makes