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.

Want all the SQL statements from above and the CREATE table statements?

Grab the code and try it for yourself

New to MySQL, read this blog post… MySQL terminology guide

Found this helpful?

Signup below, for more helpful tips.

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 reduce physical stress when working as Web Developer?

If you are new to web development, sitting down all day might seem like a cushy easy job, and  it can be.  However as time and the years pass by, all that sitting will take it’s toll.  It’s easy to slouch, it’s easy to forget about your posture and easy to grab another sugary snack to keep you going in that mid-morning or mid-afternoon low point.  Whatever stage of your career you are at you should be mindful of all that time you are paid to sit on your chair in the office.

How to counter all that stagnant sedentary time sitting? The answer is real simple and just the opposite…. movement!

Make sure you take regular breaks from sitting still, at least every hour, if not more.  If don’t have a good chair, buy one or ask your manager or office about replacing your chair.  These days there also standing desks and variable height platforms, so you can adjust the height of your keyboard, mouse and monitor to suit your stature.  If you do have a standing desk you can add a special treadmill, so that you can slowly walk whilst working.

“I’ve been standing at my desk for 2 years now, and all lower back problems are now gone.”

Personally when I started working as a Web Developer in an office my back was strong and it was easy to slouch, over the years though it’s definitely weakened my back and caused me pains down my legs, but after seeing a specialist a second time I bought a variable height desk and every morning start the working day by standing up.

Taking regular breaks, moving around, walking to the washroom, walking outside, going up and down the stairs all helps by moving to combat all the years off sitting.  Often companies, managers and some colleagues all seem to think that more is more, the more you are working, the more will get done and that taking a break is not working while everyone else is.  For me the opposite is true, the more I sit the less productive I become, taking a break and moving about is refreshing both physically and mentally as well as helping to solve problems.  Less (continuous) work is definitely more (discontinuous) work.

So make sure you take a break for your body, your mind and your work!

How do you take yours? Please leave a comment below…

 

How do you deal with stress as a web developer?

Life can be stressful enough, work as a web developer has it’s own unique stresses from dealing with users who don’t know what they are doing, managers who don’t know what they are managing and technology that never stands still.

At times and some days at multiple times in the day the stress can feel too much.  Sometimes you feel like you need to release the built up angst, anger and frustrations.  Venting and releasing the stress in a controlled manner is important for you and everyone around you.

There are times when you can spend hours trying to fix a bug, that can turn out to be a single character (often invisible) that’s out of place causing a bug and stress.

There are lots of options for dealing with stress, but one that is free and should be available to almost anyone, is just getting up and going for a walk.  Preferably in natural surroundings, if you are in a city, try and walk around a nearby park.

Take a break, when you are stuck go for a walk.  Disconnect from your problem, when you come back after your 10-20 minute walk, you will have a fresh perspective.  I do this all the time and it is crucial to my productivity.

It’s amazing how bugs can be solved, by just leaving the code alone, getting outside for a walk for 10-20 minutes and then when you come back, you have the fix and the stress is gone!  Often I find the fix will come to me halfway on the walk, or when I’m on my way back to the office.  When you are desperately trying to fix an urgent problem, it’s amazing how time can fly by, so stop, get up and take a break with a walk and let the solution come to you, while the stress dissipates…

How to protect your PHP source code as a freelancing web developer

How to protect your PHP source code?  You might be worried about giving away your secret sauce  or having it reverse engineered.

Another good reason for wanting to protect your code is delivering the code to a clients server / hosting before final payment has been made for the development of the code.

I have experienced this first hand! I was very surprised to find a delivery of code for sign off was encrypted, I hadn’t seen that before.  Sure enough when we asked the development company they said they would remove the encryption once the final payment had been made (there already had been a number of part payments through the project).

So definitely a useful tool for small shops and freelancers, make it a part of your contract that the source code will be decrypted when fully paid for.

We also had another client who wanted to distribute PHP code on USB thumb sticks.  Not recommended, but it is possible to run a web server like that, using a SQLite database.  Given the accessibility of a USB thumb stick rather than a Linux server, we encrypted the PHP code to protect it.

Here’s a couple of PHP encoders that we’ve used:

So encode, obfuscate and protect your code, if you feel the need, especially if you aren’t 100% confident of a client paying you.

Have you experienced a similar situation before, please share below in the comments.  If you would like more useful tips like this, please subscribe to be updated on new ones.

Why you should keep a routine when working remotely as a freelancer

Transitioning to freelancing and working at home or at a remote location from your client can run riot over your life…

I find myself working late, and then when I get in bed my brain is still active and I cannot sleep, so I end up going to bed at 1 or 2 in the morning, which has an impact on the next day…

Being home, can feel like, well being at home! Where you normally relax, spend time with family, eat and sleep. Working at home might be something new for you.

For me it can be tough getting into a working mindset in the morning and getting out of it in the evening.

Of course you don’t have to just work at home.  There’s always libraries, cafes, co-working spaces or you could even rent an office, just like other businesses do.

Whenever I am feeling the cabin fever of working from home, I hit the library.

There can be lots of distractions, so make sure you have a regular routine for yourself, to keep your productivity up and that way you know when you are at “work”.  Turn that daily routine into a habit so that it becomes second nature.

If you’ve transitioned from a regular office job to freelance position, try keeping the same 9-5 hours you had before.

What works for me these days is I go to bed early and start early, when the clock hits 5pm I’m done for the day.

Stick to your routine, plan your days out and remember to stop working and don’t let your work overrun its routine and affect family life and your sleep. Routine sounds routine, a little boring perhaps, but that’s how you get things done, sticking to the routine, doing the work, taking a break and doing more work day in and day out.

 

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.

Why you should track your time for you and your clients

My client wanted proof that I worked the amount of hours that I said I did…

What’s the best way to prove you worked for the amount of hours you said you did on what you were meant to be working on, on which day?

You are taking the time to record your time? Right?

There are lots of advantages to logging your time…

  • You can remember where the time went and review your productivity.
  • You have proof to show your client, if they do question it.
  • But you should be sharing your timesheets with your client anyway for a good transparent relationship.
  • Knowing how long something took, will help with making more accurate estimates in the future.
  • Invoicing for your time should now be super easy.

Of course you can use good old pen and paper, I’ve used notepad to track time when I was working exclusively for one client.  There’s always excel / spreadsheets.  Now though there are a lots of online time tracking apps and tools with lots more additional features and integration’s than you get with a simple spreadsheet.  All of which should increase the speed with which you can invoice clients for your time, saving you wasted time and maximizing your productivity.