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:

INSERT INTO `realdata` (**COLSTRING**) SELECT **COLSTRING** FROM `tempdata`

Voila!

[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

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.

Sources

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.

Traits in PHP 5.4

Due to the fact that PHP has up to now used only a single inheritance model, reusing code  by reducing duplication of code was sometimes a bit of a problem.

To start explaining what traits in terms of PHP are, a good start is to look at how the PHP documentation defines it:

“Traits is a mechanism for code reuse in single inheritance languages such as PHP. A Trait is intended to reduce some limitations of single inheritance by enabling a developer to reuse sets of methods freely in several independent classes living in different class hierarchies.”

Buzz words

We have several of buzz words here. They are:

  • Code reuse
  • Single Inheritance
  • Class hierarchies

To fully understand what the definition of a trait means, it is important that you understand what these individual buzz words mean.

Code reuse

Code Reuse is the practice of reusing old code (or software) to create new code (or software). For example, as an overly simplistic example, you could write a single routine, let’s say, HelloWorld, that may do something as simple as write “Hello World” on the screen. You can then use it in other places within your case by simply calling the function HelloWorld which will then automatically put the phrase “Hello World” on the screen where you call this function or routine. An added benefit of code reuse is that should you need to change the message displayed to “Hello Jupiter” it is as simple as changing it in one place in your code. Of course, code reusing has a more prominent place in object oriented programming, but I suspect you are catching my drift.

Single Inheritance

In its most simplest definition, the term Single Inheritance refers to the fact that a class written in a language that utilizes it, can only inherit from one super class.

Class Hierarchies

Class Hierarchies refers to the classification of classes in relation to one another. There are various classes of hierarchies, which falls outside the scope of this article, but in short, some classes can be used as blueprints for others, or an instance of another.

Trait definition revisited

Now that we can distinguish the buzz words in the PHP documentation definition of a trait, the definition makes more sense. Loosely translated, based on the statements above, this can now be simplified as follows:

“A Trait is intended to reduce some limitations of single inheritance by enabling a developer to reuse functions or routines of code previously developed freely in several independent classes living in different classes that are not related to one another.”

Conclusion

This post is not intended as a “how-to” for using traits. If you’re interested in such a post, you should take a look at one of the following articles, which describes the process clearly: http://phpmaster.com/using-traits-in-php-5-4/ or http://simas.posterous.com/new-to-php-54-traits. Both of these articles explains HOW to use traits. The intention of this article is to explain what it is, define the concept and have a discussion after this. Feel free to post your comments by replying to this post.