getting both rows in a many to many type database

geitvdplas · #1 (**permalink**) 10-30-09, 21:37

Hi,

I have a fairly simple question, but I can't get my head arround this. Here's the basics of my site.

Friend-Party Agenda
A _ a Table with items
B _ a Table with replies to that item
AB _ the table that links these two tables by the primairy key.

When I send my query with PHP it looks like this:

PHP Code:


			
$id = 3; // just a random id to search for

$query=

"SELECT b.*  

FROM ab

LEFT JOIN b ON (b.id = ab.b_id)  

WHERE (ab.a_id = {$id})";

Now this gives me all the replies that are stored in table b, but here comes the question:

How can extend this query so that it will also send back the data of corresponding row a? This will be just 1 row.. I need it, very badly.

I hope someone can help me out

I know it's easier to do it with a parent ID stored for each reply, but I've read that this is a better way to do it.

Greetings,

Geit

r937 · #2 (**permalink**) 10-30-09, 23:55

Code:

SELECT a.*
     , b.*   
  FROM a
INNER
  JOIN ab
    ON ab.a_id = a.id
INNER
  JOIN b 
    ON b.id = ab.b_id   
 WHERE a.id = $id

geitvdplas · #3 (**permalink**) 10-31-09, 07:34

oh right, I kinda tried that before without success, but now I see the problem wasn't with my query. I sorted everything with MYSQL_ASSOC, but both tables had same names in them. I didn't know they would overwrite eachother.

Is there anyway I can use the result type MYSQL_ASSOC without this side effect? (thus not overwriting but putting them in seperate arrays?)

Many thanks to you r937

r937 · #4 (**permalink**) 10-31-09, 09:00

Quote:

Originally Posted by geitvdplas

Is there anyway I can use the result type MYSQL_ASSOC without this side effect?

sorry, i have no idea what you're talking about, as this appears to be a php function and i don't know php

if you're having problems because there are columns with the same name in both table, you may assign column aliases in the SELECT statement

change this:

Code:

SELECT a.*
     , b.*

to this:

Code:

SELECT a.foo AS a_foo
     , a.bar AS a_bar
     , b.foo AS b_foo
     , b.bar AS b_bar

geitvdplas · #5 (**permalink**) 10-31-09, 09:11

This seems very logical and will surely work. I didn't know I could do that. Thanks for helping out a newbe

ps:
With PHP you can do mysql_fetch_array() to fetch a mysql result as an array. MYSQL_ASSOC is just a parameter for this fucntion. It means it will return an Associative array. (with strings as keys, instead of numbers)

Internet Services

Web Development

Digital Marketing

Consumer Tech