Hey Everyone,

I am having a bit of a problem with trying to display the correct results from the data in a query. So far i only have a form with 4 controls, that will change as soon as i can get these four working fine. The problem lies in the way my constrictions are constructed..... The 4 controls i have are two text fields, and two drop down lists. Two text fields are named 'Ticket ID' and 'Car Model Name', the two drop down lists are named 'Transport Type' and 'New/Used'. The problem i am experiencing when i submit the form is if i for example type in the number 11 in the Ticket ID textfield, the wrong results are shown. That ticket ID does exist. Here is how my constrictions look like for the where clause...

WHERE car_classifieds.carmodel.online = 1
AND car_classifieds.carmodel.carTypeID = car_classifieds.cartype.carTypeID
AND car_classifieds.carmodel.carModelID = car_classifieds.ticket.carModelID
AND (car_classifieds.ticket.ticketID = #URL.ticket#
OR car_classifieds.carmodel.carModelName LIKE '%#URL.carmodelname#%')
AND car_classifieds.carmodel.TypeOfTransport = '#URL.transport#'
AND car_classifieds.carmodel.carNewOrUsed = '#URL.newused#'

The above code is incorrect as it displays the incorrect results, it is constructed incorrectly - the syntax i'm assuming is fine because i dont get any errors. If i were to move the 'car_classifieds.ticket.ticketID = #URL.ticket#' constriction out of the paranthesis and add it to the bottom of the code as 'AND car_classifieds.ticket.ticketID = #URL.ticket#' then when i type in ticket ID 11 again, it shows me the correct results. Problem is that when i submit the form without filling anything in the ticket ID textfield, nothing shows up at all because it is expecting some value to be sent as a parameter for the ticket ID field.

If anyone can help me construct that correctly i would really appreciate it :).

Thanks for your time,

- CK

Hey CK!

In your situation, I always put the join conditions first and in brackets, because I want this to be true always, then put the rest to the back:


WHERE
(car_classifieds.carmodel.carTypeID = car_classifieds.cartype.carTypeID
AND car_classifieds.carmodel.carModelID = car_classifieds.ticket.carModelID)
AND car_classifieds.carmodel.online = 1
AND car_classifieds.carmodel.TypeOfTransport = '#URL.transport#'
AND car_classifieds.carmodel.carNewOrUsed = '#URL.newused#'
AND (car_classifieds.ticket.ticketID = #URL.ticket#
OR car_classifieds.carmodel.carModelName LIKE '%#URL.carmodelname#%')


Now, your problem seems to be that you are restricting, no matter if get input or not.
Say, I don't give you a type of transport.
Then your query looks for rows, where the type of transport is empty - thats not what you want.

You need to build up your query according to the the input-data the user gives you, then you will be fine.

Happy coding!

Hey duesi! :D

Thanks for the advice, i employed it into the constrictions. I realized the problems i was experiencing with getting the wrong results was due to 'results' page being sent empty paramaters to fields that the SQL construct required. I changed things around by adding <cfif> statements so that the 'results' page gets sent the correct data. So the Advanced Search works perfectly fine :).

I'm almost about done with this Car Classifieds project, hopefully if i get IIS up and running i could send you a link to check it out :).

Thanks again for your help,

- CK

CK,
it was fun to see your program "materialize" up step by step!
I learned a lot from this experience, and I am looking forward to see the finished product!
(Remember the Matra of Open Source: "Release early, release often")

Happy Coding!