28

I have several columns in my databases with similar names. How do I select those based on the word they start with? Here's an example table layout: enter image description here

I tried selecting all info for a particular thing (food kind in this example) using

$Food = "Vegetable"; 
mysql_query("SELECT `" . $Food . " %` FROM `Foods`");

but it didn't seem to work.

Any help would be appreciated :-)

EDIT: Apparently this wasn't clear from my example, but I already know all column's first words. The columns are always the same and no 'food kinds' are ever added or deleted. The PHP variable is only there to determine which one of a couple of set kinds I need.

Community
  • 1
  • 1
Chris
  • 2,905
  • 5
  • 29
  • 30
  • Aside from the fact that I can conceive of no sane way to do this, it's not a great idea for the same reason that SELECT * isn't a great idea; if you add / remove veggie-related columns your query returns unexpected results. – Dan J Jan 25 '11 at 19:07
  • I think everyone here did understand the question. What we are saying is that you won't be able to create such query in a single call. If you know every columns name then loop into them in oder to find which one starts with your prefix and then generate a SQL Select statement dynamically. – The_Black_Smurf Jan 25 '11 at 19:28
  • Ok, am kind of confused by the fact that wildcards are not allowed when selecting data based on column name. – Chris Jan 25 '11 at 19:53

5 Answers5

25

You'd have to build the SQL dynamically. As a starting point, this would get you the column names you're seeking.

SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'Foods'
        AND table_schema = 'YourDB'
        AND column_name LIKE 'Vegetable%'
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Sorry, maybe my example wasn't clear enough, but I already know all column names, they will remain the same. I'm just looking for the right wildcard character and a way to use it. – Chris Jan 25 '11 at 19:21
  • 5
    @Chris: You can't use a wildcard for the column list of a select statement. The SQL has to be built dynamically so that each column name is explicitly referenced in the select. – Joe Stefanelli Jan 25 '11 at 19:23
  • 1
    Ok, I'll just go with individually defining my columns then, using the PHP variable in each name I define :-) thanks – Chris Jan 25 '11 at 19:51
8

Here's a way I did it purely with MySQL:

SET SESSION group_concat_max_len = 2048;
SELECT GROUP_CONCAT(CONCAT(" ",CAST(column_name as CHAR(50)))) FROM information_schema.columns WHERE table_name='real_big_table' AND column_name LIKE 'prefix%' INTO @sub;
SET @x = CONCAT("SELECT ",@sub," FROM my_db.real_big_table WHERE my_db.real_big_table.country_id='US'");
PREPARE stmt FROM @x;
EXECUTE stmt;

My answer is inspired by this answer.

Note: My 'inner-DBA' (in the form of a small angel on my shoulder) tells me that needing to do this is probably a sign of bad DB structure, but my 'inner-hacker' (in the form of a small devil on my other shoulder) says "just get it done!"

Chris
  • 6,914
  • 5
  • 54
  • 80
Cato Minor
  • 2,992
  • 3
  • 29
  • 42
8

There's no way to do exactly what you're trying to. You could do another query first to fetch all the column names, then process them in PHP and build the second query, but that's probably more complex than just writing out the names that you want.

Or is there a reason this query needs to be dynamic? Will the table's structure change often?

Chad Birch
  • 73,098
  • 23
  • 151
  • 149
  • Nope it will never change, so I don't think this is very complicated, all I need is the correct wildcard to match all columns of a certain kind (= starting with a particular word, defined by a PHP var). – Chris Jan 25 '11 at 19:15
  • 1
    @Chris: That's what I'm saying, there is no such wildcard. MySQL doesn't support that. You either have to put `*` for all columns, or define them individually. – Chad Birch Jan 25 '11 at 19:22
  • How would I define them individually? A link to an example would suffice :] – Chris Jan 25 '11 at 19:23
  • 1
    If the columns will always stay the same, you can just define a variable somewhere, like `$VegetableColumns = 'VegetableName, VegetableColor';` and then use that in your query. – Chad Birch Jan 25 '11 at 19:23
  • To individually define columns it's just `SELECT VegetableName, VegetableColor FROM table`. If you have a variable like my last comment, you could do something like `$query = "SELECT $VegetableColumns FROM table";` – Chad Birch Jan 25 '11 at 19:24
2

Convert your database to one with three columns: 

Product_type (vegetable, fruit, etc) 

Name (apple, carrot, etc) 

color (orange, yellow, etc)

Now you can use your wildcard to obtain only a certain type, because it now is IN a columns, not in the header.

Taryn
  • 242,637
  • 56
  • 362
  • 405
GB_
  • 21
  • 1
1

How about creating the query in 2 steps?

1- Get the column's name from the db schema (or elsewhere)

2- Generate an sql query with the column's name that match your filter condition.

The_Black_Smurf
  • 5,178
  • 14
  • 52
  • 78
  • I already know the column's names, I'm looking for help to create the query which will match the similar columns, of which I already know the first word. – Chris Jan 25 '11 at 19:17