[Resolved] confused with joins

vavavince

hi! i hav the main table with the fone id, model, make, features.. and i also hav another table of fone prices with fone id, price, dealer.. coz 1 fone model could have diffrnt prices with other dealers right?.. now this is the problem..

i hav a search page that will display the phone models that fit the criteria.. one criteria is price.. now thats on another table, i just want the price query to return the fone id..

this is what ive got so far..

$price=$_POST['price'];

$query="SELECT * FROM fones2, prize WHERE
bt LIKE '%$bt%' AND
ir LIKE '%$ir%' AND
wap LIKE '%$wap%' AND
mp3 LIKE '%$mp3%' AND
radio LIKE '%$radio%' AND
stat LIKE '%$stat%' AND
cam LIKE '%$cam%' AND
make LIKE '%$make' AND
rington LIKE '%$rington%' AND
messaging LIKE '%$messaging%' AND
frmdsign LIKE '%$frmdsign%' AND
dispcolor LIKE '%$dispcolor%' AND
(priceprize >= '$price') ORDER BY make ASC";

pls dnt laff.. hehe.. thanks..

zhabala

;-) You are on the right way, but you forgot to join the tables so try this (I don't know, what are the column names for your phone_id in both tables I assume that it is named fone_id):

$query="SELECT * FROM fones2, prize WHERE 
fones2.fone_id = prize.fone_id AND
bt LIKE '%$bt%' AND 
ir LIKE '%$ir%' AND 
wap LIKE '%$wap%' AND 
mp3 LIKE '%$mp3%' AND 
radio LIKE '%$radio%' AND 
stat LIKE '%$stat%' AND 
cam LIKE '%$cam%' AND 
make LIKE '%$make' AND 
rington LIKE '%$rington%' AND 
messaging LIKE '%$messaging%' AND 
frmdsign LIKE '%$frmdsign%' AND 
dispcolor LIKE '%$dispcolor%' AND 
(priceprize >= '$price') ORDER BY make ASC";

vavavince

hehe!! that got it.. but i found a problem.. each phone model has diffrnt prices, and diffrent dealers.. now, if i search with a budget of 2000 pesos, all models of that price will pull up.. thats ok.. but the problem is, if that unit has 3 prices, the query will echo it thrice.. i think i hav to pass it to an array first and check for duplicates.. do u hav a snippet that could do it? that is, check 4 duplicate model id and pass it into the array if unique, then echo the array.. thanks..

zhabala

So it depends on the list which you want to get. In case you want to have only unique phone models without prices and dealers you can just use DISTINCT SELECT like this

SELECT DISTINCT fone_id, fone_name, fone_blabla, make FROM fones2, prize WHERE

fones2.fone_id = prize.fone_id AND
bt LIKE '%$bt%' AND

ir LIKE '%$ir%' AND

wap LIKE '%$wap%' AND

mp3 LIKE '%$mp3%' AND

radio LIKE '%$radio%' AND

stat LIKE '%$stat%' AND

cam LIKE '%$cam%' AND

make LIKE '%$make' AND

rington LIKE '%$rington%' AND

messaging LIKE '%$messaging%' AND

frmdsign LIKE '%$frmdsign%' AND

dispcolor LIKE '%$dispcolor%' AND

(priceprize >= '$price') ORDER BY make ASC

in case you want to have list of unique phone models, but with the information about prices and dealers, you have to use another way, probably more SELECT statements or other.

Zdenek