Not getting all the data using LIKE

Databases
1

I am making a search feature that looks for either the location, the category or both. My search for category works, but for the city name I am not getting all the cases.

When the form is filled in, the city field could contain just one city name (ie, Toronto), or it could contain a list of locations (ie, Toronto, Montreal, Vancouver, all of Manitoba). This does not seem to affect which items are displayed, and which ones are left out.

Is there something wrong with my SQL query?

 $query = "SELECT id, org_name, prog_name, tollfree, email 
     
                               FROM organizations";
                    if ($search_city == '' && $search_cat != '') {
                        $query .= " WHERE org_type = '$search_cat'";
                    } else if ($search_city != '' && $search_cat == '') {
                        $query .= " WHERE geo_area LIKE '%$search_city%'";    
                    } else if ($search_city != '' && $search_cat != '') {
                        $query .= " WHERE org_type = '$search_cat'
                                            AND geo_area LIKE '%$search_city%'";
                    }
                    $query .= " ORDER BY org_name ASC";
2

OK, I’m confused. Will the TABLE have a list of location, or will the search field have a list of locations? That’ll change the answer I’d give you.

Just to start out, and perhaps you’re already doing this, but please, Please, PLEASE consider switching to PDO prepared statements instead of straight SQL like this. Using prepared statements gives you an extra level of protection from SQL injection issues. I know you may want to be getting something done quickly, but trust me - you’ll be glad you switched the sooner you do it…

That being said, if your search field can bring back the multiple values, that maybe your problem. You can do something like this (done in your style for quick and dirty purposes)

<?php
$query = "SELECT id
	       , org_name
	       , prog_name
	       , tollfree
	       , email 
            FROM organizations
	   WHERE 1 = 1";
$where = "";
$count = 0

// check city first	
foreach ($_POST['searchCity'] as $selectedCity) {
	$where .= ($count > 0 | ", " : "") . "'$selectedCity'";
	$count++;
}
if (strlen($where) > 0) {
	$where = " AND geo_area IN (" . $where . ")";
}
if (strlen($search_cat) > 0) {
	$where .= " AND org_type = '$search_cat'";
}
// add the where clause and the order by
$query .= $where . " ORDER BY org_name ASC";
%>

Now if the table has a list of values in it, I want you to go over to your desk, get the ruler out and smack yourself on the back of the hand five times, and repeat the following mantra ten times: “I will not forget to normalize my tables!” If you have a field on a table which is essentially a list of values, nine times out of ten (if not 99 times out of a 100), that should be in a separate look up table. So your search would be something like this:

<?php
$query = "SELECT DISTINCT id
			   , org_name
			   , prog_name
			   , tollfree
			   , email 
            FROM organizations o
			JOIN organizationLocation OL ON o.id = OL.organizationID
		   WHERE 1 = 1";
$where = "";

// check city first	
if (strlen($search_city) > 0) {
	$where .= " AND geo_area = '$search_city'";
}
// then cat
if (strlen($search_cat) > 0) {
	$where .= " AND org_type = '$search_cat'";
}
// add the where clause and the order by
$query .= $where . " ORDER BY org_name ASC";
%>
3

The data comes from an html form that the organizations fill out. The location field is a textbox, so they have some flexibility in what they enter here (hence my two examples in my opening post), and the category field is a drop-down.
All the data goes into a single table ‘organizations’.

The search feature is a simple one with two drop-downs - one for the locations, and one for the categories. The locations dropdown only lists those locations that are of particular interest in the search.

The user can search using locations, or categories or both, but each drop-down in the search brings back only one field.

By the way, I am using PDO prepared statements. I just showed this part of the code, because my question was a mySQL question, not a PHP question. Sorry for the confusion.

4

From what you’re saying here, I’d guess you have a data problem and/or a process problem. If the organizations can fill in anything they want, but you’re limiting what the searching user can search for, the chances of records being missed greatly increases. If the organization can fill in anything they want, then there can be typos, abbreviations, etc.

Might I suggest a change to the way the organizations enter data? Perhaps if they had a series of checkboxes they could fill in for areas covered, with perhaps five blank boxes which can have data entered for additional cities, which can then be validated later. This would allow them to quickly identify the areas they cover, but allow them to add areas which are missing (which would be added that other organizations could then also select later).

In the short term, without seeing a data dump for organizations, and examples of searches which are missing results, it’s hard to figure out why you’re not getting the results you want. The basic premise you’re using as a sample seems right, but without seeing data and hard examples, it’s hard to tell for sure.

5

I went through the table (only 84 records) to see if there was a pattern, and also corrected any typos. What I found was that if there were 15 records that contained ‘Montreal’ for example, even if that was the only city in the field, not a list of cities, I would get back maybe 7 of them. That is why I thought my %$search_city% code might be wrong.

I like your suggestion for changing the way the location is collected in the first place. I will check with my client on that one. This is a previous form and database that had many issues, and I have been asked to try to rescue it and make the display of the results more usable to his people.

6

Hmmm. Perhaps an encoding issue? Or do you have the accent over the a in Montreal (ALT+133 - Discourse won’t let me type it here)

Montréal

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.