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

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

More than one join can quickly create a messy SQL statement. If you don’t use aliases they can quickly become difficult to read.

If your database tables need to have three 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. Finally a Users id is recorded to approve the changes.

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
approved_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:

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:

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

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

SELECT
p.id
, u.firstname AS 'approver_firstname'
, u.lastname AS 'approver_lastname'
FROM products p
INNER JOIN users u
ON p.approved_by_user_id = u.id

But how can you combine all three queries to know the usera that created, modified and approved the products in one query?

Just join the Users table three times, but you need to use a different alias each time you JOIN 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'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_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
INNER JOIN users u3
ON p.approved_by_user_id = u3.id

But what if a product has only been created and modified but hasn’t yet been approved? In which case the approved_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 a 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'
, u3.firstname AS 'approver_firstname'
, u3.lastname AS 'approver_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
LEFT JOIN users u3
ON p.approved_by_user_id = u3.id

So now you can join the same table three times with aliases 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!

How to use a local fallback in the event a CDN isn’t available for jQuery

Worried about using a CDN?

  • Worried about a CDN being down?
  • Concerned a CDN might slow your site down?
  • Thinking there must be a down side to accessing files from a CDN?

Fear not a CDN (Content Delivery Network) is a globally distributed network of servers deployed in many data centers. Giving you several advantages:

  • A CDN is unlikely to be down, after all, up time is one of the big selling points of CDN.
  • Decreased latency so that a user accessing your site will be able to download the content faster
  • Increased parallel connections can be made. Again increasing the speed your content and site can download to a users browser
  • Cross-site caching if other sites your users have visited also use the same CDN. jQuery may already be cached in their browser

If that hasn’t allayed your fears? Then you can always use a fallback for a local version of jQuery like this:

If you found this post useful, please subscribe below. If you have any questions, don’t hesitate to leave a comment…

How to integrate a WordPress.com blog RSS feed into a PHP page

What comes first your wordpress blog or your website?

You might have started with a free wordpress blog. Then later created a separate website or vice versa. Well whichever way round it is. You might have ended up with two disconnected websites. Now you want to reuse your wordpress.com blog content in your website.

One easy way to do this and integrate your blog content in your website is using its RSS feed. Which is available on your wordpress blog. Just add this query string “?feed=rss” to your blog’s url and you will get an RSS feed from your blog.

Try it now in your web browser, by clicking on this link:

http://element121.com/?feed=rss

Now that you can access the RSS feed, you can parse the RSS data into your page

Last RSS is a PHP RSS parser. Available to download as a single file here: http://lastrss.oslab.net/

So you can now parse the RSS feed. With a few lines of PHP code as demonstrated below. This is based on one of Last RSS’s examples.

If you found this post useful, please subscribe below. If you have any questions, don’t hesitate to leave a comment…

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!

Help for avoiding undefined index notices

If you are just starting out with PHP, you may come across this message:

Notice: Undefined index: username in C:\wamp\www\mysite\index.php on line 99

Even if you’ve been coding for years you can still get this message!

You may be confused as to what an “Undefined index” is? Often seen when trying to access global server variables such as $_POST and $_GET when passing data from another page or posting it back to the same page.

So what is it? It really is an index of an array that hasn’t been defined yet.  You are trying to access data in a variable that doesn’t exist!

Depending on the PHP error reporting level you configure, you can even hide these notices, but that’s not recommended, better to deal with them and write good code.

You can normally fix your “Undefined index” errors with the isset function to check whether a variable is set, like this:

// Check if the username is set or not...
If (isset($_POST['username']))
{
$username = $_POST['username'];
}
else
{
$username = "";
}

However a very useful function which can save you many lines of code like the above and undefined index notices, can be found in the comments (all credit to the author) on the same page of the PHP manual.

function isset_or(&$check, $alternate = NULL)
{
return (isset($check)) ? $check : $alternate;
}

//Example usage which will give the same result as the if / else statement above
$username = isset_or($_POST['username'], "");

It’s a very useful function if you are using plain PHP and need to determine the value of variables. It will also make your code cleaner and easier to read than repeated if / else statements like the above.

How to stay productive when traveling and working as a Web Developer

Working remotely and traveling is a balancing act of maintaining your productivity whilst keeping up to date on your work.  Keeping your clients / customers / boss happy and continuing to earn money at the same time as enjoying your travels to new cities, countries and continents.

Traveling and unexpected delays in traveling along with possible slow or no internet connections can affect your work routine.

Although there can be distinct advantages to no internet connection for periods of time whilst actually traveling on a train or a plane.

I do some of my best writing and coding on trains.  Flights can also be nice in that, without WiFi, i’m not interrupted by client emails and calls.

If you know you won’t be connected for any reason, make sure you communicate that to any clients, so you can set their expectations that you won’t be available for a period of time.

Try to adapt as quickly as possible to any new time zone and maintain your routine, if you are a morning person, get up and get the work done first, so that you can take time off in the afternoon to explore your new surroundings.

Don’t plan to stay anywhere less than a week, you need time to sight-see, relax, do other tasks.

Here’s a useful list of other ways to stay productive whilst traveling:

  • White noise can help in a new or distracting environment.
  • Manage your time better with the Pomodoro technique to get the work done.
  • Always look for efficiencies in your doing your work, saving time will either earn you more money if you need it or earn you more free time to enjoy your surroundings.
  • Taking regular breaks and exercise reduces your stress levels and helps maintain your productivity.

How do you stay productive? please add a comment below…

How can I forward calls and SMS messages from one country to another?

Working remotely in different countries and want to keep a phone number active in your home country?  Travelling and needing to re-direct your calls and forward SMS text messages?

There can be times when you might not want your clients to know you are on the road or abroad in a foreign country.  To be able to keep the same number and move around internationally.

These days technology easily allows you be free to travel, but stay connected to your numbers.  You can also have multiple numbers in different countries if needed.  Along with toll-free numbers you can also send and receive SMS and MMS messages whilst away without incurring international roaming charges on your cell / mobile phone.

I’d just like to be able to call and text like I’m still in the US (when I’m not)

For a few dollars, Twilio provides all the phone technology to make it easy to stay connected with voice and messages.

All major countries are covered well with Twilio and there are lots and lots of features available beyond the basic ones like recording calls, automated calls and interactive SMS.

Interested in receiving more helpful posts like this, please subscribe or leave a comment below…

How to get paid, when you work remotely from your client as a Freelance Web Developer?

If you are working remotely from your client there is a good chance you are in a different country.  Payment providers charge fees and use favorable  (for them) exchange rates and banks love to charge fees whenever they can.

There are a lot of options out there, a favorite for many is PayPal, but…

“It really hurts having PayPal take a 7% combined fee on all my earnings from the initial payment and the currency conversion.”

There is IBAN and SWIFT, but often banks charge the sender to send and for the receiver to receive the funds and use their exchange rate in the middle of money sending sandwich.

But now TransferWise is claiming to be the:

“THE CLEVER NEW WAY TO BEAT BANK FEES”

They have a nice simple calculator on their home page, so you can quickly determine the fees involved and the exchange rate without reading any fine print, check it out at: https://transferwise.com/

“TransferWise is really good, I’ve had funds in my account in under a day.”

How have you found TransferWise? Please leave your comments below.

How to use PHP Sessions?

Need help with a simple PHP session tutorial?

Wondering what you should store in a session?

You are in the right place for a fun example:

If you are learning PHP, sooner or later you will have a need for using sessions.

Sessions allow you to maintain the state of an application between user requests.

They are useful when a user logs in to your site. So that you can recognize on each page that the user is still logged in as they navigate the pages of your site.

Once you understand how sessions work you might be tempted to store lots of data in the session. However sessions should generally be used to identify a user. Other data is saved in the database which is linked to the user / session.

Every PHP page which needs to access the session data will need to call this function first:

session_start();

You can then store and retrieve data in the session:

// To store a number in the session
$_SESSION['number'] = 911;
// To retrieve the store number
echo $_SESSION['number'];

The following in simple example of storing and retrieving sessions. This is a game of “Higher or lower?”, where you try and guess a number between 1 and 100. The code picks a random number and stores in it in the session when the game is started. Then as you attempt to guess, the cumulative number of guess is also stored in the session. When you win and correctly guess the number. It will retrieve the number of attempts from the session and display it.

Link to demo http://trabest.com/higher-or-lower.php
Link to github repo with the code https://github.com/element121/higher-or-lower

Try it out, the best number of guesses so far is 3!

Please leave a comment below and let me know what you think. If you any have questions, feel free to contact me.