Using One Search Field to Represent several (help! ;-)

[bienville]

Not sure even how to make the best subject for this... 

 

I have a simple form to view meetings for an organization.  The user can select a time and a city etc...  Now they want to be able to define a geographical region for the search.  So instead of searching 3 times by 3 cities, the user can just select 'region 1' get the results for all cities we hard code as region 1.

 

(clear as mud?)

 

Here's the code:

 

if($param['Submit'])
{
$tags = '';

if(count($param['tag']) > 1)
{
	foreach($param['tag'] as $v)
	{
		$tags .= "tags LIKE '%$v%' AND ";
	}
}
else
{
	if($param['tag'] != '')
		$tags .= "tags LIKE '%".$param['tag'][0]."%' AND ";
}
if(strlen($tags) > 0)
	$where[] = substr($tags, 0, -5);
if($param['time'])
{
	$tmptime = $param['time'] + 100;
	$where[] = "time >= '".$param['time']."' AND time <= '".$param['time2']."'";
}
if($param['day'])
	$where[] = "day = '".$param['day']."'";
if($param['city'])
	$where[] = "city = '".$param['location']."'";
$where[] = "published = '1'";
if(count($where) > 0)
	$whereStr = " WHERE " . implode(' AND ',$where). " ORDER BY date,time ASC";

//die('debug: ' . $whereStr);
$sql = "SELECT * FROM meeting$whereStr";

$result = mysql_query($sql);

$intCounter = 0;

$output = "<table width=\"90%\" cellpadding=\"0\" id=\"meeting_t2\" style=\"border: 0\" rules=\"ALL\"><tbody><tr><th>Day</th><th>Time</th><th>Name</th><th>Address</th><th>City</th><th>Tags</th><th>Details</th></tr>";

while($row = mysql_fetch_assoc($result))
{
	$intCounter++;
	$output2 = "<tr><td>".$row['date']."</td><td>".date("g:i a", strtotime($row['time']))."</td><td>".$row['meetname']."</td><td> (google maps URL stuff clipped) $row['address']."</a></td><td>"." ".$row['city']."</td><td>"." ".$row['tags']."</td><td>".$row['description']."</td></tr>";

	if($row['status'] == '0')
	{
		$output2 = str_replace('<td>','<td><S>',$output2);
		$output2 = str_replace('</td>','</S></td>',$output2);
	}

	$output .= $output2;
}

$output .= "</tbody></table>";


if($intCounter == 0)
{
	$output .= "No results";
}

echo $output;
echo "<br /><br /><br />";
}

?>

 

So at the top of the 'city' pull down they want 'Region 1' Region 2' etc.  If a user selects Region 1 they get the results from all the defined cities.

 

What I think I need is another IF up there at the top that says If city = region1 then city = Boston + NYC + Albany etc...

 

But I have no idea how to say that in php/sql.

 

If making multiple regions is harder, I can live with one region.

 

many thanks

 

[monkeytooth]

I'd add a new column to my db table that represents the current data set. The new column would specify region1, region2.. respectfully to what ever city falls into the region you want it to.. then just query for that alone when you want to list all the cites within a specific region..

 

ie

if($city == "region1")
{
     $sql = "SELECT * FROM myTableName WHERE myNewColumnName = "region1";
}
else
{
    //existing code you already have?
}

[bienville]

I thought about doing this but then as people enter meetings they need to put in the city and the region. For logistical reasons, that's a showstopper.

 

But thanks, it would work technologically, but I can't make it fit the workflow sadly.

[kickstart]

Hi

 

Variation on monkeytooths suggestion.

 

Have a table of cities and the regions they represent. You would need to set this up in advance (but you will whichever way you use cities).

 

Then if they specify a region use a JOIN.

 

Something like this:-

 

<?php
if($param['Submit'])
{
$tags = '';

if(count($param['tag']) > 1)
{
	foreach($param['tag'] as $v)
	{
		$tags .= "tags LIKE '%$v%' AND ";
	}
}
else
{
	if($param['tag'] != '')
		$tags .= "a.tags LIKE '%".$param['tag'][0]."%' AND ";
}
if(strlen($tags) > 0)
	$where[] = substr($tags, 0, -5);
if($param['time'])
{
	$tmptime = $param['time'] + 100;
	$where[] = "a.time >= '".$param['time']."' AND a.time <= '".$param['time2']."'";
}
if($param['day'])
	$where[] = "a.day = '".$param['day']."'";
if($param['city'])
	$where[] = "a.city = '".$param['location']."'";
if($param['region'])
	$where[] = "b.region = '".$param['region']."'";
$where[] = "a.published = '1'";
if(count($where) > 0)
	$whereStr = " WHERE " . implode(' AND ',$where). " ORDER BY a.date, a.time ASC";

//die('debug: ' . $whereStr);
$sql = "SELECT a.date, a.time, a.meetname, a.address, a.city, a.tags, a.description, a.status, b.region FROM meeting a INNER JOIN cityregions b ON a.city = b.city $whereStr";

$result = mysql_query($sql);

$intCounter = 0;

$output = "<table width=\"90%\" cellpadding=\"0\" id=\"meeting_t2\" style=\"border: 0\" rules=\"ALL\"><tbody><tr><th>Day</th><th>Time</th><th>Name</th><th>Address</th><th>City</th><th>Tags</th><th>Details</th></tr>";

while($row = mysql_fetch_assoc($result))
{
	$intCounter++;
	$output2 = "<tr><td>".$row['date']."</td><td>".date("g:i a", strtotime($row['time']))."</td><td>".$row['meetname']."</td><td> (google maps URL stuff clipped)".$row['address']."</a></td><td>"." ".$row['city']."</td><td>"." ".$row['tags']."</td><td>".$row['description']."</td></tr>";

	if($row['status'] == '0')
	{
		$output2 = str_replace('<td>','<td><S>',$output2);
		$output2 = str_replace('</td>','</S></td>',$output2);
	}

	$output .= $output2;
}

$output .= "</tbody></table>";


if($intCounter == 0)
{
	$output .= "No results";
}

echo $output;
echo "<br /><br /><br />";
}

?> 

 

All the best

 

Keith

[bienville]

Ah Ha! I was having a lot of trouble comparing the two but I just ran both code snippets though http://www.textdiff.com/ and I still don't get it yet but BOY is it easier to look at. (bookmarked!)

[bienville]

I'm on the verge of busting this wide open.... I can do this with one line of code...  I just need help with syntax.

 

In looking at the code, it just builds a big WHERE statement. So I added 3 lines of code after the IFs to test.

 

echo $whereStr  . "<br />";
	$whereStr = " WHERE time >= '0500' AND time <= '2359' AND location = 'city 1' OR location = 'city 2' AND published = '1' ORDER BY date,time ASC";
echo $whereStr  . "<br />";

 

Ok so the top line shows me the -real- WHERE statement as it gets built from the input the user selects

The second line of code force feeds it a WHERE statement of my choice.

The third line confirms line 2

 

Surprise surprise, no matter what I ask it for, when I hit submit , it shows me the same results from my hard coded WHERE statement.

 

Then it hit me... Force feed it the WHERE string of choice in my select... here's the code for picking the city (as I just altered it.)

 

					<select name="City" style="width:180px;">
					<option value="">Any City</option>
					<option value="City 5' OR location ='City 7' OR location ='City 9">Region 1</option>
                                                <option value="city1">City 1</option>

 

It's a simple little hack that simply force feeds the WHERE statement I want. -- So one line of code will fix my problem.

 

Only one problem (and this is where you guys come in) I can't get the quotes right.  Here is what the above outputs:

 

WHERE time >= '0500' AND time <= '2359' AND location = 'City 5\' OR location =\'City 7\' OR location = \'City 9' AND published = '1' ORDER BY date,time ASC

 

Magic quote are off in both the php.ini and the .htaccess. (and I know they are off because I had to do that for another hunk of code)

 

I just can't get rid of the backslashes... If I can figure out syntax, I can just stuff the criteria of my choice in the in the selects and be done with it.

 

I'll figure it out if I play with it long enough, but if anyone can save me some stress, I'd appreciate it.

[kickstart]

Hi

 

Trouble with that is you are making it VERY easy for anyone to try SQL injection.

 

To avoid delimiting the inverted commas you would need to avoid using mysql_real_escape_string, but that would be pretty dodgy.

 

All the best

 

Keith

[bienville]

hmmm... isn't that the case either way?  (whether I do this or or do it another way)

 

In which case I guess I'm asking, is the underlying code flawed? (I did not write it)

 

Seems to me I'm not introducing a potential exploit, I using an existing one...  Or am I missing something?

 

thanks

[kickstart]

Hi

 

You would use mysql_real_escape_string on the variables which would prevent anyone putting a ' inside the variable to take control. However you NEED to have the quotes in the variable (which you are accepting from the user) hence can't block them with mysql_real_escape_string. And so land up leaving yourself open to sql injection.

 

You code already has mysql_real_escape_string in it, in the code you passed earlier. You have looped through $_POST and escaped all the variables and put them into the $param array.

 

All the best

 

Keith

[bienville]

lol OK I  am sooo in the weeds :'(  But 20 minutes of googling and reading and I think I'm with you....  :rtfm:

 

From reading on php.net I have two different ideas:

 

1)  How about 'force feeding it in the IF section.

 

if city = region 1

  city = "force fed string with hard coded cities"

else

  legacy code

 

This would be after the mysql_real_escape_string correct????  (And if this would work, some syntax help would be appreciated.)

 

-------

 

2) It LOOKED LIKE (from the way it was worded) that I could use stripslashes to strip the slashed on the $whereStr safely. It absolutely delivered the strong I wanted:

 

I did an echo stripslashes($whereStr  and it worked perfect... but is it safe? I'm not bypassing mysql_real_escape_string am I?

 

(yes I know these are kludges but I just need to get this finished)

 

If nothing else, I've learned more on this problem than I ever imagined I would.

 

[kickstart]

Hi

 

Using strip slashes like that would be very unsafe. All it takes is for someone to pass a value of something like yes');drop table blah; (bit more than that, but not much) and you would execute a very nasty command.

 

If you want to use regions like that then I would suggest something like this:-

 

$CityRegions = array('Region 1'=> array('Some City','Another City'), 'Region 2'=> array('Ye Another City','SmallVille'));
if (array_key_exists($City,$CityRegions))
{
$sql .= "City IN ('".implode("','",$CityRegions[$City])."')";
}
else
{
$sql .= "City = '$City'";
}

 

That would take the city, and if it was a region it would use the list of cities defined for that region in the array, if not just check the city matches the specified one (assuming $City already has mysql_real_escape_string applied to it).

 

It would still be best to redesign things to be more database based, but this should cover it as it is.

 

All the best

 

Keith

[bienville]

I ended up solving this with 3 lines of code.... (Other than the option value=Region1)

 

I moved the if for the city to the top and did a small mod:

 

if($param['city'])
	if($param['city'] == "Region1")
		$where[] = "(city= '"."Town 1' OR city ='Town 5' OR city ='Town 7' OR city='Town 9'".")";
		else
		$where[] = "city= '".$param['city']."'";

 

I realize this won't method won't put me in the Geekboy Hall of Fame and there was probably a better way than nested ifs.

 

But it works and that's all that matters this week. ;-)

 

 

BTW from the original post:

 

What I think I need is another IF up there at the top that says If city = region1 then city = Boston + NYC + Albany etc...

 

But I have no idea how to say that in php/sql.

 

Needless to say, now I do.