I want to export database into sql file, now I have two question:
How to separate CONSTRAINT
from CREATE TABLE
?
Now the output is like this:
CREATE TABLE `comment` (
`comment_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(8) unsigned NOT NULL,
`content` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `Comment_User` FOREIGN KEY (`user_id`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
And I want to make it like this (similar to phpmyadmin):
CREATE TABLE IF NOT EXISTS `comment` (
`comment_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(8) UNSIGNED NOT NULL,
`content` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `comment`
ADD CONSTRAINT `Comment_User` FOREIGN KEY (`user_id`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE;
Here is the script I do:
//loop through the tables
foreach ($tables as $table) {
$result = $connectdb->query('SELECT * FROM '.$table);
$numColumns = $result->field_count;
$comment['separation_line'] = '-- -----------'."
";
$comment['table_structure'] = $comment['separation_line'].'--'."
".'-- Table structure for table `'.$table.'`'."
".'--'."
";
$comment['table_dumping_data'] = '--'."
".'-- Dumping data for table `'.$table.'`'."
".'--'."
";
$return .= $comment['table_structure'].'DROP TABLE IF EXISTS `'.$table.'`;';
$result2 = $connectdb->query('SHOW CREATE TABLE '.$table);
$row2 = $result2->fetch_row();
if ($result->num_rows > 0) {
$return .= "
".$row2[1].";
".$comment['table_dumping_data'];
/* Get column name */
$column_result = $connectdb->query('SELECT * FROM '.$table);
$column_name = $column_result->fetch_all(MYSQLI_ASSOC);
$get_column = array();
if (!empty($column_name)) {
$get_column = array_keys($column_name[0]);
}
$columns = '`'.implode('`, `', $get_column).'`';
/* End get column name */
$return .= 'INSERT INTO `'.$table.'` ('.$columns.') VALUES'."
";
} else {
$return .= "
".$row2[1].";
";
}
for ($i = 0; $i < $numColumns; $i++) {
while ($row = $result->fetch_row()) {
$return .= '(';
for ($j=0; $j < $numColumns; $j++) {
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("
", '
', $row[$j]);
if (isset($row[$j])) {
if (ctype_digit($row[$j])) {
$return .= $row[$j];
} else {
$return .= '`'.$row[$j].'`';
}
} else {
$return .= '``';
}
if ($j < ($numColumns-1)) {
$return.= ',';
}
}
$return .= ');'."
";
}
}
$return .= "
";
}
Another little question, how to count rows and then add ),
to the end of each row instead of );
when table have more than two rows?
Like this:
INSERT INTO `template` (`tpl_id`, `tpl_path`) VALUES
(1, 'template-common'),
(2, 'static-css'),
(3, 'static-css');
Now it will add );
to every rows:
INSERT INTO `template` (`tpl_id`, `tpl_path`) VALUES
(1, 'template-common');
(2, 'static-css');
(3, 'static-css');