Home ›
Combining one-to-one mysql tables into a single tableCombining one-to-one mysql tables into a single table
Submitted by Benjamin Melançon on December 11, 2007 - 8:08pm
Merging tables does not mean what I want it to mean-- it refers to tables with the same schema.
I simply want to join two tables into one on a unique key.
To do that very easily
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
These are the tables Agaric needs to condense into one:
CREATE TABLE `commenti` (
`id_comm` int(11) NOT NULL auto_increment,
`id_frase` int(11) NOT NULL default '0',
`testo` text NOT NULL,
`autore` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`data` datetime NOT NULL default '0000-00-00 00:00:00',
`attivo` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id_comm`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `wsf2008`.`firme`
--
DROP TABLE IF EXISTS `firme`;
CREATE TABLE `firme` (
`pid` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`organisation` varchar(100) NOT NULL default '',
`country` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`attivo` tinyint(1) NOT NULL default '1',
`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`representative` tinyint(1) NOT NULL default '0',
`network` varchar(255) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
PRIMARY KEY (`pid`),
UNIQUE KEY `Unique` USING BTREE (`name`,`email`,`organisation`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
And here we are. id_frase of commenti matches pid of firme, so we drop the latter in the combined table. :
CREATE TABLE `combined_` (
`id_frase` int(11) NOT NULL default '0',
`testo` text NOT NULL,
`autore` varchar(100) NOT NULL default '',
`data` datetime NOT NULL default '0000-00-00 00:00:00',
`name` varchar(100) NOT NULL default '',
`organisation` varchar(100) NOT NULL default '',
`country` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
`representative` tinyint(1) NOT NULL default '0',
`network` varchar(255) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
PRIMARY KEY (`pid`),
);
Here's code that would provide a way to automate the creation of the table that would combine the two other tables, I guess. I can't believe it's so hard to extract a MySQL table definition in a way that can then be altered easily.
$result = mysql_query("SHOW COLUMNS FROM sometable");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
print_r($row);
}
}
Examples from the two tables:
(1509,'Francisco Lastname','RADIO EL GRITO','Argentina','example@example.com.ar',1,'2007-09-05 15:31:44',0,'',''),
(1513,'Marcela Lástnamez Páez','independiente','Colombia','otraexample@hotmail.com',1,'2007-09-05 20:24:16',1,'','MedellÃn'),
(1514,'aouhab mouaad','FJM','morocco','example@hotmail.com',1,'2007-09-05 20:28:23',1,'','tetouan'),
(1515,'Carmen','Independiente','Spain','carmencabrillo@hotmail.com',1,'2007-09-06 12:22:46',0,'','Madrid'),
(1516,'MAMA Arsitide','Tous Unis Sur Terre','Bénin','example@gmail.com',1,'2007-09-07 13:45:43',1,'','Cotonou'),(108,1513,'porque creo que juntos podemos lograr un mundo digno, participativo, y equitativo','Marcela Laástnamez Páez','otraexample@hotmail.com','2007-09-05 20:24:16',0),
(109,1516,'\"Luttons ensemble pour un monde plus juste, responsable, unis et solidaire\"','MAMA Arsitide','example@gmail.com','2007-09-07 13:45:43',0),
(110,1518,'Todo es posible si somos muchos y lo deseamos de veras.','Bosco Urruty','example@yahoo.com','2007-09-07 23:53:48',0),
I think the easiest way to compare tables in a SQL-based database is using SQL ... What about exporting the tables from the databases, importing those you want to compare into one database and then using set-operations in SQL using MINUS, INTERSECT. For example: select c1, c2, c3 from table1 intersect select c1, c2, c3 from table2; -- return data common in both tables select c1, c2, c3 from table1 minus select c1, c2, c3 from table2; -- data only in table1 etc. You can export specific tables from a MySQL database using the mysqldump commandline tool and then load them into the other database. HTH, -- Gerhard
Thnaks to everybody for their input. I have found a quick fix for now. MySQL dump allows me to export the data to XML which can easily be compared. This will help me for now and the project will have to wait until I have some more time. Regards Jandre
MySQL save query as table
Resolution
Searched words:
export one-to-one tables as csv file
export one-to-one tables as csv file MySQL
export one-to-one tables as csv MySQL phpmyadmin
export tables as one csv MySQL phpmyadmin
export join tables csv MySQL
user import nodeprofile
MySQL merge tables
MySQL join two tables into new table
MySQL select insert
MySQL combine two one-to-one tables
MySQL read schema
MySQL read table definitions
MySQL get table definition
drupal create csv
mysql select and insert
mysql select then insert
Comments
It seems like this should do
It seems like this should do more or be easier or something. Why can't I easily extract table data and turn it into a create or alter table function without having to think?
From http://www.php.net/manual/en/function.mysql-list-fields.php
Example#1 Alternate to deprecated mysql_list_fields()
<?php$result = mysql_query("SHOW COLUMNS FROM sometable");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
print_r($row);
}
}
?>
The above example will output something similar to:
Array
(
[Field] => id
[Type] => int(7)
[Null] =>
[Key] => PRI
[Default] =>
[Extra] => auto_increment
)
Array
(
[Field] => email
[Type] => varchar(100)
[Null] =>
[Key] =>
[Default] =>
[Extra] =>
)
For future reference
The following code may be of use to others that stumble upon this question:
CREATE TABLE `combined_table_name` ASSELECT
/* selected columns */
FROM `table_1`
LEFT JOIN `table_2` ON(/* join conditions here */);
For each columns this will use the same datatype and the same names as the original tables had.
If you use aliased names for the selected columns, the new table will use the alias as the column name.
If you use type casting for the selected columns, the new table will use the datatype the column was cast to.
Post new comment