Create mysql table from array

Question

Using SHOW TABLE and then SHOW FULL COLUMNS i got this array

Array
(
[tabel_de_test] => Array
  (
    [0] => Array
        (
            [Field] => id
            [Type] => int(11)
            [Collation] => 
            [Null] => NO
            [Key] => PRI
            [Default] => 
            [Extra] => auto_increment
            [Privileges] => select,insert,update,references
            [Comment] => comentariu
        )
    [1] => Array
        (
            [Field] => numee
            [Type] => varchar(100)
            [Collation] => utf8_unicode_ci
            [Null] => NO
            [Key] => 
            [Default] => 
            [Extra] => 
            [Privileges] => select,insert,update,references
            [Comment] => 
        )
    )
)

My attempt to put this back to mysql with a function

function tosql($tbl_name,$fields)
{
    foreach($fields as $f_id => $f_arr)
    {
      $in.=''.$f_arr['Field'].' '.$f_arr['Type'].' '.$f_arr['Extra'].',';
    }
return 'CREATE TABLE '.$tbl_name.' ('.trim($in).')';
}

However i need all parameters, not just the field name, type and extra. How to do it properly?

Answer 1

As your underlying request is

how to make the SQL to make an existing table ?

I'll directly answer for this question.

---

SQL provide a really useful query (doc) :

SHOW CREATE TABLE my_target_table

That'll print you out something like that :

CREATE TABLE `engine_cost` (
 `engine_name` varchar(64) NOT NULL,
 `device_type` int(11) NOT NULL,
 `cost_name` varchar(64) NOT NULL,
 `cost_value` float DEFAULT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `comment` varchar(1024) DEFAULT NULL,
 PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0

---

/!\ Careful, this WILL NOT export TRIGGER that may be bind on the table

Use

SELECT trigger_name 
FROM information_schema.triggers 
WHERE EVENT_OBJECT_TABLE='my_target_table';

And

SHOW CREATE TRIGGER my_target_trigger