Connecting to a remote MySQL database via SSH tunnel with local phpMyAdmin

I got tired of working with unfamiliar and slow database tools and wanted to use my old favourite tool, phpMyAdmin on my local machine to connect to a remote database, that I can only access via an SSH tunnel. First, you need to create the SSH tunnel, of course

ssh -fNL 3307:localhost:3306 user@host

This makes the tunnel to user@host to port 3307, which you can then access as if it is local in phpMyAdmin. Next you must set up phpMyAdmin to show a dropdown to multiple servers:

  • Open in your phpmyadmin installation folder.
  • Create a $hosts array that contain all the info between all the servers that are different. For now, we will only specify a few items. Adapt to your liking.
$hosts = array(
     'local' => array(
          'verbose' => 'Verbose display name',
          'user' => 'blah',
          'password' => 'blah',
          'port' => 3306,
          'host' => '',
          'auth_type' => 'http'
     'remote1' => array(
          'user' => 'etc'
     'etc' => array(
          'user' => 'etc'

With the various server configs defined, you can simply edit your file as follows:

$i = 0;
foreach ($hosts as $k => $v) {
    $cfg['Servers'][$i]['verbose'] =$v['verbose'];
    $cfg['Servers'][$i]['port'] =$v['port'];
    $cfg['Servers'][$i]['user'] =$v['user'];
    $cfg['Servers'][$i]['password'] =$v['password'];
    $cfg['Servers'][$i]['host'] =$v['host'];
    $cfg['Servers'][$i]['auth_type'] =$v['auth_type'];
    $cfg['Servers'][$i]['favorite'] ='pma__favorite';


Excel, CSV and SYLK

Anyone who often works with CSV exports programmatically may have had this issue before, and I am sure that you may have been confused about what is causing it. You may have gotten one of the below, or both.

Error message one:

Excel has detected that ‘exported_file.csv’ is an SYLK file, but cannot load it. Either the file has error or it is not a SYLK file format. Click OK to try to open the file in a different format.

Error message two:

SYLK: File format is not valid

The good news is that it has a very simple solution:

If the first letters of the file is “ID”, you will get this problem. In my case, I had to rename the column header from “ID” to “Record ID” to solve the problem.

View the solution directly on Microsoft’s website. They suggest that you can also change “ID” to “id”. For me, that was not an option; I am pedantic about writing style in my reports. There is also a suggestion to add an apostrophe. Also viable, but my solution works easier for me. Do whatever works best for you!

Replace the value of one column with a value from another column in MySQL

Let’s say that you need to import a large set of student data together with the campus (string), faculty (string) and course information (string).

Your main table, let’s call it `students` must have ID links to the separate tables, `campuses` (ID), `faculties` (ID) and `qualifications` (ID).

Your problem: you received the data in a single flat file, where each student record is in a line – thus, non-normalized data. How do you go about this? Here is a way:

  1. First import each separate sub-table (if I may call it that), for example, `qualifications`. This will give each string entry a primary key and name (depending on the fields and setup of your table).
  2. Next, in your `students` table, set the field types of the foreign keys to the a string variant (or type matching your data; of course, these are not keys yet at this point – currently just regular columns in the table).
  3. Now import the entire flat file (including the text values for the columns that need to be referencing separate tables.
  4. Update the `students` table with the appropriate ID from its sub-table, for example, `qualifications` [Query follows below].
  5. Once the string values are replaced by IDs, simply set the field types for these IDs in the `student` table to the same type as the sub-table’s ID (typically INT).
  6. Finally set your foreign keys if you need to.

Here is the query you will need to do replace the values (as explained in Step 4 above) by matching with the string value for the sub-table in the `students` table with the `name` field in the sub-table:

UPDATE `students` s
SET s.qualification_id =
(SELECT `id` FROM `qualifications` q WHERE = s.qualification_id)

Do this for each separate sub-table, and you’re done.

Select all columns except one from a MySQL table

I am not sure how often you would stumble upon this problem, however, I did recently, and after some research, found a nifty way to overcome it.

Here is the scenario:

  • You want to import a large CSV file into a database table.
  • The files have potential to be very large, therefore you do not want to get rid of the old data before you have imported the new data successfully into a temporary table.

How do you do this?

  • You import your data into a temporary table, let’s call it `tempdata`. This will take some time with large files, especially if you are processing each line before inserting.
  • Once the import is successful, you simply and copy from the `tempdata` to the `realdata` table by doing a simple query: INSERT INTO `realdata` SELECT * FROM `tempdata`. While this would work when there are no keys defined, you run into problems when you have UNIQUE or PRIMARY keys defined, because the key columns in the `tempdata` and `realdata` tables will overlap.

Oops. Now what?

You have two possible solutions:

  • Select only the columns that you want to import from `tempdata` and import into the same columns into `realdata`, or
  • You can be a little adventurous if you do not know beforehand what all the column names are (for example when you create tables on the fly).

Here is how you bring the adventure into your SQL mission:

First, you need to get the columns from the `realdata` table from the INFORMATION SCHEMA table:

SELECT `column_name` FROM information_schema.columns WHERE `table_name` = '" . $table_from . "' and column_name != 'id'

Now, this is assuming that your primary key or unique key’s name is “id”.

The result of this query will then contain all the columns in the `realdata` table except the ID column.

Now, with some PHP magic, you build up your query using these names and some string concatenation, and you can simply do this:



[REVIEW] Laravel Design Patterns and Best Practices

I saw a post on LinkedIN inviting people to review a new book, and I thought – what the heck. I have reviewed books before, so this could be fun. So – I signed up, and was asked to blog about the book when I am done.

The book I had to review was “Laravel Design Patterns and Best Practices”. Written by Arda Kılıçdağı and H. İbrahim YILMAZ. The book is available at Packt Publishing.

As I am reading the first few chapters, I was generally impressed. As a beginner in Laravel, I found it easy, informative, and the usage of analogies were quite clever. This resonated well with me, as I learn well by understanding difficult concepts by analogy.

Talking of analogies, I am going to describe my overall experience with the book using an analogy. Imagine a comic book with nice big letters. It reads nice. The text forms pictures in your mind as you read them. Awesome. As you turn each page in the comic book, you get more immersed in the book, and you really start to enjoy it.

But suddenly, when you turn the page somewhere near the middle of the comic book, things get a little hairy. Suddenly, you are reading a printed book. No more pictures and very small print that is highly condensed with very little line spacing.

What happened here is one of two things:

  • The book was aimed at absolute beginners, and then switched to advanced level without a gradual build-up. It became far too difficult for me to understand.
  • The book was aimed at intermediate Laravel developers, but the inclusion of the first few chapters were to fill the pages: It is far too elementary for intermediate Laravel developers.

Now – on the Packt website, the following paragraph is found:

This book is intended for web application developers working with Laravel who want to increase the efficiency of their web applications. It assumes that you have some experience with the Laravel PHP framework and are familiar with coding OOP methods.

That brings me to the second possibility I have mentioned: that the book is aimed at intermediate Laravel developers, but that the first few chapters were only fillers, being too elementary to teach intermediate Laravel developers anything.

So, while it was a good read for me up to the point where the book lost my attention, I would recommend that:

  • If you are an intermediate or advanced Laravel developer, PLEASE skip the first few chapters.
  • If you are a beginner Laravel developer, PLEASE read only the first few chapters, and tackle the last bunch of chapters only once you have gotten some more experience under the belt.

I have read several Packt books before, and I generally like them, but I have to say I am slightly disappointed by this one. That being said, it is purely my own opinion, and you might find the experience with this book a lot better than I did.

Happy reading!

Finding duplicate records in a MySQL table

So, you imported a large CSV file. You do not have a UNIQUE index on one of the fields, for example, the “student_number” field. Your client asks you to find a list of the duplicates. What do you do?

You write an SQL query, of course!

Assuming we want to de-duplicate the entire student database from the “students” table, by finding duplicates of the field “student_number”. Here is how you go about it:

First you write the INNER query:

SELECT `student_number` FROM `students` GROUP BY `student_number` HAVING COUNT(`id`) > 1

You now assign the alias “duplicate” to this query:

(SELECT `student_number` FROM `students` GROUP BY `student_number` HAVING COUNT(`id`) > 1) duplicate

Next, you write the OUTER query, and specify the match fields:

SELECT `first_name`, `last_name`, students.student_number FROM `students`
INNER JOIN __________ ON students.student_number = duplicate.student_number

Finally, you drop in the INNER query into the _____ in the OUTER query, leaving you with:

SELECT `first_name`, `last_name`, students.student_number FROM `students`
INNER JOIN (SELECT `student_number` FROM `students` GROUP BY `student_number` HAVING COUNT(`id`) > 1) duplicate ON students.student_number = duplicate.student_number

The OUTER query contain the following elements:

* The fields that you want to show when displaying the records (first_name, last_name)
* The field that you want to de-duplicate (student_number)

The INNER query does the actual magic by joining the same table with itself on the student_number field, finding all student_numbers that have more than one ID.

There you have it. Simple, right?

Reveal.js – a marvelous toy

Edit – 2021-06-30: The site Renault Société is no longer available online. For examples of usage, rather look at Reveal.js website for demos.

Recently, I have done a project called Renault Société. The technologies used here were:

  • iCMS, my own custom written CMS, based on CodeIgniter (a PHP-based framework).
  • jQuery, HTML5 and CSS3
  • Reveal.js (Github)

The result is exceeding my expectations by a million miles. Reveal.js is at the center of this magazine. While iCMS is managing the content management of the magazine, Reveal.js is in control of the presentation layer.

I have investigated the source code of Reveal.js, and found it clean, easy to understand, and it caters for extreme customization. How often do you see (or hear) that “all sites based on a certain framework” are all looking the same? I have heard several complaints of that ranging from Drupal to Joomla!, as well as WordPress and other popular open source systems. My opinion is that when this is the truth, the people designing the presentation layer thereof is simply too lazy to dig in and customize it properly.

The showcase for Reveal.js is largely a case in point. Many of the showcases have plainly followed the base template developed by Hakim El Hattab, the author of Reveal.js. Very few of the submissions are deviating from this base template, and I am proud to announce that our site is vastly different from most of those out there, and also better looking.

I would just like to use this opportunity to thank Hakim for his brilliant project. Thanks for all the hard work. It is an amazing project!

Feel free to browse around Hakim’s website. You will find lots and lots of interesting things…

Until next time!

PHP 5.4 released

After nearly three years since the release of PHP 5.3, PHP 5.4 is finally released. Although PHP 6.0 was meant to be the next PHP release, several issues, in particular issues with unicode, postponed the release. Instead, work on PHP 5.4 begun, and in this week, we have seen the culmination of that effort.

Some features available in PHP 5.4

  • Shortened array syntax, a’la JavaScript
  • Traits
  • Built-in web-server
  • Session uploads
  • Addition of binary number format
  • Function array dereferencing
  • Some reports exist in the wild of users getting over 25% speed increases
  • A new version of the Zend framwork
  • Default character set is now UTF-8

Built in web-server

Honestly, I am quite happy with Apache, but I still have to evaluate whether I will use the built in web-server. Although it is VERY early days, I am currently not finding any innovators or early adopters of this in commercial hosting setting, hence I am a bit skeptical to jump on the bandwagon regarding this feature, although I may explore this feature shortly. If so, I will post an article about it.

Shortened array syntax

For me, this means nothing, really. I wonder if this holds a performance penalty for using this syntax. Stay tuned for a little benchmark test on this in the near future.

Some pitfalls

As far as my initial investigation of PHP 5.4 indicates, most current PHP code should still work, but there are some possible pitfalls, such as:

  • New keywords that were added that may cause undesirable effects if used on older code
  • No more safemode, register_globals and magic quotes
  • PHP 5.4 is also the last PHP version that will support Windows XP and Windows  server 2003

Default character set UTF-8

I am particularly excited about this. Having a home language that often has problems with the ISO8859 character set (although mostly due to missing or wrong configuration implementations as well as different character set choices over various platforms and tools), this will help me a lot by knowing at least one of my components used for development will be correctly set and won’t need any additional checking.


Client abuse… what do you do?

It is a well known fact that by conceding in everything a client says is a recipe for disaster. Okay – so you set your price. The client tells you it is too high, and that you will have to come down drastically to get the job. More often than not, especially when your company (or you as a freelancer) is not known well enough to be able to demand the price you want and deserve, you do just that.

A client who mastered this technique often utilizes all their skills in that department to get a huge job done for the least amount of money. As a business manager, that does make sense to do, but some take this too far.

How often do you complain that you are dealing with cheapskate clients? How often do you fume about the amount of effort, work and quality they expect for the paltry amount of money they pay you? If you are falling victim to the practices mentioned above, chances are that you are often in that boat, and are in danger of being dragged away by the current.

So – how do you overcome this? In my opinion, and as corroborated by various sources on the internet, marketing and entrepreneurial magazines, and other publications, it has to do with your ability to stand by your price, deliver value for money at that price, and be open to negotiation in a moderate degree. Be open to be negotiated to a 10% lower price, but more than that, you’re risking becoming a victim of this phenomenon.

Should you succeed in getting your price, and you deliver a good quality product, chances are great that you will build a reputation of strength, and get glowing testimonials from your satisfied clients.