[SOLVED] Figuring Out How To Place In Database

[hansman]

I have a Table filled with zip codes.

I have another table that i wish to put companies in. In this table there is a field for zip codes. I would like to place several zip codes in this box.

 

I then would like the company profile to come up when i search for one of the zip codes.

 

I dont know how I would use explode(). Or if i should be using another method.

 

Can anyone help?

[JonnoTheDev]

You should add another table for zips to companies

 

 

zip_codes

----------

zip_id

zip

 

companies

-----------

company_id

name

 

zip_to_company

---------------

id

zip_id

company_id

 

This allows 1 company to have many zip codes (1 to many relationship)

[hansman]

i dont understand how i would run the php to find the zip code in the field

[JonnoTheDev]

This isnt done with PHP code. It is achieved through a database query. The zip code used in the query may come from a form submission if users want to find companies from zip codes. Im guessing you are a database novice.

Think about it. If you were to store all zip codes in the company table in a string i.e.

 

comapny1 | zip1,zip2,zip3

company2 | zip3,zip75

 

Then you are going to have to select every record from the companies table, explode all the zip codes and then find matching records.

 

If the zips are stored in their own table then you are only searching for 1 record and it relates to the zip_to_company table that relates to the companies table giving you all the companies under 1 zip code.

 

You may need to do some research on relational database design.

[hansman]

so each zipcode would be placed it their own fields, in the zip_to_company table?

 

Yeah i am a novice, im just trying to figure this out, and all my problems will be solved trying to figure this out.

[JonnoTheDev]

No the zip codes are placed in the zip table. You dont want duplicate records!

 

zips

--------

zip_id

zip

 

 

So:

 

1 | 123456

2 | 456543

 

The companies are stored in their own table:

 

companies

----------

company_id

name

 

So:

 

1 | Company 1

2 | Company 2

 

The zip codes that belong to a company are stored in zip_to_company:

 

zip_to_company

---------------

id (auto-incremental)

company_id

zip_id

 

So:

 

1 | 1 | 1

2 | 1 | 2

 

 

In the above example Company 1 can be found from 2 zip codes (zip_id 1 and zip_id 2)

 

Is this what you are after?

[hansman]

ok, i am understanding this more now.

 

So when a user searches for the Zip Code: 12345

 

$zip = 12345

 

Select * From Zips WHERE

 

ahh i cant figure it out.

[JonnoTheDev]

SELECT ztc.company_id FROM zip z LEFT JOIN zip_to_company ztc ON (z.zip_id = ztc.zip_id AND z.zip='12345')

 

If your a db novice you are best doing some swatting up first as this is not really beginners stuff. Sorry.

[hansman]

ok so i have my

 

 

Zip Codes

-----------

Fields:

-----------

Zip Code

State

zip_id(all unique)

 

 

Companies

-----------

Fields:

-----------

Name

Zip Codes

PhoneNumber

comp_id(unique)

 

ZipsToCompany

------------

id(unique)

comp_id

zip_id

 

 

My zip codes will be placed in the "Zip Codes" Field in Companies, separated by commas.

 

I then go into my ZipsToCompany and type the comp_id with zip_id in.

 

I will then run the query that you provided(with my table name changes)

 

Is this correct?

 

 

 

 

 

 

 

[hansman]

also what does the "z" by itself mean in that query

[JonnoTheDev]

You dont need the zips in the companies table as you have a relationship using the ZipsToCompany table with the foreign keys  comp_id and zip_id.

 

The z in the query is a table alias so you can reference the fields in each table so:

 

SELECT a.field1, b.field2 FROM tableA a, tableB b WHERE a.primaryKey=b.foreignKey

[hansman]

Ok, understood, I was reading up, and i think i forgot to mention something.

I want to be able to have more then one company per zip code, so would i have to add another table on the db and add combonations of zip codes and ID numbers?

[nitation]

@ Poster

 

You need to learn database design before embarking on this project. Thank you

[nitation]

@ Poster,

 

This is my idea. Add all companies (names) into table A, then add (insert) the zip codes in table B. Remember, while adding the zip codes to table B, you populate the companies from table A into a select option. Then you are done. This can work.

 

Structure looks like this.

 

Table A

-----------------

company_id  primary, auto increment

company_name

company_details

 

Table B

---------------------------------

zip_id primary, auto increment

zip_codes

company_name

 

If you look carefully, you would notice that something is common in the both tables, which "company_name".

 

Its pretty easy if you understand how SQL works

 

 

Regards

[hansman]

After  3 hours i finally understood how to do this.

 

All company zip codes are stored in the "Companys" Table

 

I then use the Join Query with w/e zip code the user typed in, and i will get all my company data.

 

Thank you guys very much