October 22nd, 1999, 12:22 AM
I'm currently designing a site that queries mysql for matches, but depending on the input sometimes wildcards are required to show all results in a particular field.
ie. person chooses "show all" instead of a particular item category, and then I need to push the result of that being a wildcard into the database query ie.
select * from atable where afield = '*' and anotherfield = 'acat'
Is using wildcards in the above example possible? Are there better ways of doing this that I'm missing?
The problem is, that because up to 10 fields can either contain an exact category or if the person wants all the results, the script needs to flag the part of the where clause as a wildcard.
If I have individual queries to cover all the possible instances of a wildcard being required (ie leave the particlar match out of the database query) then I would need over 100 different queries.
any help would be appreciated, as I'm running out of ideas :-)
October 22nd, 1999, 05:05 PM
I ran into the same task.
I have a form with 5 fields where a user can fill up to build a query.
Some of the fields might not be used so I have to "build" the where clause based on whether data was entered in a field.
First, I created the query variable:
$query = "select * from my table where flag = 'pickme' ";
Luckily, every query made will have this flag set making the building of the where clause easier.
I then made a function called buildwhere. This function needs 3 arguments: the $query, 'fieldname', $var1(from the form).
The function checks to see if the $var1 that was passed is null. If it is not, an expression gets added to the passed query. The query then gets returned for the next field.
<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
/this is from the main function.
$query = "select * from mytable where flag = 'pickme' ";
$query = buildwhere($query,'field1',$var1);
$query = buildwhere($query,'field2',$var2);
$query = buildwhere($query,'field3',$var3);
if ($datavalue != "")
$clause .= "AND $field LIKE '%datavalue%' ";
I used LIKE instead of the = because the user might not enter the data just like the way it was entered in the field. Not 100% but, close enough.
Note the ' "; There is a space between the ' and the ".
I hope this helps.
October 22nd, 1999, 05:45 PM
Hi Rommel, appreciate your response.
I've tried something pretty similar to that actually (the idea of building a new query based on reponse)...but the way you've done it is probably a lot better...I'll have a play :-)
I'm pretty new to using mysql as a backend, so I'm learning as I hit problems.
October 22nd, 1999, 05:56 PM
yep...your idea will work great...I must have been thinking very box like. I was having a problem with my own script when building the query because I didn't think of setting a default flag at the beginning of the clause to take care of the first part not having an "and".
Really appreciate your help..you've saved me lots of headaches! :-)