Help With PHP / MYSQl Search Script

[adammc]

Hi Guys,

Im racking my brains trying to get this to work!
Can anyone please help?

I want to allow users to search on the Db entries with the following conditions:

suburb - $tb (can conatin multiple items, seperated by a '+')
postcode - $postcode
category - $category
** All these fields are optional so if they dont submit any, all listings are displayed.

I am using 'get' method in my form, so the url after pressing submit looks something like this:

testing-search.php?tb=+ADELAIDE%2C+MIDGEE%2C+&u=9055&postcode=&category=32 &state=SA&action=go&mode=search&submitted=start+your+search

I assume I need to explode the $tb(suburbs) variable first as users are able to select multiple suburbs on the form?

My question, how to correctly construct the sql query?

[edited by: dreamcatcher at 9:03 am (utc) on Oct. 30, 2007]
[edit reason] Fixed side scroll. [/edit]

[Habtom]

Your idea of exploding it first and imploding it back again with the + signs back in, is a good idea.

For the rest, you can do the following:

$WHERE = '';
$WHERE = 'suburb = ". $_REQUEST['suburb'] ." OR';
$WHERE .= 'postcode = ". $_REQUEST['postcode'] ." OR';
$WHERE .= 'category = ". $_REQUEST['category'] ."';

if ($WHERE <> ''){
$WHERE = "where ".$WHERE; // Adding the where clause
$WHERE = rtrim($WHERE, "OR"); //Getting out the extra OR
}

'SELECT * FROM table1 ". $WHERE ."'

There is always a more elegant way of doing things though.

Habtom

[edited by: Habtom at 6:54 am (utc) on Oct. 30, 2007]

[dreamcatcher]

For the explode part try:

$string = '';

if ($_GET['suburb']) {

$words = explode("+",$_GET['suburb']);

for ($i=0; $i<count($words); $i++) {
$words[$i] = mysql_real_escape_string($words[$i]);
$string .= ($i? "OR suburb = '{$words[$i]}' " : "WHERE suburb = '{$words[$i]}' ");
}

// To view string..
echo $string;

}

dc

[adammc]

Hi Habtom & DC, thanks for the replies :)

Can someone please explain what the first line is doing:
$WHERE = '';

And what this is doing?
if ($WHERE <> ''){
$WHERE = "where ".$WHERE; // Adding the where clause
$WHERE = rtrim($WHERE, "OR"); //Getting out the extra OR
}

[adammc]

Hi Dreamcatcher,
Im confused :(

How do I use your code in the final query?

[adammc]

Getting somewhere, i think...

$WHERE = '';
$WHERE = "city = '". $_REQUEST[tb] ."' OR ";
$WHERE .= "postcode = '". $_REQUEST[postcode] ."'";

if ($WHERE <> ''){
$WHERE = "where ".$WHERE; // Adding the where clause
$WHERE = rtrim($WHERE, "OR"); //Getting out the extra OR
}

echo"$WHERE";
echo"<br><br>";

$query = mysql_query("SELECT * from Links $WHERE AND validated='1'") or die(mysql_error());
$num_rows = mysql_num_rows($query);

if (empty($num_rows))
{
echo "Your search has returned no results.<br />Try your <a href=\"javascript:history.go(-1)\">search again</a>";
} else {

while($r = mysql_fetch_assoc($query))
{

$link .= "<table width=\"100%\" cellpadding=\"5\" cellspacing=\"5\"><tr>";
$link .= "<td width=\"20%\"><a href=\"pages/AV$r[link_id].html\"><img src=\"$UploadURL$r[picture]\" border=\"0\"></a></td>";
$link .= "<td width=\"80%\"><a href=\"pages/AV$r[link_id].html\"><font class=\"green\">$r[business_name]</a></font><br />";
$link .= "<font class=\"grey\">$r[address]<br />$r[city], $r[state],<br />$r[postcode]</font></a></td>";
$link .= "</tr></table><br />";

}

echo "$link";
}

If I search entering a postcode only the script finds the correct entries. However if I attempt to find an entry using 'city' it find nothing.

Has anyone got any ideas?

[PHP_Chimp]

If the script is working then it would be worth checking out the contents of $_REQUEST[tb].
Try echoing the $_REQUEST array to see if [td] is getting though correctly.
<edit>
The $WHERE echo should also let you see if city is getting through.
</edit>

Also do you want to use $_REQUEST? As this take information from all types of requests. Would it be better to be using $_GET or $_POST unless you actually require a script that will work with get, post and cookie's.

[edited by: PHP_Chimp at 12:51 pm (utc) on Oct. 31, 2007]