Monthly Archives: August 2014

Select all columns except one from a MySQL table

Posted on by
Reply

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

Posted on by
Reply

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

Posted on by
Reply

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?