CODEWITHSUNDEEP
phpmysqlselect
sarah
sarah

Reputation: 105

Get column names of multiple tables and insert it to another table + PHP MySQL

I have this code:

$createFields="CREATE TABLE temp_field (fld text NOT NULL, tablename char(50) NOT NULL)";   
mysql_query($createFields);

$fields=array();

$campaign=mysql_query("SELECT campaignid FROM campaign ORDER BY campaignid");

while($row=mysql_fetch_array($campaign)){

    $table="'campaign_".$row['campaignid']."'";

    $temp_field="SELECT COLUMN_NAME,TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=".$table;

    $temp_fieldquery = mysql_query($temp_field);

    while($rowL=mysql_fetch_array($temp_fieldquery)){

        $fields[]="'".$rowL['COLUMN_NAME']."'";

    }
$field=implode(",",$fields);

$insertFields='INSERT INTO temp_field (fld,tablename) VALUES ("'.$field.'","'.$table.'")';

    mysql_query($insertFields);

and I need to have this kind of output:

fld                       | tablename
=====================================
fld1,fld2,fld3,fld4...    |  table1
fld1,fld2,fld3,fld4...    |  table2

but what I get is:

fld                                          | tablename
========================================================
fld1,fld2,fld3,fld4...                       |  table1
fld1,fld2,fld3,fld4,fld1,fld2,fld3,fld4...   |  table2

The second table gets the values of first table

What am I doing wrong with my code, please help

Upvotes: 0

Views: 644

Answers (2)

TomScaphare
TomScaphare

Reputation: 13

INSERT INTO temp_field
SELECT 
GROUP_CONCAT(COLUMN_NAME separator ",") AS fld
TABLE_NAME AS tablename
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME LIKE 'campaign_%'
GROUP BY TABLE_NAME

Your code sample is much to complicated. Just use the query above.

Upvotes: 1

ace
ace

Reputation: 7593

Just initialize your $fields array right before the while loop.

$fields = array();
while($rowL=mysql_fetch_array($temp_fieldquery)){
  $fields[]="'".$rowL['COLUMN_NAME']."'";
}
$field=implode(",",$fields);

What happened in your code is that the $fields array continues to add new fields for each succeeding table due to $fields[] so resetting to a new array should fix it.

Upvotes: 1

Related Questions