left join

[techker]

hey guy i doing a litle script for renting dvd's..

 

i got the DB OUT for rentals and employee for id of the person renting it..

 

So on my page account i wan't to list the rented dvd's from the employee

 

need to check the OUT database and the Location database for the movie title and pics..

 

i got this but i got a syntax error..

 

$query = "SELECT OUT.Employer,OUT.DVD_ID, Location.L_ID ".
"FROM OUT, Location ".
"WHERE OUT.DVD_ID = Location.L_ID AND OUT.Employer = $Account";

 

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUT, Location WHERE OUT.DVD_ID = Location.L_ID AND OUT.Employer = techker' at line 1

 

[smerny]

the error is because "out" is a reserved word in mysql. best thing to do would be to not use reserved words for names of tables/columns

 

here is a list of reserved words for reference:

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

[techker]

ahh...lol thx

[techker]

last thing..now i get

 

Unknown column 'Techker' in 'where clause'

 

i think it's in my were clause?im missing something no?

WHERE OUT.DVD_ID = Location.L_ID AND OUT.Employer = $Account";

[Jessica]

You need single quotes around a string. I assume $Account = 'Techker'. It thinks you're looking for a column since it's not within quotes.

[techker]

wow fast..

 

will this formula do what i need?cause now im getting zero output from it..

[cpd]

You can use reserved words, just make sure you wrap them in slanted apostrophes. There's no need for you to have a full stop at the end of each line either.

 

$sql = "SELECT `OUT`.`Employer`,`OUT`.`DVD_ID`, `Location`.`L_ID` 
            FROM `OUT`, `Location`
            WHERE `OUT`.`DVD_ID` = `Location`.`L_ID` AND `OUT`.`Employer` = '{$Account}'";

 

If your not getting any results ensure a result set is being returned and no errors are occurring with the mysql_errors() function. If everything's fine, you probably don't have any results matching that query.

 

Moreover, with joins such as these you can easily get ambiguity so be careful.

[techker]

ok cool thx!

[techker]

still doesnt work..fill the database..

 

so my goal is to check the rentals done by the loggin in user.

 

so in rentals is have

R_ID DVD_ID Employee LP Date

 

then i want to go in the Locations(dvd's) and get the DVD info that he rented.(DVD_ID)

L_ID Titre Date Prix Categorie Image Status UPC

 

so basically when he goes to the account page it shows his rented movies..

[cpd]

Re-read my post and try doing a bit of debugging as described.