Adress-book in MySQL: how to add more fields easily

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Adress-book in MySQL: how to add more fields easily

Post 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
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Adress-book in MySQL: how to add more fields easily

Post by jankidudel »

Are you able to use phpmyadmin ?
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Re: Adress-book in MySQL: how to add more fields easily

Post 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
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Adress-book in MySQL: how to add more fields easily

Post 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.
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Re: Adress-book in MySQL: how to add more fields easily

Post 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 :)
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Adress-book in MySQL: how to add more fields easily

Post by jankidudel »

I think it will be a problem, try to just insert whem as an sql statement
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Re: Adress-book in MySQL: how to add more fields easily

Post 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
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Adress-book in MySQL: how to add more fields easily

Post 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.
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Re: Adress-book in MySQL: how to add more fields easily

Post 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.
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Adress-book in MySQL: how to add more fields easily

Post by jankidudel »

Of course, the best way to know if this will be work, is to try to do it :)
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Re: Adress-book in MySQL: how to add more fields easily

Post 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
jankidudel
Forum Commoner
Posts: 91
Joined: Sat Oct 16, 2010 4:30 pm
Location: Lithuania, Vilnius

Re: Adress-book in MySQL: how to add more fields easily

Post 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.
lin
Forum Commoner
Posts: 49
Joined: Tue Dec 07, 2010 1:53 pm

Re: Adress-book in MySQL: how to add more fields easily

Post 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 :)
Post Reply