Trying to retrieve tables only with particular column php mysql

Question

I am trying to retrieve only tables with particular column like 'name'. For example having 3 tables table1, table2 with column name 'name' and table3 with column name 'message', I want to retrieve only table1 and table2 as they are having column name as 'name'. Here is code I tried:

     <div class="form-group">
          <label class="control-label col-sm-2" for="email">Enter number of tables:</label>
              <div class="col-sm-10">
                <select  name="table_name">
                  <?php 
                     include 'config.php';
                     $query="show tables";
                     $result_query=mysqli_query($conn,$query);
                      while($row=mysqli_fetch_array($result_query))
                       {
                          echo "<option value='{$row[0]}'>$row[0]</option>";
                       }
                  ?>
                 </select>
              </div>
        </div>

The above code is showing all tables, but I want to show to show tables with only column name as 'name'.

Possible duplicate of [How to find all the tables in MySQL with specific column names in them?](http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them) — Haotian Liu, Feb 01 '17 at 08:39

score 1 · Accepted Answer · answered Feb 01 '17 at 08:39

1

you must change a query to

SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')
        AND TABLE_SCHEMA='YourDatabase';

answered Feb 01 '17 at 08:39

Hedin

109
2

Trying to retrieve tables only with particular column php mysql

1 Answers1