Page 1 of 1
Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 9:37 am
by lin
Hi dear friends,
i want to create an adressbook with MySQL. At the moment i do not know how many fields i need.
i want to be flexible with that - at least in the next days... Untill i am sure how many fields i really would need.
i have found a Dump that allready is build for a Adressbook - i found this one in the internet.
http://www.apachefriends.org/f/viewtopi ... view=print
Code: Select all
# phpMyAdmin SQL Dump # version 2.5.7-pl1 # http://www.phpmyadmin.net # # Host: localhost # Erstellungszeit: 04. September 2007 um 16:37 # Server Version: 4.0.20 # PHP-Version: 4.3.7 # # Datenbank: `joels` # # -------------------------------------------------------- # # Tabellenstruktur f�r Tabelle `address_book` # CREATE TABLE `address_book` ( `address_book_id` int(11) NOT NULL auto_increment, `customers_id` int(11) NOT NULL default '0', `entry_gender` char(1) NOT NULL default '', `entry_company` varchar(32) default NULL, `entry_firstname` varchar(32) NOT NULL default '', `entry_lastname` varchar(32) NOT NULL default '', `entry_street_address` varchar(64) NOT NULL default '', `entry_suburb` varchar(32) default NULL, `entry_postcode` varchar(10) NOT NULL default '', `entry_city` varchar(32) NOT NULL default '', `entry_state` varchar(32) default NULL, `entry_country_id` int(11) NOT NULL default '0', `entry_zone_id` int(11) NOT NULL default '0', PRIMARY KEY (`address_book_id`), KEY `idx_address_book_customers_id` (`customers_id`) ) TYPE=MyISAM AUTO_INCREMENT=2 ;
can i use this - and can i easily add more fields... ?
lookforward to hear from you
Regards
db1
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 10:39 am
by jankidudel
Are you able to use phpmyadmin ?
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 10:59 am
by lin
Hello Jan many many thanks for the quick reply.
Yes - i can use phpmyadmin! That is no problem for me. - and i guess that it can be done with that... I have played a bit with phpMyadmin in the last 20 Minutes.
But i have a nother problem; Well - what if i want to
migrate 10 (Adressbook-)DBs into one.
What makes it a bit hard: They look
a bit different:
Code: Select all
Adressbook 1: name adress eMail tel Telefax portrait
Adressbook 2: name Company aresss: postalcode Telefon: Fax: E-Mail: Internet:
Adressbook 3: name address tel fax email homepage
all ten look like a bit different. How to treat this migration of ten tables into one big DB!?
Hope i was able to make clear what i want. If i have to be more precise - just lemme know
Many thanks in advance
regards
db1
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 11:13 am
by jankidudel
Your final table must have columns, which exists on each of the address book table, unless you want to have for example empty email, or empty address.
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 11:27 am
by lin
Hi Jankidudel
many thanks for the quick relply.
jankidudel wrote:Your final table must have columns, which exists on each of the address book table, unless you want to have for example empty email, or empty address.
well - great thanks for the infos. I understand.
I also understand the importance and the benefits of the usage of phpMyadmin.-
I have all the db-records in a TabSeperatedFormate (TSV) - you think that it is possible to migrate all into one db?
I understand that ihave to make sure that the (final) big database must have all the columns, which exists on each of the abress-book-table,
But - does it not be difficult - to put all the data into this db - when the tab-seperated-values look different??
Or should i use some first dbs where i insert all the tsv - in order to migrate the different dbs from those "INTERMEDIATE" DBs to the final one..
Look forward to hear from you
regards

Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 11:38 am
by jankidudel
I think it will be a problem, try to just insert whem as an sql statement
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 11:58 am
by lin
hello Jan
jankidudel wrote:I think it will be a problem, try to just insert whem as an sql statement
thx for the quick answer!
Well i can do this like so:
What if i create ten dbs - out of the ten datasets that i have in TSV-format!
To make it easy i want to explain a solution with only the migration
of one old - to the
new (final) database
Let us say - i have a old db and a new db -Then i take care for the
names of the columns.
It should work with a INSERT statement.
Code: Select all
INSERT INTO db_new.adressbook
(name, prename, street, postalcode, town)
VALUES
(SELECT name, prename, street, postalcode, ort FROM db_old.adressbook)
In the SELECT-Statement i only have to choose the colums which i want to migrate out of the old Database
I want to try this out! What do you think about this solution
love to hear from you
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 12:04 pm
by jankidudel
INSERT INTO db_new.adressbook
(name, prename, street, postalcode, town)
VALUES
(SELECT name, prename, street, postalcode, ort FROM db_old.adressbook)
I don't think the above will work, because when inserting values, you can't use 2 statement to grab them with the help of mysql.
but you can do it in 2 statements of course.
Yes, this solution should work, : you choose only these values that you need and insert them in big database.
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 1:58 pm
by lin
hello jankidudel,
many thanks !
jankidudel wrote:INSERT INTO db_new.adressbook
(name, prename, street, postalcode, town)
VALUES
(SELECT name, prename, street, postalcode, ort FROM db_old.adressbook)
I don't think the above will work, because when inserting values, you can't use 2 statement to grab them with the help of mysql. But you can do it in 2 statements of course. Yes, this solution should work, : you choose only these values that you need and insert them in big database.
Okay - well you mean that i have to do it stepwise.
And what about this: - with UNION see
http://dev.mysql.com/doc/refman/5.0/en/union.html
UNION is used to combine the result from multiple SELECT statements into a single result set.
Code: Select all
INSERT INTO addressbook (name, address, email tel, fax) VALUES
(SELECT name, address, email, tel, telefax FROM addressbook1)
UNION ALL
(SELECT name, company adress, email, telefon, fax FROM addressbook2)
UNION ALL
..
Legend: UNION is used to combine the result from multiple SELECT statements into a single result set. The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)
If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements. For example, consider the following:
Code: Select all
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+
(In some earlier versions of MySQL, only the type and length from the first SELECT would have been used and the second row would have been truncated to a length of 1.) The SELECT statements are normal select statements, but with the following restrictions: Only the last SELECT statement can use INTO OUTFILE. (However, the entire UNION result is written to the file.) HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION. If you specify it for the first SELECT, it has no effect. If you specify it for any subsequent SELECT statements, a syntax error results.
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sat Jan 15, 2011 4:26 pm
by jankidudel
Of course, the best way to know if this will be work, is to try to do it

Re: Adress-book in MySQL: how to add more fields easily
Posted: Sun Jan 16, 2011 7:49 am
by lin
Hi Jan
jankidudel wrote:Of course, the best way to know if this will be work, is to try to do it

i do it! and try it out!
Many thanks for the hints - and your comments. This is a great forum! Indeed!
BTW; As i already have the addresses in tab separated format
What aobut this: I can create 10 different tables (or less according to the different formats) and loaf the into the database using load
data infile command MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax .After this i can used the commands posted by you to create a new table with the new address book format.
what do you think about this!
look forward to hear from you
best
db1
see also: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sun Jan 16, 2011 8:38 am
by jankidudel
I can just say in what way I would do it probably.
To do it without headache, I would create 1 big temporary table, and then get all tables to it with a help of phpmyadmin( I would get the sql, which created all tables, and probably modify them) . Then , I would probably create second table where there would be only these columns what i want, and got the data from temporary database.
Re: Adress-book in MySQL: how to add more fields easily
Posted: Sun Jan 16, 2011 11:39 am
by lin
Hello good everning dear Jankidudel
many thanks for the input!
jankidudel wrote:I can just say in what way I would do it probably.
To do it without headache, I would create 1 big temporary table, and then get all tables to it with a help of phpmyadmin( I would get the sql, which created all tables, and probably modify them) . Then , I would probably create second table where there would be only these columns what i want, and got the data from temporary database.
great - yes i will do so! Many many thanks for the input.
Greetings
db1
