export table structure

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

export table structure

Post by pelegk2 »

uin mysql gui there is a possibility to export a table structure
so if i want to creat another one on another server the same i just copy and run in elswhere.
how can i prin to the screen the table structure but by using a PHP code?
for example
CREATE TABLE /*!32300 IF NOT EXISTS*/ delivery (
del_id int(6) unsigned NOT NULL auto_increment,
region_id char(3) NOT NULL DEFAULT '' ,
date int(11) unsigned ,
driver_id int(4) unsigned ,
car float ,
weight float ,
closed enum('1','0') NOT NULL DEFAULT '0' ,
round_num tinyint(1) NOT NULL DEFAULT '0' ,
insert_time int(11) unsigned ,
which_place tinyint(1) unsigned ,
tota_money float NOT NULL DEFAULT '0' ,
total_weight float NOT NULL DEFAULT '0' ,
actual_weight float NOT NULL DEFAULT '0' ,
PRIMARY KEY (del_id,region_id,round_num),
UNIQUE del_id (del_id),
INDEX del_id_2 (del_id),
INDEX region_id (region_id)
);

thnaks in advance
peleg
Bennettman
Forum Contributor
Posts: 130
Joined: Sat Jun 15, 2002 3:58 pm

Post by Bennettman »

What you want to do is get the data for each field and write out valid SQL code (no guarantees this will work though):

Code: Select all

<?php

// connect to SQL first ;p

$data = mysql_query("SELECT * FROM table");
$count = mysql_num_fields($data);

for ($i = 0; $i < $count; $i++) {
   $type  = mysql_field_type($data, $i);
   $name  = mysql_field_name($data, $i);
   $len  = mysql_field_len($data, $i);
   $flags = mysql_field_flags($data, $i);
   $query .= "$name $type($len) $flags";
   if ($i < ($count - 1)) $query .= ", ";
}

print <<<SQL
CREATE TABLE table (
$query
);
SQL;

?>
Not sure about the primary key and whatnot, take a look in the MySQL functions methinks.
Last edited by Bennettman on Sun Jun 05, 2005 4:48 am, edited 1 time in total.
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

thnaks alot

Post by pelegk2 »

man
Bennettman
Forum Contributor
Posts: 130
Joined: Sat Jun 15, 2002 3:58 pm

Post by Bennettman »

Been looking around phpMyAdmin's code, found this:

Code: Select all

SHOW TABLE STATUS FROM database LIKE 'table'
Post Reply