Hey everyone,

 

I've got a search results page which queries a database of flags and their respective images.

 

The database is all set up and running fine, but now I want to add the search feature. The following code works for literal strings, i.e. searching for "Denmark" produces Denmark, but searching for "Den" doesn't produce anything:

 

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
 if (PHP_VERSION < 6) {
   $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 }

 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 switch ($theType) {
   case "text":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;    
   case "long":
   case "int":
     $theValue = ($theValue != "") ? intval($theValue) : "NULL";
     break;
   case "double":
     $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
     break;
   case "date":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;
   case "defined":
     $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
     break;
 }
 return $theValue;
}
}

if (isset($_GET['flagname'])) {
 $flagname_rsFlags = $_GET['flagname'];
mysql_select_db($database_connFlags, $connFlags);
$query_rsFlags = sprintf("SELECT * FROM flags WHERE flagname = %s ORDER BY flagname ASC", GetSQLValueString($flagname_rsFlags, "text"));
$rsFlags = mysql_query($query_rsFlags, $connFlags) or die(mysql_error());
$row_rsFlags = mysql_fetch_assoc($rsFlags);
$totalRows_rsFlags = mysql_num_rows($rsFlags);
}
?>

 

I'd like to modify (I believe it's the SQL query) to make it return results based on part-words, so searching for "Den" would return Denmark, and any other flags with "Den" in the name, like Sweden.

 

I'm fairly new to PHP but learning pretty quickly, so apologies if this is an obvious question.

 

Many thanks

 

Darren

I'm no expert, but in your query, you need to change:

 

SELECT * FROM flags WHERE flagname = %s

 

to

 

SELECT * FROM flags WHERE flagname LIKE %s

 

( http://www.w3schools.com/SQL/sql_like.asp )

 

But I am no expert... so it could be wrong lol

Edited October 28, 200917 yr by terydinho

Hi Terydinho is correct, replacing = with LIKE will do a comparison with the variable passed to it. however I would recommend putting another % at the end so it looks like

 

SELECT * FROM flags WHERE flagname LIKE %s%

 

this will do a full wildcard search and find anything that contains a search term EG. typing "mark", "enm" or "denm" should return "Denmark"

 

A great example can be found here: Click Here

 

Hope that helps, if you have any problems give me a shout

 

Chris

Hi both,

 

Thanks for the help with the code.

 

I've tried entering the code you suggested (using both suggestions), and on clicking Submit on the search, I got this PHP error message:

 

Warning: sprintf() [function.sprintf]: Too few arguments in /home/sites/jms-flagpoles.co.uk/public_html/flags-national.php on line 48
Query was empty

 

Just to clarify further, this is what the new code looks like:

 

if (isset($_GET['flagname'])) {
 $flagname_rsFlags = $_GET['flagname'];
mysql_select_db($database_connFlags, $connFlags);
$query_rsFlags = sprintf("SELECT * FROM flags WHERE flagname LIKE %s%", GetSQLValueString($flagname_rsFlags, "text"));
$rsFlags = mysql_query($query_rsFlags, $connFlags) or die(mysql_error());
$row_rsFlags = mysql_fetch_assoc($rsFlags);
$totalRows_rsFlags = mysql_num_rows($rsFlags);
}

 

Many thanks for your continuing help guys,

 

Darren

I think you need to put ' ' round %s% but not certain. Try '%s%'

Hi Chris,

 

Thanks for joining in. Unfortnately I'm getting the same error message as before, so it seems that didn't make much of a difference (or it did make a difference but there is something else causing an error too).

 

Thanks,

 

Darren

You're getting this error because sprintf uses the % as prefixes for replacement.

 

change this

$query_rsFlags = sprintf("SELECT * FROM flags WHERE flagname LIKE %s%", GetSQLValueString($flagname_rsFlags, "text"));

 

to

 

$query_rsFlags = sprintf("SELECT * FROM flags WHERE flagname LIKE %s", GetSQLValueString('%'.$flagname_rsFlags.'%', "text"));

 

or you can double them up like this

 

 

$query_rsFlags = sprintf("SELECT * FROM flags WHERE flagname LIKE %%%s%%", GetSQLValueString($flagname_rsFlags, "text"));