How to Select Data from Columns with Similar Prefixes using Dynamic SQL in MySQL?

Dynamic SQL for Wildcard Column Selection

Question:

Suppose you have a database table with multiple columns sharing similar prefixes, such as "Fruit" and "Vegetable." How can you efficiently select data from all columns starting with a specific word without manually specifying each column name?

Example:

Consider the following table structure:

<code class="markdown">Foods | Fruit | Vegetable
----------------------
Apples | Banana | Carrots
Pears | Grapes | Potatoes
Oranges | Lemon | Tomatoes</code>

You wish to select all columns whose names start with "Vegetable."

Answer:

Direct wildcard substitutions in MySQL queries, such as SELECT Vegetable %, are not supported. To dynamically select all columns meeting this criteria, you can build the SQL query based on information obtained from the database schema. Here's a solution:

<code class="sql">SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Foods'
  AND table_schema = 'YourDB'
  AND column_name LIKE 'Vegetable%'</code>

This query retrieves the names of all columns in the Foods table starting with "Vegetable." You can then use this information to dynamically construct a second query that selects data from the desired columns:

<code class="php">$columnList = mysql_query($columnsQuery);
$columnArray = array();
while ($row = mysql_fetch_assoc($columnList)) {
  array_push($columnArray, $row['COLUMN_NAME']);
}
$columns = implode(",", $columnArray);

$query = "SELECT " . $columns . " FROM Foods";</code>

By executing this query, you can retrieve all data from the columns whose names start with "Vegetable" in a single pass.

The above is the detailed content of How to Select Data from Columns with Similar Prefixes using Dynamic SQL in MySQL?. For more information, please follow other related articles on the PHP Chinese website!