[SOLVED] Mysql Multiple Joins

[bww]

I am trying to have a query that has a LEFT and a SELF join. I am trying to display each page's title and the two owners names' (own and own2) (using a query and putting that query into an array).

Here are the tables:

users (id, first, last)

pages (pg_number, title, own, own2)

I was able get mysql to  a left join (displaying the title and the first owners name (code below)),  but I have not been able to also use both the self join and the left join together.

SELECT * FROM pages LEFT JOIN users ON pages.own = users.id

 

Thanks

Ben

 

Specs:

Mysql 5.1.30

PHP 5.2.8

Apache 2.2.11

 

[thepip3r]

Ultimately, forget what you think you're trying to do, what data are you actually trying to join?  Using this: 

I am trying to display each page's title and the two owners names' (own and own2)

  ...and this is assuming that from your query, pages.own is a field in the pages table that holds the corresponding id of the user from the users table!

SELECT * FROM pages, users WHERE pages.own=users.id

 

that query will pull all records from both tables where the two id fields (own and id) correspond.

[bww]

thepip3r, let me give an example of what I am trying to do:

In the pages table lets say the title =Home, pg_number=111 and own=1 and own2=5

In the users table, pretend the first record is id=1 first=bob last=smith

Also in the users table, pretend that there is another record where id-5 first=george last=washington

I want to display the following:

Page: 111 Title: Home Owners: Bob Smith and George Washington.

 

[thepip3r]

try:

 

SELECT * FROM pages, users WHERE pages.own=users.id OR pages.own2=users.id

 

edit:  the problem with your logic is that your going to have to pull multiple records in this query because of how you have your columns configured (specifically own and own2).  Trying to use the same query with an ANDing operator will never apply except when you have a single entry where Bob Smith and George Washington are listed.

[fenway]

You should really join in the users table twice... ORs are bad.

 

SELECT *

FROM pages as p

inner join users as u1 on (p.own=u1.id )

inner join users as u2 on (p.own2=u2.id )

[bww]

I don't understand how you got some of the variables.

SELECT *

FROM pages as p what is p? should this be pages?

inner join users as u1 on (p.own=u1.id )where are you getting u1 and u2

inner join users as u2 on (p.own2=u2.id )

 

Maybe the query you gave is correct,  I just have no idea how to have php echo the first and last names for own and own2. Thanks

[fenway]

Those are table aliases... try u1.first, u1.last, u2.first, u2.last... you'll see.

[bww]

I can't figure what I did wrong; In this example, I was trying to list the first names of own and own2 for each page.

mysql_connect("localhost","user","pass");
mysql_select_db("database");

$result = mysql_query("SELECT *
FROM pages as p
inner join users as u1 on (p.own=u1.id)
inner join users as u2 on (p.own2=u2.id)
");


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {


echo $row['u1.first'];
echo $row['u2.first'];
}


mysql_close;

[thepip3r]

as fenway was pointing out, those references are to table aliases, they have no correlation in PHP.  to explain aliases, look at this example:

 

SELECT p.pid, p.number, p.blah, p.own, p.own2, u1.id, u1.name, u1.test, u1.blah
FROM pages p
inner join users u1 on (p.own=u1.id)
inner join users u2 on (p.own2=u2.id)

 

really how SQL reads that is:

 

SELECT pages.pid, pages.number, pages.blah, pages.own, pages.own2, users.id, users.name, users.test, users.blah
FROM pages p
inner join users u1 on (pages.own=users.id)
inner join users u2 on (pages.own2=users.id)

 

...and as you can see, the former is MUCH shorter (and this is a small example).  with large joins and large table names, abbreviated aliases can immensely cut down on your SQL query string length.  aliases do nothing but make it easier and less complicated to construct SQL statements.

 

i'll give you another hint on troubleshooting.  WHENEVER I'M DEALING WITH ARRAYS, I use the following function:

 

iterateArray($row);

function iterateArray($arr) {
echo "<pre>";
print_r($arr);
echo "</pre>";
}

 

this will graphically display the array you're trying to parse to the screen so you can see what data is being returned to PHP -- VERY useful.  and to include this in your example, try:

 

mysql_connect("localhost","user","pass");
mysql_select_db("database");

$result = mysql_query("SELECT *
FROM pages as p
inner join users as u1 on (p.own=u1.id)
inner join users as u2 on (p.own2=u2.id)
");


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     iterateArray($row);
}

function iterateArray($arr) {
echo "<pre>";
print_r($arr);
echo "</pre>";
}

 

[bww]

Thanks for all of the info! One more question: how can i get mysql to output both names (own and own2)? Right now it looks like only one set of first and last names is being parsed. Here is the current output:

Array

(

    [Own] => 1

    [Own2] => 5

    [sec] => Aca

    [Page1] => 100

    [Page2] => 102

    [Title] => The Page Name

    [Mods] => No Mod

    [iD] => 2

    [first] => George

    [last] => Washington

)

 

 

[fenway]

Actually, you're getting a hash collision on the names... use column aliases and your problem will be solved.

 

Try:

 

SELECT CONCAT_WS( ' ', u1.first, u1.last ) as own1Name, CONCAT_WS( ' ', u2.first, u2.last ) as own2Name

...

[bww]

Thanks! Ok, so now I have been able to get both of the peoples name's but how can I display other information from the pages table?

$result = mysql_query("
SELECT CONCAT_WS( ' ', u1.first, u1.last ) as own1Name, CONCAT_WS( ' ', u2.first, u2.last ) as own2Name
FROM pages as p
inner join users as u1 on (p.own=u1.id)
inner join users as u2 on (p.own2=u2.id)


");
   

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     iterateArray($row);

}

function iterateArray($arr) {
   echo "<pre>";
   print_r($arr);
   echo "</pre>";
}


mysql_close;
?>

[fenway]

Same thing... alias the column names using as to create unique names.

[bww]

Thanks for all of your help!