By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,148 Members | 748 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,148 IT Pros & Developers. It's quick & easy.

Database related Q

P: n/a
Although this is not strictly PHP related question, i presume lots of you
are good in MySql so maybe you could help me. I am making sort of
yellowpages application and have following issue:

I have different tables for storing info about different companies, their
addresses & phone numbers and their field of work (i can't remember exact
word in english ;) Tables look like this:

1. Table "company": ID, name, web, email....
2. Table "company_address": ID, ID_company, street, city, state... (one
company can have multiple addresses so this table is linked to table
"company" via ID_company field
3. Table "company_telephone": ID, ID_company_address, number
(ID_company_address is linked to table "company_address" because on
different locations companies can have multiple phone numbers)

When i want to display all of the data for a company i use following query:

SELECT company.name, city, state, street, number,
company_telephone.naziv AS tel_naziv
FROM company
LEFT JOIN company_address ON company.ID = company_address.ID_company
LEFT JOIN company_telephone ON company_address.ID =
company_telephone.ID_company_address

And this works great. Now beside this data, i want to display field of work
for each company. for that, i use following tables:

4. Table "field": ID, name, l, r (l and r are used for storing data
hierarchically)
5. Table "field_lookup": ID, ID_field, ID_company, rank (which is used as
lookup table in which i can connect companies and their field of work. Field
rank is used when you search or browse in one category so that some
companies can be displayed first).

OK, now i want to display list of companies with their addrersses and phone
nrs. together with their field of work. I use following:

SELECT company.name, city, state, street, number,
company_telephone.naziv AS tel_naziv, field
FROM company
LEFT JOIN company_address ON company.ID = company_address.ID_company
LEFT JOIN company_telephone ON company_address.ID =
company_telephone.ID_company_address
LEFT JOIN field ON company.ID = field_lookup.ID_company &&
field.ID = field_lookup.ID_field

Now i expect something like that:

Company1, City1, State1, Street1, Number1, Naziv1, Field1
Company2, City2, State2, Street2, Number2, Naziv2, Field2
Company3, City3, State3, Street3, Number3, Naziv3, Field3

But, it returns this:

Company1, City1, State1, Street1, Number1, Naziv1, Field1
Company2, City2, State2, Street2, Number2, Naziv2, NULL
Company3, City3, State3, Street3, Number3, Naziv3, NULL
Company1, City1, State1, Street1, Number1, Naziv1, NULL
Company2, City2, State2, Street2, Number2, Naziv2, Field2
Company3, City3, State3, Street3, Number3, Naziv3, NULL
Company1, City1, State1, Street1, Number1, Naziv1, NULL
Company2, City2, State2, Street2, Number2, Naziv2, NULL
Company3, City3, State3, Street3, Number3, Naziv3, Field3

What seems to be problem? Is my database design good? It is important that
one company can have multiple addresses, multiple phone numbers and multiple
fields of work. I am also interested are there any free PHP/MySql solutions
for yellow pages application.

Thanks for your replies!
Best regards,

Marko

--
Relaxen und watch das blinkenlights...
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I noticed that Message-ID: <ch**********@bagan.srce.hr> from Cpt. Zeep
contained the following:
SELECT company.name, city, state, street, number,
company_telephone.naziv AS tel_naziv, field
FROM company
LEFT JOIN company_address ON company.ID = company_address.ID_company
LEFT JOIN company_telephone ON company_address.ID =
company_telephone.ID_company_address
LEFT JOIN field ON company.ID = field_lookup.ID_company &&
field.ID = field_lookup.ID_field


Well if you get the bit with the joining table working first you can
than just add it on to the other bit.

I prefer to use the where clause, I think it's simpler to understand

SELECT f1,f2 FROM t1,t2,t3 where t1.t1_id=t2.t1_id and t2.t3_id=t3.t3_id

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.