MYSQL too many results

[JRBeltman]

HI,
this is a bit of a MySQL /PHP question

Imagine the following tables and fields:
customer - custId,custName
custadd - custId, addressId
address - addressId, street, town

In these tables:

customer
custId - custName
01 - John
02 - Mike

custadd
custId - addressId
01 - 01
01 - 02
02 - 02
02 - 03

address
addressId - street - town
01 - John's home - John's town
02 - company street - company town
03 - Mike's home - Mike's town


So from the above we can see that John and Mike both work at the same company address and have different homes.

In my page I want to display this as follows:
'John' lives at 'John's home' and works in 'company town'
'Mike' lives at 'Mike's home' and works in 'company town'

How can I best construct my queries and depending on that, what would be the best PhP?

Many thanks

JR
As a wise man once said: To build the house you need the stone.
Back to the Basics!

 

[namida]

IMHO
there is no way you know in the address table whether the address is a 'work' address or 'home' address. Except that if there's a rule if 2 people have the same address then that address is deffinitely a company address.

But I don't think that's hypothetically correct either.

Regards,

Namida

 

[Olavxxx]

yes, I think this is a db-design issue!
1 person can have 1 home adress
1 person can have 1 work adress

You need to specify which is which!

I guess you thought:
1 person can have MULTIPLE adresses, and therefore you made the third table.

I dont think this is needed in this case, as you can add fields to the "person" table.

One example where you need 3 tables:
1 Person can have Many cars
Many cars can have Many parts

Person [1:MANY] Cars [MANY:MANY] parts

Also there, you can "cheat" by defining one field per part.
That will be the fastest sollution!

If however, some data is not known before registerring, you would maybe be better off specifying in a third table, like you here tried, but then with some description fields, etc.


Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[jpadie]

Olav

isn't it fair to say that many people could have the same address? and that one person may have both more than one homes and more than one work addresses? it might also be true that one person's work address is another person's home address (which is, in fact, true for me).

if so, it would seem logical to have a person table and an address table and then a lookup table.

I do agree that the lookup table needs to specify for each party-address pair, the nature of the address for that party (i.e. home, work, second home or whatever).

Justin

 

[JRBeltman]

Hi,
the tables are fictional, and are just to illustrate the issue of having the 3rd table with the combination of IDs.

It could have been cars, books and authors or anything.

I was mainly hoping for some input of getting it to display like:

'John' lives at 'John's home' and works in 'company town'
'Mike' lives at 'Mike's home' and works in 'company town'

The table structure for what it will be used can not be changed

Any ideas how to best create this format on a page?

'John' lives at 'John's home' and works in 'company town'
'Mike' lives at 'Mike's home' and works in 'company town'


Cheers

JR
As a wise man once said: To build the house you need the stone.
Back to the Basics!

 

[jpadie]

are you looking for help in how to formulate the join syntax for the query or for how to plot the field values with php?

 

[JRBeltman]

Well,
I am wondering what the best query would be.
I am thinking of doing this in 2.

One would be just to find the Names of the people like
1 John
2 Mike

And do a for($i=1; mysql_fetch_array($result); $i++)
and for each execute a second query that fetches the address and then put it nicely together....

But maybe there are better ways?

Cheers

JR
As a wise man once said: To build the house you need the stone.
Back to the Basics!

 

[sylve]

JR, I came to the same conclusion. I used the same structure in another context. The queries would be like this:

$query = "SELECT cust_id, cust_name from customer"
$result = mysql_query($query, $link);

while ($cust_id = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $mystring = "$row['cust_name'] lives at ";
    $query = "SELECT a.street FROM adresses as a, custadd as b WHERE a.address_id = b.address_id AND b.cust_id = '{$row['cust_id']}'";

    $result = mysql_query($query, $link);
    foreach ($result as $var)
    {
        $mystring = "$var and works in ";
    }
    $mystring = substr($mystring, 0, -14); 
}

For the syntax of string you are looking for, i can't think of a simpler way at the moment. I hope this helps for your real context!