[SOLVED] table joins

[richardjh]

This is a follow on from another post regarding Amazon ranks lists but i thought it best to start another thread as it is an entirely separate problem.

 

this bit of code:

 

$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title FROM Booklinks,Submit WHERE Booklinks.amazonuk != '' AND Booklinks.oid=Submit.oid";

 

works brill and will pull ALL the rows from the Booklinks table providing they contain something in 'amazonuk' No problems there.

 

I now find myself stuck (yes again) try to use the same query but wanting to pull only the rows that contain the username of the logged on user.

 

BOTH tables (Booklinks & Submit) contain a row called username and what I'm now trying to do is show a logged ON user a list of ranks for only HIS books rather than the original list containing ALL books that meet the sql query above.

 

I tried this:

$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title FROM Booklinks,Submit WHERE Booklinks.amazonuk != '' AND Booklinks.username='$username'";

my logic being that the query will look in Booklinks table in the username row for $username (the logged on user). However this results in the webpage loading a long time and then timing out.

 

Removing the quotes around $username thus:

$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title FROM Booklinks,Submit WHERE Booklinks.amazonuk != '' AND Booklinks.username=$username";

throws up this error:

Unknown column 'BillBloggs' in 'where clause'

 

I've tried a few other methods of adjusting the sql query  - all to no avail. I'm so sorry for acting so thick especially as this *seems' to be a very easy thing to achieve (not for me Ahem!)

 

thanks in anticipation.

 

R

 

 

[hitman6003]

$username is a string and should be in single quotes in your query:

 

$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title FROM Booklinks,Submit WHERE Booklinks.amazonuk != '' AND Booklinks.username='$username'";

[richardjh]

yep thanks hitman. Using this:

$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title FROM Booklinks,Submit WHERE
Booklinks.amazonuk != '' AND Booklinks.username='$username'";

doesn't cause any errors - but it also doesn't work. I just have a  log wait as the page loads and then returns nowt (Yorkshire speak for nothing )

[Barand]

Your second query

<?php
$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title 
          FROM Booklinks,Submit 
          WHERE Booklinks.amazonuk != '' AND Booklinks.username='$username'";

 

has omitted the vital table join condition "Booklinks.oid=Submit.oid". This means every record in Booklinks is joined with every record in Submit. So if there are 1000 recs in each table you get 1,000,000 rows returned - hence the long time to run.

 

It's much better to use this join syntax which keeps the join condition separate from the selection criteria and makes the type of error above far more obvious:

 

<?php
$sql = "SELECT Booklinks.oid,Booklinks.amazonuk,Submit.title 
          FROM Booklinks 
                 INNER JOIN Submit ON Booklinks.oid=Submit.oid
          WHERE Booklinks.amazonuk != '' 
                 AND Booklinks.username='$username' ";

[hitman6003]

You aren't actually doing the join...

 

SELECT b.oid, b.amazonuk, s.title
FROM Booklinks b
  JOIN Submit s ON b.username = s.username
WHERE b.amazonuk != '' AND b.username = '$username'

 

You could also do it using the "shorthand" syntax:

 

SELECT b.oid, b.amazonuk, s.title
FROM Booklinks b, Submit s
WHERE b.username = s.username AND b.amazonuk != '' AND b.username = '$username'

[Barand]

As I pointed out already he IS doing a join, but not the one he wanted. He's doing a "cartesian" join - every row in one table with every row in the other

[richardjh]

Barand, hitman, I love you both and I would happily have children with you (if I was a lady). Barand's code works a treat but again I am upset that I couldn't fathom the answer without the help of you kind people of phpfreaks.

 

I think i might follow Homer's advice.. " if at first you don't succeed.. give it up!"

 

thanks is all i can offer