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_addres s": 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_teleph one": ID, ID_company_addr ess, number
(ID_company_add ress is linked to table "company_addres s" 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_telepho ne.naziv AS tel_naziv
FROM company
LEFT JOIN company_address ON company.ID = company_address .ID_company
LEFT JOIN company_telepho ne ON company_address .ID =
company_telepho ne.ID_company_a ddress
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_look up": 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_telepho ne.naziv AS tel_naziv, field
FROM company
LEFT JOIN company_address ON company.ID = company_address .ID_company
LEFT JOIN company_telepho ne ON company_address .ID =
company_telepho ne.ID_company_a ddress
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.. . 1 1811
I noticed that Message-ID: <ch**********@b agan.srce.hr> from Cpt. Zeep
contained the following: SELECT company.name, city, state, street, number, company_teleph one.naziv AS tel_naziv, field FROM company LEFT JOIN company_address ON company.ID = company_address .ID_company LEFT JOIN company_telepho ne ON company_address .ID = company_teleph one.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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Maellic |
last post by:
Hi,
The company I work for often develops databases for clients. Those
databases always need to do similar tasks. One company might need a
database to do task 1 and task 2, while another company might need it
for task 2 and 3. Instead of having to redevelop each database from
scratch, I'd like to be able to develop several "modules" (kind of
mini-databases) representing each of this task, and then chose the
modules which are relevant....
|
by: Raven |
last post by:
Hello,
I get a error message when I try to Add data in a relational Access
Database.
This is the error message:
"You cannot add or change a record because a related record is required in
table 'Classes'."
How can I solve this problem??
|
by: James Armstrong |
last post by:
Hi all,
(warning - long post ahead)
I have been tasked with designing a database for my company which will
store trade information (it is a financial firm). It will need to
export this info into an excel file while converting some of the data
into an export format (example - we use B for buy, the firm we export
to uses BY). Eventually, accounting will also need reports from the
data.
|
by: cronman |
last post by:
I am creating a small Windows application using Visual C# 2005 Express
Edition (August CTP) for a small non-profit agency. My current issue is
with updating related information in an Access database. The individual
records are being saved to the database with a few issues...
Notes:
1) I was going to post the source code from a test program I developed
for testing these issues, but it made for a messy post. Here is a link
to a zip...
|
by: Morgan Bachu |
last post by:
Hello everybody,
I have an intranet application which is mostly a bunch of data editing
forms.
The database has about 20 tables all related together.
Recently somebody deleted a "the wrong record" and the cascade delete
in sql server did its work nicely :(
| |
by: TS |
last post by:
Say i have a class car with properties: Color, Make, Model, Year, DriverID
And a Driver class with properties: DriverID, Name
The driverID PRIVATE property is the id of the driver from say a driver
table (t_driver). This has a PUBLIC property accessor called Driver
My understanding of OO using the composition model is that when you want to
load up a car class, you would access the DB to get Color, make, Model,
Year, DriverID and load...
|
by: sweer ou |
last post by:
hi everyone
this is my last year on Open University..and i have a graduation project
i choose 2 make it on MS access with Visual Basic for interfaces...
my idea is production of a Encyclopedia Database which is reference work offering comprehensive information on all areas of knowledge related to Open University Subject and other supporting materials made available to students for reading, study, or reference. Central mission of the...
|
by: |
last post by:
Hi. This is a a semi-newbie question about how to store arbitrary
information about my apps
such that I can code quickly, mimizing complexity and the number of things I
have to hold in my brain. I am going to describe the scheme I'm using, and
then
I'm going to describe another scheme that may be more OO but that I have not
tried.
I'm hoping someone vastly smarter and more experienced than me will critique
this
and/or tell me a yet...
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
|
by: Neil |
last post by:
Yikes! My database, which had been consistently 1 gig for a long time, went
from being 1 gig to 3 gigs overnight! Looking at the nightly backups, the
database increased on average about 5-15 MB per day, and was 1.06 GB on the
Thursday night backup. Then, with the Friday night backup, it was 2.95 GB,
and has stayed that way since!
I did a Shrink on the database, but that didn't help the situation.
The only thing I could think it might...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |