mysql export

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
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

mysql export

Post by Shendemiar »

I'm exporting mysql database. The data i can export just fine, but I'm having a bit difficulties with table structure clauses.

It creates way different table cretion line than phpmyadmin, and executing the sql gives error...

Code: Select all

function create_header()
	{
    $d  = "";
    $h  = "";

		$fields = mysql_list_fields($this->db, $this->table, $this->cnx);
		$h = "CREATE TABLE `" . $this->table . "` ( \r\n ";

		for($i=0; $i<mysql_num_fields($fields); $i++)
		{
			$name = mysql_field_name($fields, $i);
			$flags = mysql_field_flags($fields, $i);
			$len = mysql_field_len($fields, $i);
			$type = mysql_field_type($fields, $i);

			$h .= "`$name` $type($len) $flags, \r\n ";

			if(strpos($flags, "primary_key"))
				$pkey = "PRIMARY KEY (`$name`) ";
      else
        $pkey=" ";
		}

		$h = substr($h, 0, strlen($d) - 1);
		$h .= "$pkey) TYPE=MyISAM;\r\n";
		return($h);
	}
Output by it:

Code: Select all

CREATE TABLE `haku_sana` ( 
 `sana` string(30) not_null primary_key, 
 `kutsuja` int(20) not_null, 
 `vuosi` int(6) not_null, 
 `kuukausi` int(4) not_null, 
 ) TYPE=MyISAM;
While phpmyadmin outputs this:

Code: Select all

CREATE TABLE `haku_sana` (
  `sana` varchar(30) NOT NULL default '',
  `kutsuja` bigint(20) NOT NULL default '0',
  `vuosi` smallint(6) NOT NULL default '0',
  `kuukausi` tinyint(4) NOT NULL default '0',
  UNIQUE KEY `sana` (`sana`)
) TYPE=MyISAM;

feyd | Help us, help you. Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Last edited by Shendemiar on Thu Jan 06, 2005 8:58 pm, edited 3 times in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

mysql_field_type doesn't do VARCHAR's, it uses string instead. I suspect phpMyAdmin doesn't use mysql_field_type(), so either look look at phpMyAdmin's source or shell out to a mysqldump command.
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

markl999 wrote:mysql_field_type doesn't do VARCHAR's, it uses string instead. I suspect phpMyAdmin doesn't use mysql_field_type(), so either look look at phpMyAdmin's source or shell out to a mysqldump command.
Can't run exec from php, and shell is no option. phpmyadmins source is too complex for me :P, but i'll figure out something.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

mysql_list_fields might be of use as that seems to use varchar but note the depriciated comment and it's alternatives.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why not use

Code: Select all

SHOW COLUMNS FROM `table_name`
?
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Post by Shendemiar »

feyd wrote:why not use

Code: Select all

SHOW COLUMNS FROM `table_name`
?
Ok i'm hopefully getting there. Just need to add bunch of logics for default values and then solve the index keys, i guess thats it then, but i'll leave it for tomorrow.
Post Reply