Get all PRODUCT_TYPE = "cars" from all table

Question
database
==========brand_1(table)

------------------product_1

------------------product_2

==========brand_2(table)

------------------product_1

------------------product_2

all row structrue have this

ID, PRODUCT_TYPE, PRODUCT_NAME

i have stuck here

$table_sql = "SHOW TABLES";

# get table name
$tables = $connection->_query($table_sql);

while (($table = $tables->fetch(PDO::FETCH_NUM)) !== false)
{
  $fields = $connection->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMN WHERE PRODUCT_TYPE");
  $fields->execute();
}

my question how do i get all PRODUCT_TYPE = "cars" from all table ? if i echo the $tables i already show the table name. my intention is get all the table name 1st , then somehow loop and get all the PRODUCT_TYPE and store to a array.

how i get that ?


Show source
| pdo   | php   | mysql   2017-01-07 12:01 1 Answers

Answers to Get all PRODUCT_TYPE = "cars" from all table ( 1 )

  1. 2017-01-07 13:01

    Create array of all table names, where you find this FLAG, and in foreach create SQL query like:

    SELECT COLUMN_NAME FROM brand_1 WHERE PRODUCT_TYPE = 'cars'
    UNION
    SELECT COLUMN_NAME FROM brand_2 WHERE PRODUCT_TYPE = 'cars'
    UNION  ........
    

    foreach like:

    $query = '';
    foreach (array_of_dbtables as $table)
        $query += ' UNION SELECT COLUMN_NAME FROM ' . $table . ' WHERE PRODUCT_TYPE = "cars" ';
    

    and you must add IF for first item in array, then you does not use UNION.

    You're welcome, if helped to mark answers as correct and others, have a nice day

Leave a reply to - Get all PRODUCT_TYPE = "cars" from all table

◀ Go back