SQL Query

oscillate

I have the following setup, and i need to pretty much perform a quad join as far as i am aware. But im not sure how i would write the sql query. and do i have all the fields in each table required to join them all together? thanks heaps

i tried this, but it dont work properly

$query_rslist_classifieds = sprintf("SELECT c.catID, c.catname, a.item_name, a.item_desc, a.item_image, p.phone FROM classified_cats c, classified_ads a, customer p, magazine m WHERE a.fk_catID = c.catID AND a.fk_customerID = p.customerID AND a.fk_magID = m.magID AND c.catID = %s AND m.magname = 'AJM'", $colname_rslist_classifieds);
/*
TABLES:

classifieds_ads: adID, fk_catID, fk_customerID, fk_magID, item_name, item_desc
customer: customerID, company_name, phone, email
classified_cats: catName, catID
magazine: magID, magname, current

I NEED TO ECHO: (but this first: magname = 'AJM', current = 'Y')

item_name
item_desc
company_name
phone
email
*/

Roger_Ramjet

Just a start, try this

"SELECT c.catID, c.catname, a.item_name, a.item_desc, a.item_image, p.phone 
FROM classified_cats c INNER JOIN classified_ads a ON c.catID=a.fk_catID
INNER JOIN customer p ON a.fk_customerID=p.customerID
INNER JOIN magazine m ON a.fk_magID=m.magID
WHERE c.catID = %s AND m.magname = 'AJM'"

I'm sure it won't work because I don't see how c.catID = %s can work. Should it not be something like c.catID='%$s' ??

AstroTeg

Originally posted by Roger Ramjet

I'm sure it won't work because I don't see how c.catID = %s can work. Should it not be something like c.catID='%$s' ??

The way he has it will work. He's using the power of [man]sprintf[/man] and it'll swap out the %s with his data.

Oscillate: for SQL safety, you may wish to change the %s to %d. I'm assuming c.catID is numeric. The %s will work and PHP will do the conversion, but you may find it safer to use %d since you'll want an integer and you might as well let sprintf know its to be treated as an integer. If funky text get put in there, I think sprintf will put in a 0. Probably not the desired result, but possibly much better than someone trying to do some SQL injection...

oscillate

awsome. you dont think it would be better to do like left and right joins (im not sure how to do them)

thanks for the replys guys