Hi guys, trying to let my users do a custom database search, I.e. they can define they want to search for certain things in certain columns but leave out other search criteria.
How could I have one query which is controlled by which options the user chooses in the search form?
I was thinking that, I could have certain variables containing clauses, and do something like:
if ($GET_searchclause1) {
$searchclause1 = WHERE columname LIKE '$GET_searchclause1'
}
if ($GET_searchclause2) {
$searchclause2 = AND columname LIKE '$GET_searchclause2'
}
And then in the query have
select * from table $searchclause1 $searchclause2
Is this methodology correct? or can someone suggest a better alternative. thanks a million.That's the way I would do it.
I'd start with a variable containing the base of the query(tables, columns, etc.) and then run your search variables through some if statements. If the statements are true append to the query variable and eventually submit it to MySQL.
- BradCheers, just thought it might not work having variables in the query which could be empty.
that will not work. if "$searchclause1" is empty and "$searchclause2" is not is empty the query will error out.
you must have a constant search word in the fields. can you give more info on what you are trying to do?Well I want a search function which allows users to customise their search and the query then changes depending on what fields the user has entered strings into.that sounds reasonable and doable but try doing it without adding the where in one fo them unless you know that will never be empty. that shoudl only be 1 search field but you can have other dropdown or radio button fileds that can add to the query.
what does the html look like?Haven't done it yet.well it really depends on what you have in your form. when you complete it and get stuck again on the query, post back and we will get you through it. 
Will do thanks mate. In fact I havent even started this part of the script yet, I was just sort of asking in advance. 
How could I have one query which is controlled by which options the user chooses in the search form?
I was thinking that, I could have certain variables containing clauses, and do something like:
if ($GET_searchclause1) {
$searchclause1 = WHERE columname LIKE '$GET_searchclause1'
}
if ($GET_searchclause2) {
$searchclause2 = AND columname LIKE '$GET_searchclause2'
}
And then in the query have
select * from table $searchclause1 $searchclause2
Is this methodology correct? or can someone suggest a better alternative. thanks a million.That's the way I would do it.
I'd start with a variable containing the base of the query(tables, columns, etc.) and then run your search variables through some if statements. If the statements are true append to the query variable and eventually submit it to MySQL.
- BradCheers, just thought it might not work having variables in the query which could be empty.
that will not work. if "$searchclause1" is empty and "$searchclause2" is not is empty the query will error out.you must have a constant search word in the fields. can you give more info on what you are trying to do?Well I want a search function which allows users to customise their search and the query then changes depending on what fields the user has entered strings into.that sounds reasonable and doable but try doing it without adding the where in one fo them unless you know that will never be empty. that shoudl only be 1 search field but you can have other dropdown or radio button fileds that can add to the query.
what does the html look like?Haven't done it yet.
well it really depends on what you have in your form. when you complete it and get stuck again on the query, post back and we will get you through it. Will do thanks mate. In fact I havent even started this part of the script yet, I was just sort of asking in advance.