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
Users table:
id Primary Key

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 =

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
Users table:
user_id Primary Key

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!

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'];
$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 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 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.