JOIN - many-to-many output? - PHPBuilder Forums

JOIN - many-to-many output?

alexei

Hi,

Can anybody help me with a many-to-many join output problem? I need to grab info from a main table, and assocatiated authority list, then join it with an expanding list of topics. This is okay, but I want the output of the mulitple hits from the many to many topics table to output in an array, THEN go to the next result row. is such a thing possible?

a simplified mockup version of the tables:

table1

name (boston, chicago, new york)
stateitem (MA, IL, NY)
id (1, 2, 3)

table2

stateid (MA, IL, NY)
statename (Massachussets, Illinois, New York)

table3

t1id topic

2 windy
2 jazz
2 gangsters
3 broadway
3 chinatown
1 chinatown
2 chinatown
1 jazz
3 jazz

the idea is that I can just keep adding topics to table 3 whenever needed and be able to look up the associated info in the other tables. (note all of the tables have a separate auto incremented ID, and "table1.id" & "table3.t1id" here are both just kept static, even if records are deleted later.)

what I want to do is to send a text string query against some info in table1.name and have the multiple items that are related to each entry in table1 appear on a single line in the output, as in:

query: new york

desired output:

I am using something simple like this:

begin PHP snippet>>>>>>>>>>>>>

$result = mysql_query ("SELECT * FROM table1, table2, table3
WHERE table1.stateitem = table2.stateid
AND table1.id = table3.t1id
AND table1.name LIKE '$name%'
AND table2.statename LIKE '$statename%'
AND table3.topic LIKE '$topic%'

");

if ($row = mysql_fetch_array($result)) {

do {

print ("Name:");
print $row["table1.name"];
print (" ");
print ("State:");
print $row["table2.statename"];
print (" ");
print ("Topic:");
print $row["table3.topic"];
print ("");

} while($row = mysql_fetch_array($result));

} else {print "Sorry, no records were found!";}

end PHP snippet>>>>>>>>>>>>>>>>

using this code we get all of the possible output as separate listings as in:

query: new york

output:

is there any way to write an "if / else", "if / while", or other loop in PHP to get the desired output of the extra matches on a single line?

thanks for any ideas!

alexei

Anon

Try taking a look at this

http://www.ca.postgresql.org/docs/aw_pgsql_book/node60.html

Hank

Anon

HI,

Thanks Hank.

The tables are helpful. I should have mentioned that I am using MySQL...although the tables you recommended should work fine... can you also point me to any code related to the php loop needed to display the multiple entries related to each join on the the same line? what i'm trying to avoid is a listing of each row result as a separate set of attributes...

thanks!

lex

amcgrath

I think what you are tring do can be accomplished this way:

$query = "select * from table1,table2,table3 " .
"where table1.stateitem = table2.stateid " .
"and table1.id = table3.tid " .
"and table1.name like 'n%' " .
"and table2.statename like 'n%' ";
$res = pg_exec($conn,$query);

while($row = @mysql_fetch_array($res)) {
$city_state[$row["name"]] = $row["stateitem"];
$topic_list[$row["stateitem"]][] = $row["topic"];
}

while(list($k,$v) = @each($city_state)) {
echo "City: $k ";
echo "state: $v ";
echo "topic: ";
while(list($k1,$v1) = @each($topic_list)) {
if ($k1 == $v) {
while(list(,$v2) = @each($v1)) {
echo "$v2 ";
}
}
}
echo " ";
}

alexei

Hi Allen,
many thanks for your ideas! this is exactly the sort of output method I was looking for. Now if only I can get it to work in mysql : )

I've tried swapping pg_exec to mysql_query and a few other changes, but can't seem to to get it to work yet. Have you managed to get something to work along these lines?

also I am not familiar with the usage "@each" and "list"... are they PostGres specific.

anyway, thanks alot for your help.

alexei

amcgrath

Heh, i see i mixed pg and mysql... sorry about that (I use Pstgres, it's a force of habit...) When you sat "a few other things," what exactly do you mean? Unless there's somehting about Mysql that I don't know (very high probability) then this code should work (sans the pg_exec...)

list() and each() is like a for each but easier. http://www.php.net/manual/en/function.each.php