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

How to check a number is valid when a form is submitted to your PHP code?

You have a form on your website and you are collecting numbers

You want to make sure they are valid numbers.

How do you make sure they are valid numbers?

First, you can validate them client-side in the browser, before the form is submitted to your server

With HTML5 you can define the input field type as email and make it a required field, like this:

<input type="number" name="email" required>

Example of how the number input looks

The number input also supports a range of acceptable numbers, so if you only wanted to numbers between 1 and 10, you would set the min and max attribtues like this:

Example of how the number input with a range looks

<input type="number" min="1" max="10" name="number" required>

Note that most of the frequently used browsers will support the above functionality.

Validating the input client side is one thing, but you have to validate it on the server also.

You can use this PHP code, to make sure that submitted number is really valid:


// Grab the POSTed number input field
$number = $_POST['number'];
$number_range = $_POST['number_range'];

// Use filter_var to check if the number is valid
if (filter_var($number, FILTER_VALIDATE_INT) === false) {
echo("$number is not a valid number");
} else {
echo("$number is a valid number");
}

// Use filter_var to check if the number is valid and within the range
if (filter_var($number_range, FILTER_VALIDATE_INT, 1, 10) === false) {
echo("$number_range is not a valid number");
} else {
echo("$number_range is a valid number");
}

For a bare bones code sample, demonstrating the above you can find it below:

If you found this post helpful, please subscribe below for more.

How to check an email address is valid when a form is submitted to your PHP code?

How to check an email address is valid when a form is submitted to your PHP code?

You have a form on your website and you are collecting email addresses

You want to make sure they are valid email addresses, so that you can make use of them

Whether you are replying to a customer inquiry

Or using the email addresses for a marketing mailing list

How do you make sure they are valid email addresses?

First, you can validate them client-side in the browser, before the form is submitted to your server

With HTML5 you can define the input field type as email and make it a required field, like this:

<input type="email" name="email" required>

Note that most of the frequently used browsers will support the above functionality.

Then once your form is submitted you can use this PHP code, to make sure that submitted email address is really valid:


// Grab the POSTed email address input field
$email = $_POST['email'];

// Use filter_var to check if the email address is valid
if (filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
echo("$email is not a valid email address");
} else {
echo("$email is a valid email address");
}

For a bare bones code sample, demonstrating the above you can find it below:

How to call a PHP function (with parentheses)

How to call a function in PHP

A reader of my blog recently emailed me, asking me to check over some PHP code

Nothing works from my php file except just connecting to the database

It’s simple code – I’m not doing anything fancy

So here’s the PHP code supplied:

function getConnection( ) {

$host="host";
$port=3306;
$socket="";
$user="me";
$password="mypassword";
$dbname="downloads";

@ $con = new mysqli($host, $user, $password, $dbname, $port, $socket);

if ( mysqli_connect_errno()) {
echo "Connection function failed";
return false;

} else {
echo "Connection function succeeded";
return $con;
}
}

$res = getConnection;
if (!$res) {
echo "Connection failed. " . mysql_errno($res) . ": " . mysql_error($res). "\n";
}
else {
echo "Connected to database." . "\n";
}

$key = md5(microtime());
if ($result = $res->query("SELECT count(*) FROM downloads WHERE downloadkey = '{$key}' LIMIT 1")){
echo "Select succeeded." . "\n";
}
else
{
echo "Select failed." . "\n";
}

They helpfully supplied output and the error message too:

Connected to database. Fatal error: Call to a member function query() on a non-object in /path-to/filename.php on line 33

Which seems to suggest to the connection is fine, but when it comes to use the connection. The connection object isn’t actually an object.

They are using a function to create the database connection, a good sign.

There are a few lines after the function, they all look okay

But wait, maybe something doesn’t look right about this line:

$res = getConnection;

Notice anything missing?

Does it look better like this?

$res = getConnection();

Yes! Calling a PHP function, even though that function has no parameters, you must include the parentheses!

Otherwise PHP doesn’t know it’s a function and actually assumes getConnection is a string. As demonstrated by this snippet:

$res = getConnection;
var_dump($res);
// Outputs: string(13) "getConnection"

So that was the first problem, the reader assumed the connection was being created.

When in fact the function was never being called.

Even though they have code to check the connection.

However, that code:

if (!$res) {

Was never going to equate to false, as $res was actually a string “getConnection”.

That if statement should be changed to this:

if (!is_object($res)) {

Now we are actually checking whether the $res variable is actually a connection object or not!

How to add a subscriber using MailChimps API with PHP

How to add a subscriber using MailChimps API v3.0 with PHP and CURL

A quick step-by-step guide with PHP code

Here was the problem, my greeting card website (PamitCards.ca) has a list of subscribers sitting in a MySQL database table. Whenever I wanted to use MailChimp to send the list of subscribers an email. I would have to export the email addresses as a CSV file. Then log-in to MailChimp, import the file so that any new subscribers would be added. MailChimp’s import process would then email me with a list of additions, bounced and un-subscribed email addresses. I would then take those and update the MySQL database table removing the redundant addresses.

Whoa that’s a lot of steps, quite a time consuming process for something that could be automated / integrated!

Realizing that using MailChimp’s API, I could add subscribers to my MailChimp list. Thereby making the above process redundant along with the database table. What’s the point of keeping that data if it’s in MailChimp.

So here’s how to make it work…
  1. If you are reading this, I’ll assume you already have a MailChimp account and at least one list set-up. If not it’s really easy to sign-up and get started with MailChimp.
  2. Then you will need an API key if you don’t have one already, find it under Account > Extras > API keys. Note that running the code locally under WAMP I had to change the https to http to make it work.
  3. Once your key is generated, you will use the last 3 characters as part of the API call, in my case it was us2 to make this url https://us2.api.mailchimp.com/3.0
  4. Then you will need to generate your Auth token, I used Postman which is available in the Chrome Web store to add to your browser.
  5. Using Basic Auth you can put in your Username and Password and this will generate a string that looks like this Basic cGFtaXRjYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXZGUwNS11czI=
  6. First do a GET call to http://us2.api.mailchimp.com/3.0/lists this will return a list of your lists, find your relevant list id to insert in the code below along with your API key
  7. Then specify an email address to Subscribe and run the code.
  8. You should get a response in the browser showing the the email address has been added
  9. You can extra fields if your list and signup process has them.
  10. Any problems, please let me know with a comment or follow me on Twitter for more helpful posts…
  11. Here’s the PHP code I used:

7 tips for using a standing up desk

Interested in getting a stand up desk?

If you are used to sitting at your desk, here are 7 tips to help you get started with a standing desk.

You can download a print ready PDF of the tips

  • Choose a desk with variable height, such as a Varidesk
  • Make sure you take breaks from standing
  • Wear flat shoes
  • Shift your body position / weight
  • Will your desk be able to support both your monitors?
  • Start your work day standing up
  • It may take a while to get used to it, as with anyting new, but keep using it

Read more about the reasons to get a stand up desk here

7 Advantages to using a standing up desk when working as a developer

Are you spending all your work day sitting writing code?

Is your back feeling stiff or causing you some pain?

Maybe it’s time to consider getting one of those new hip standing desks?

Here’s 7 reasons that might help convince you a standing desk is worth it.

You can always try one out with just cardboard boxes…

Also check out the link at the bottom to download a print ready PDF of the advantages

7 Advantages FOR STAND UP DESKS
Download as a PDF – 7 Advantages for stand up desks

How do you sit and code for so damn long all day, every day?

How do you sit and code for so damn long?

I feel like my wrists are going to snap off and my back/neck is deforming.

Your day goes something like this, as it does for a lot of people…

  • Wake up…
  • SIT in your car and drive to work
  • Or SIT maybe stand on public transport work
  • Get to the office and SIT down
  • Go for a coffee break and SIT down
  • SIT back down at your desk
  • Eat lunch SITTING down
  • SIT back down at your desk
  • Head home probably SITTING down
  • Get home and SIT down to watch some TV / Do more work / Eat
  • Repeat 5 day a week.

Sitting all day is harmful to your health, and you can’t fix it by exercising.

So what’s the answer if it’s not exercising?

Sounds obvious, but it’s trying to sit less…

That’s easy to say except when your paid to SIT for your day job…

What you need is a variable height stand up desk!

After years of development sitting down and enduring back pain I decided to get one.

I didn’t want to spend the money on a piece of office furniture it and it wasn’t covered by medical insurance.

My company wasn’t going to pay for it.

You might be lucky enough to work for a company that will do.

At the end of the day I decided a few hundred dollars for a standning desk versus back pain was a no brainer!

I wanted to live without the pain.

We have standing desks, the first couple of weeks were hell though.

And yes after years of sitting, to be standing at your desk, does hurt, it’s not easy.

But you do get used to it.

You stand sometimes and you sit sometimes as opposed to sitting all day.

I start the day off standing up and as the day goes by end up sitting more.

Sitting every so often, good shoes and a padded mat also make a big difference.

I find flat heeled shoes help for standing and working at the desk.

You’r standing in place, sure, but you aren’t usually as immobile as you would be if you were sitting. You shift your weight around, you’re also more inclined to walk away from your desk.

I switched to a standing desk a few months ago. Over the first month, the back pain decreased until it vanished completely.

For more healthy tips, please subscribe below…

How to INNER JOIN on the same table twice in SQL statement

Having more than one INNER JOIN to the same table can be confusing.

More than one INNER JOIN can create a messy SQL statement.

If you don’t use aliases they can become difficult to read.

So join them with aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored.

If you want to find out who created the product, you can do that with a simple SQL query like this:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

Table users is aliased as u, products as p.

Then you can prepend the alias to SELECT a field from a table.

p.id is equivalent of writing products.id

If you want to find out who modified the product you can do that with a similar query:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

But how can you combine both queries to know the user that created and modified the products in one query?

Just join the Users table twice, but you need to use a different alias each time you use the same table:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id

But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
LEFT JOIN users u2
ON p.modified_by_user_id = u2.id

So now you can join the same table twice in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please subscribe below and you’ll be sure to receive them!

Or follow me on Twitter