Goo day all

Apologies if this should rather be in the mysql forum, but it seems more of a php issue.
I'm trying to create a query that comes off an advanced search page I've made. A visitor can select any number of values from drop lists, and enter keywords. Some drop lists work together as min and max values.

I've done something like this before, and it got very messy with if statements, etc. What I'd like to do, is create the mysql query based on the options selected. The user can input text, or select options, while other are obviously left and would then not be used in the search. I do have full text enabled on 3 columns in the table I'm querying. All columns would be returned, but any number could be searched on. The search would be an AND search in the end.

Any help, suggetions, etc. would be greatly appreciated. Any worth putting this into a stored procedure?

Thanks in advance, and regards...

Hi Andrew
I did something like this a while ago, there were a number of options that the user could pick, then these were assembled into a SELECT

I can't find the source because I had a crash on my laptop and lost a load of stuff, the backups are in another place <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Anyway, the basic thing was to start with the first part of the query, something like

<pre id=code><font face=courier size=2 id=code> $query='SELECT field_1, field_2 FROM table ';</font id=code></pre id=code>

then there was a function called something like <pre id=code><font face=courier size=2 id=code>buildQuery($query, $add_on); </font id=code></pre id=code> so I could pass in the existing query together with whatever was the next criteria and the function concatenated it onto the end of the query string, I can't remember the details but I do recall that the function looked at the query that was passed in to see whether or not there was already a WHERE in it.

you could do the same thing with values from the dropdowns, but I would be hesitant to do that in case of sql injection, but you could put the actual parts of the queries into arrays . . .Oh I can see it's already getting as bad as all those if statements <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks Alan

That sounds like an idea though. Perhaps running through a fixed size array, and skipping over the empty, or null values, pop a where in at the first encountered value, and ands between the others. Sounds like quite the task though...

I did something like this with MS-SQL, using a stored procedure, but haven't been able to replicate that with mysql. At the moment I'm using the sql string right in PHP.
Also need to dig that out again though. It's on backup discs after a system restore. It went something like setting all the procedure variables to null, and then checkin that they were either equal to the current field value, or equal to null (if null, it was just ignored, if equal, it would meet the criteria).

I'm a lot newer to PHP and MySQL than ASP and MS-SQL though.

Thanks again, and any other ideas from anyone happily welcomed...

Hi Andrew,
hope you get on ok with php, myself I decided on php as it's open source, being somewhat anti-M$ <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

you probably know this, but here goes anyway . . .

in those queries watch out for single and double quotes php treats single and double quotes differently, which can mess up queries sometimes

Thanks <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Got caught with that on my first project. This is probably the third 'big' project I've done now with PHP. Hosting costs here for MS-SQL and ASP have become prohibative.

I've been toying with something. Posting the form to a page that will display the results. If those $_POST aren't empty, then set a variable to that value, if they are, then set that variable NULL.
In my SQL (I'm guessing this would have to be put in a stored procedure to work) I then test for either the variable being equal to a field, or that variable being equal to NULL. That way I can have something like:
sproc_details (name, dept)
SELECT * from table1 where (name = username OR usernme = NULL) and (depart = dept or dept = NULL)

This way, I can have my SQL look for any variable, or combination of variables.

But still a work in progress...