Selecting all columns that start with XXX using a wildcard?

php mysql select wildcard
Chris picture Chris · Jan 25, 2011 · Viewed 59.8k times · Source

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.

Answer

Joe Stefanelli picture Joe Stefanelli · Jan 25, 2011

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%'

Related questions

How can I properly use a PDO object for a parameterized SELECT query

I've tried following the PHP.net instructions for doing SELECT queries but I am not sure the best way to go about doing this. I would like to use a parameterized SELECT query, if possible, to return the ID in …

Read More
How to create virtual column using MySQL SELECT?

If I do SELECT a AS b and b is not a column in the table, would query create the "virtual" column? in fact, I need to incorporate some virtual column into the query and process some information into the …

Read More
php select from mysql where title begins with A (and A alone)

I'm sure this is super easy, but can't seem to figure it out.. I need to select all titles from my database where the title starts with A, or B, or C etc. Here's what I've tried so far: SELECT * …

Read More