Wednesday, February 1, 2012

I Need a Database Please?

Can someone program me a database about a person that contains:





First Name


Last Name


Address Line 1


Address Line 2


City


State


Zip Code





Company The Person Works For


Home Phone


Cell Phone


Work Phone





eMail 1


eMail 2





I need to be able to:





1. Add a new person to the data base


2. Modify the data about a person


3. Delete a person from the database


4. Search the database for a matching name, or match a phone number or a part of a phone number, by city or state, and by part of an email address.





Thanks, I really appreciate it.





I just would like to have something that I can keep track of people on other than Access because I hate it.|||MySQL is free, but on Windows you need to install Apache and PHPmyAdmin, and thus PHP.


Try XAMPP, it's simple enough if you carefully follow the instructions.





Personally, if you already have Access, then I would use that.





For MySQL -


CREATE TABLE person (


ID int(10) unsigned NOT NULL AUTO_INCREMENT,


First_Name VARCHAR(50),


Last_Name VARCHAR(50),


Address1 VARCHAR(50),


Address2 VARCHAR(50),


City VARCHAR(50),


State VARCHAR(50),


Zip_Code VARCHAR(20),


Home_Phone VARCHAR(20),


Cell_Phone VARCHAR(20),


Work_Phone VARCHAR(20),


Email1 VARCHAR(100),


Email2 VARCHAR(100),


FK_Company INT(10),


PRIMARY KEY (ID),


KEY `FK_Company` (`company`),





)








CREATE TABLE IF NOT EXISTS `company` (


ID int(10) unsigned NOT NULL AUTO_INCREMENT,


Company_Name VARCHAR(200),


Address1 VARCHAR(50),


Address2 VARCHAR(50),


City VARCHAR(50),


State VARCHAR(50),


Zip_Code VARCHAR(20),


PRIMARY KEY (ID)


)





-- 1. Add a new person to the data base


INSERT INTO person (First_Name, Last_Name, Address1, Address2, City,State, Zip_Code, Home_Phone, Cell_Phone, Work_Phone, Email1, Email2, FK_Company) VALUES (


'First Name', 'Last Name', 'Address 1', 'Address 2',' City', 'State', '00000', '00 000 0000', '11 111 1111', '22 222 2222', 'name@home.com', 'name@work.com',1)





-- 2. Modify the data about a person


UPDATE person SET (


First_Name='First Name',


Last_Name='Last Name',


Address1='Address 1',


Address2='Address 2',


City='City',


State='State',


Zip_Code='00000',


Home_Phone='11 111 1111',


Cell_Phone='22 222 2222',


Work_Phone='33 222 2222',


Email1='name@home.com',


Email2='name@work.com',


FK_Company=2


)





-- 3. Delete a person from the database


DELETE FROM person WHERE ID=1





-- 4. Search the database for a matching name, or match a phone number or a part of a phone number, by city or state, and by part of an email address.


SELECT * FROM person WHERE Last_Name='Last Name'


SELECT * FROM person WHERE Home_Phone='11 111 1111'


SELECT * FROM person WHERE Home_Phone LIKE '11 111%'


SELECT * FROM person WHERE City='City'


SELECT * FROM person WHERE State='State'


SELECT * FROM person WHERE Email2 LIKE '%@work.com'


SELECT * FROM person WHERE FK_Company=1





For Access as above except for the CREATE -





CREATE TABLE person (


ID INTEGER CONSTRAINT Constraint1 PRIMARY KEY UNIQUE,


First_Name TEXT(50),


Last_Name TEXT(50),


Address1 TEXT(50),


Address2 TEXT(50),


City TEXT(50),


State TEXT(50),


Zip_Code TEXT(20),


Home_Phone TEXT(20),


Cell_Phone TEXT(20),


Work_Phone TEXT(20),


Email1 TEXT(100),


Email2 TEXT(100),


FK_Company INTEGER CONSTRAINT FKCompanyId FOREIGN KEY (ID) REFERENCES company ON UPDATE CASCADE ON DELETE CASCADE,


);








CREATE TABLE IF NOT EXISTS `company` (


ID INTEGER CONSTRAINT PrimaryKeyConstraint PRIMARY KEY,


Company_Name TEXT(200),


Address1 TEXT(50),


Address2 TEXT(50),


City TEXT(50),


State TEXT(50),


Zip_Code TEXT(20)


);|||http://download.cnet.com/Easy-Address-Bo鈥?/a>





http://download.cnet.com/1770-20_4-0.htm鈥?/a>|||Hrmmm. And what would the other person be programming it with? And most operating systems actually include a program that will do exactly what you want ;)|||i could do that for you, using what ?


microsoft access


MySql?


either i can do and show you what you want to know, but what would i get in return ?

No comments:

Post a Comment