I am trying to create a webpage that searches a MySQL database. I can write a simple page that can search on one of the fields. For example: SELECT * FROM table WHERE = $variable;
What I need to do is figure out how to do the same thing but with more than one field (and they might be empty). For example: If there are three fields for entering search terms: Name, Rank, SerialNumber, but the user only puts in less than all of them, I want the query to be something like SELECT * FROM table WHERE name` LIKE '%Named Person%' OR rank LIKE '%Rank Amateur%'
However, I don’t want it to try and add OR serialnumber LIKE'%%' because that would mess everything up.
If anybody could point me in the right direction, I would appreciate it.
Comments
5 responses to “The PHP problem I am having”
What’s the objection to the second “OR” and the third search field? Is it that it’s the third variable? Is it that it’s the final variable? Is it the data types? Are you hoping for some sort of cool “foreach” thing so you don’t have to decide beforehand how many possible fields you can search so you don’t have to manually code them?
-Tom
Some of the pages will have sixteen possible search fields. If I can get the easy three variable page done right, then I shouldn’t have too much of a problem with the sixteen variable page.
At this point, I don’t care if the thing is cool or messy, I just have to get it work. Somebody else can worry about maintaining it. 🙂
The problem is the empty form fields, isn’t it? So, we set the empty ones to something completely outside the realm of possibility.
Okay, it’s a total hack. But…
$my_name = ($_POST[‘name’] != ”) ? $_POST[‘name’] : “xyzzy”;
$my_rank = ($_POST[‘rank’] != ”) ? $_POST[‘rank’] : “xyzzy”;
$my_serialnumber = ($_POST[‘serialnumber’] != ”) ? $_POST[‘serialnumber’] : “xyzzy”;
$query = “SELECT * FROM table WHERE (name LIKE ‘%$my_name%’ OR rank LIKE ‘%$my_rank’ OR serialnumer LIKE ‘%$my_serialnumber%’)”;
It works, if you can stand it. I kinda need a bath just for suggesting it, but it’s all I got.
-Tom
Please fix “serialnumer” to “serialnumber” in the $query line… 🙂
Thanks, Tom.