When do you need to use back ticks with MySQL?

Back ticks are often misunderstood.

I am trying to learn the best way to write queries.

You might not be sure how to use those funny little `ticks

`back ticks`, `backticks`

I have used single quotes, double quotes and back ticks without any real thought.

What is the standard for this?

Back ticks should be used for table and column identifiers, but are only needed when the identifier is MySQL reserved keyword.
Also if the identifier contains white space or other special characters.

When possible it’s recommended not to use keywords and special characters as table and column identifiers to avoid having to use back ticks.

If you use PHPMyAdmin to generate some queries you will see that all the column and table identifiers are escaped with back ticks by default.

For example a SELECT query would look like this:

SELECT `id`, `created_by_user_id`, `modified_by_user_id` FROM `products` WHERE 1

As none of these column and table identifiers contain any special characters and don’t match any of the MySQL reserved keywords, the same query will run just fine without the back ticks:

SELECT id, created_by_user_id, modified_by_user_id FROM products WHERE 1

Replace the back ticks with single quotes (or double quotes) and you will get an error as now each identifier wrapped in single quotes is treated as a string:

--DON'T DO THIS, AS IT WILL RAISE THE ERROR BELOW:
SELECT 'id', 'created_by_user_id', 'modified_by_user_id' FROM 'products' WHERE 1
--#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''products'

If you liked this tip, be sure to subscribe below and be the first to get my next tip…

How to use CodeIgniter to prevent malicious SQL injections

Should you escape input when using CodeIgniters active record class?

Do you need to worry about MySQL injection vulnerabilities when using CodeIgniter?

Yes, yes you do.

It is of course good security practice. To escape your data before submitting it into your database.

CodeIgniter provides three helpful methods. Which are part of it’s database library to stop SQL code injection.

$this->db->escape()
$this->db->escape_str()
$this->db->escape_like_str()

CodeIgniter also provides a method to to use Bindings. Whereby the question marks are replaced by the data parameters. That you pass to the query function.

For example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

This way they are escaped so you don’t have to escape them. Resulting in safer queries for you. Stopping attackers from injecting malicious SQL.

Refer to the last two sections of this page of the CodeIgniter manual.

If you liked this tip, be sure to subscribe below and be the first to get my next tip…

MySQL terminology guide for newbies

MySQL Terminology helpful guide

If you are newbie to MySQL, this guide explains some terminology that is used when developing with MySQL and PHP.

This is not meant to be a definitive guide, but will be updated. Did I miss something? Please let me know or leave a comment below.

Aliases for column names Produces a more readable column name if needed. Especially when a column is produced from a function like CONCAT() for example
Aliases for table names Makes queries shorter and easier to read / write
Backticks ` MySql’s (non-standard) method for delimiting problematic identifiers
Composite Index An index made of more than one column.
Database A collection of one or more tables. Think of a Excel workbook containing many sheets.
MySQL client Software to allow to connect and manage MySQL databases. MySQL Workbench, HeidiSQL, phpMyAdmin etc…
ORDER BY clause Used to sort the results of your SELECT query by one or more columns in ascending or descending order.
Primary Key A unique identifier of a row consisting of one or more columns
Query A way of SELECTing / INSERTing / DELETEing / UPDATEing data
Results / Result set The data / result of a query being run
Row / Record A single row or record of data in a table
Schema The design of one or more tables
SQL Structure Query Language
Sub query A quested nested within another query
Table A repository of data (entity). Think of a sheet in Excel with column headers.

If you liked this guide, be sure to subscribe below and be the first to get my next post…

How to replace NULL values in a MySQL SELECT statement

Sometimes when you are SELECTing data from MySQL, some of your columns may have NULL data values and NULL’s don’t display well to end-users, so it may be best to replace the NULL’s with a string of text instead such as:

  • “Not defined”
  • “Undefined”
  • “Default value”

You could do this in PHP when you are echoing out your data, but it’s a lot easier to do it your MySQL statement.

For example if you have this simple SELECT statement, but not every manufacturers website is defined, so that the website column value is sometimes null.


SELECT id, manufacturer, website
FROM manufacturers

Then you can simply use the COALESCE function to check for and replace any NULL values with ‘Not defined’.


SELECT id, manufacturer, COALESCE(website, 'Not defined') AS website
FROM manufacturers

Also I’ve given the same column name as an alias to the function so your resulting recordset will have the same column header as your first query.

Did you find the above helpful? Please leave a comment below and let me know…

When to use USING in a JOIN rather than JOINing ON?

What is the difference between ON and USING() when JOINing two or more tables together in a SQL query?

For example you have the following two tables, Users and Products, when a Product is created, the Users id that created it is stored.

Products table:
id Primary Key
created_by_user_id Foreign Key to Users table
product_name
Users table:
id Primary Key
first_name
last_name

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

SELECT p.*, u.*
FROM products p
INNER JOIN users u ON p.created_by_user_id = u.id

If however the user id field was named the same in both tables like this:

Products table:
id Primary Key
user_id Foreign Key to Users table
product_name
Users table:
user_id Primary Key
first_name
last_name

You can then use USING to JOIN the tables together and return the same data with this SQL:

SELECT p.*, u.*
FROM products p
INNER JOIN users u USING(user_id)

Each time you JOIN a table you will need to use an additional USING clause even if the second and third tables contain the exactly same column name.

Make sure that when you are JOINing tables together using the ON or the USING clause that the data type of the matching columns is the same data type.

Interested in more SQL tips? Please leave a comment below or pop your email in the box below and you’ll be sure to receive them!

How to join the same table twice in a single SQL query statement?

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

You might not have even thought it was possible to have multiple joins to the same table.

It is possible and easy to do with the help of aliases.

Aliases for table names

Aliases allow you to reference the table name with an abbreviation.

For example if you have a table called “products” then you reference that table by creating an alias of “p”.

With MySQL you easily create an alias like this:

SELECT p.id FROM products p

Multiple joins to the same table can quickly create a messy SQL statement. If you don’t use aliases they can become difficult to read.

Alias SQL example:

If your database tables need to have two or more columns with the same relationship to another table. You can easily join them with the help of 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.

Products table fields: Comments
id Primary Key
created_by_user_id Foreign Key to Users table
modified_by_user_id Foreign Key to Users table
Users table fields: Comments
id Primary Key
first_name
last_name

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

In this example, we use the aliases p for the products table and u for the users table:

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

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

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 reference the same table.

In this example, we use the aliases u1 for the first reference and u2 for the second reference:

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 pop your email in the box below and you’ll be sure to receive them!

MySQL phpMyAdmin Tutorial, creating tables and joining tables together

Check out my first video tutorial on YouTube.

I’m demonstrating MySQL phpMyAdmin, to get you started creating a database, creating and populating two MySQL tables and a simple SELECT statement showing how to join the two tables together.

The text size does look a little small, so the next video will be zoomed in to make it easier to see, stay tuned for more.

If you have any questions you would like answered in a video, just leave them in comments below.

Thanks,

Jon

GUI clients to help you manage a MySQL database

How do you manage a MySQL database through a GUI?

What client should you use for managing remote MySQL?

There are many options, I’ve almost always used phpMyAdmin and found it good enough for most common tasks. I’ve needed to use the console for loading large files of data for example. You can find other options out there listed below:

Which one do you use, do have a favorite GUI for managing MySQL databases? Let me know in the comments below…

If you liked this, be sure to subscribe below and be the first to get my next post…

What’s better for newbies to learn first PHP or MySQL?

It’s a good question for newbies to have, where to start?

What comes first PHP or MySQL?  The chicken or the egg?

You can learn both independently, but if you have no experience with either, I think it’s best to start with PHP.   Pretty quickly as you work through tutorials you will encounter MySQL.  They really go hand in hand for many applications.

If you have zero experience, there are other things you will need to learn also if you haven’t already…

  • HTML
  • CSS
  • Javascript / JQuery

How to join three MySQL database tables together in a simple SELECT statement

Following on from my post of how to join two MySQL tables together, adding a third table is just as easy, but if you need an example this one extends the previous post by adding a third table.

For example I have a table of products, table of product categories and table of product suppliers, how to return the category description of each product and the supplier of the product?

Products
———–
id
category_id
supplier_id
product_name

Categories
————-
id
description

Suppliers
—————
id
supplier_name
supplier_address

In it’s simplest form you would join three tables based on the relationship between a key and it’s foreign key stored in another table:

SELECT p.id, p.product_name, c.description AS ‘cat_description’, s.supplier_name
FROM products p
INNER JOIN categories c on p.category_id = c.id
INNER JOIN suppliers s ON p.supplier_id = s.id

Using a well designed relational database and JOINed queries will make your PHP code simpler and more efficient by returning the data from two or more tables in one query.

Like this post? please leave a comment below: