Selecting all except some columns in MySQL


The MySQL SELECT is a ubiquitous statement. You can select rows using the ‘*’ operator or by listing the individual column names. But many times you may require using all the columns from a table except a couple of them. For example you may have a table containing twelve columns from which you require only eleven columns. Most of us will use the ‘*’ operator in the SELECT statement rather than explicitly list all the eleven column names. But what if the column we do not require contains a large amount of BLOB data. It is inefficient to include the BLOB column in the query as it will unnecessarily increase the query load. What we want is to select all the columns except the BLOB column. We can construct the required statement using the ‘SHOW COLUMNS’ statement.

The following php function will return all the column names for a given table.

<?php
 
function get_column_names($conn, $table_name) {
 
    $query = "SHOW COLUMNS FROM {$table_name}";
 
    if(($result=mysql_query($query, $conn))) {
 
        /* Store the column names retrieved in an array */
        $column_names = array();
        while ($row = mysql_fetch_array($result)) {
            $column_names[] = $row['Field'];
        }
 
        return $column_names;
    }
    else
        return 0;
}
?>

Now we create a function that returns a comma separated list of column names which we can then pass to the SELECT statement. The function also takes a array of column names we want to exclude.

<?php
 
function create_statement($connection, $table_name, array $exclude)
{
    /* $exclude contains the columns we do not want */
    $column_names = get_column_names($connection, $table_name);
    $statement = "";
 
    foreach($column_names as $name) {
        if(!in_array($name, $exclude)) {
            if($statement == "")
                $statement = $name;
            else
                $statement .= "," . $name;
        }
    }
 
    return $statement;
}
 
?>

Now we create a list of columns and pass it to the SELECT statement.

/*
We want to exclude columns 'post_author' and
'comment_count' from the table wp_posts
*/
$exclude = array("post_author", "comment_count");
 
$stmt_list = create_statement($connection, "wp_posts", $exclude);
 
$query = "SELECT {$stmt_list} FROM wp_posts";

This site is a digital habitat of Sameer Borate, a freelance web developer working in PHP, MySQL and WordPress. I also provide web scraping services, website design and development and integration of various Open Source API's. Contact me at metapix[at]gmail.com for any new project requirements and price quotes.

9 Responses

1

Jani Hartikainen

February 28th, 2009 at 6:49 pm

Is there some reason to essentially waste one query for getting the list of columns?

sameer

March 1st, 2009 at 10:00 pm

There are many cases when this might be useful. Take a typical example. You are designing a prototype Ajax/PHP application or a Web Service. There are many instances in the application where you have to shuffle data from the server to the client quickly. The extra overhead of a single query on the server is negligible in comparison to the amount of data transfer saved by dropping unrequested columns. It also saves the labor of typing a long list of column names in the SELECT query.

3

Eli White

March 2nd, 2009 at 10:44 am

One other thing to point out. I’ve seen this type of code done in various places (the select to discover column names). One thing you can do, is cache that data aggressively, since it’s rare your table definitions change.

Which means that you don’t really have the overhead on every query, you do it once and keep it cached forever, and manually flush the cache if you change the table columns.

4

Onno Marsman

March 3rd, 2009 at 2:19 am

This is a really bad idea. When you would add columns to the table definition they transparently get inserted into a query like this. This way you aren’t tracking which columns are used and which are not. So there’s a big risk that eventually there will be columns that won’t be used.

About the aggressively caching idea: It’s not a bad idea: I would suggest to manually put the result directly in the query.

5

EllisGL

March 4th, 2009 at 6:34 am

I’m with Onno about the issues with this.

sameer

March 4th, 2009 at 6:55 am

Thanks for all the comments guys! I completely agree with Onno. But it still is a lot easy for me to use the above code while developing a quick prototype php application as I already said in my previous comment.

7

David

March 19th, 2009 at 5:20 pm

Just found your site – enjoying it quite a bit so far!!

Just curious – if you know which columns you DON’T want, would it make just as much sense to use the columns as keys, unset them, and implode the results? I haven’t tried and I’m probably just barely NOT a noobie… just figured I’d ask if you can see a reason to use one method over the other.

Great site!
David

sameer

March 19th, 2009 at 10:30 pm

Not sure what you really mean. But if you are saying that we should ‘SELECT’ all the columns and then unset the ones we do not want and then display the result; then it destroys the main purpose of the above code. The idea is to not select the columns we want so that we can reduce the query load.

9

Sebastian

March 21st, 2009 at 2:32 pm

I think David’s idea is not bad too… If said before that reducid query load was not your goal (as you are executing an extra query to fetch the table definition).

You could probably do a SELECT * and then unset the unused fields.

Your thoughts

Sign up for fresh content in your email