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!

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