Page 1 of 1

mysql export

Posted: Thu Jan 06, 2005 8:49 pm
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]

Posted: Thu Jan 06, 2005 8:51 pm
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.

Posted: Thu Jan 06, 2005 8:57 pm
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.

Posted: Thu Jan 06, 2005 8:58 pm
by markl999
mysql_list_fields might be of use as that seems to use varchar but note the depriciated comment and it's alternatives.

Posted: Thu Jan 06, 2005 9:07 pm
by feyd
why not use

Code: Select all

SHOW COLUMNS FROM `table_name`
?

Posted: Thu Jan 06, 2005 10:06 pm
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.