Can anybody tell me why this wont work :-S

[mattbrown]

I have made a function to run a simple search facility on my site but for some reason the mysql queries are not getting put together right heres the function:

[code]function search($searchStr, $searchBy) {
//Declare our globals
global $hostname, $username, $password;

//Find out what we're searching for and construct the query
switch ($searchBy) {
case 1: //Search by barcode
$searchQuery = sprintf("SELECT productCode, image, price, name, sale_price, stock_level FROM
bgy_CubeCart_inventory WHERE productCode='%s'",
mysql_real_escape_string($searchStr));
break;

case 2: //Search by title
$searchQuery = sprintf("SELECT productCode, image, price, name, sale_price, stock_level FROM
bgy_CubeCart_inventory WHERE name LIKE '%%%s%%'",
mysql_real_escape_string($searchStr));
break;

case 3: //Search by description
$searchQuery = sprintf("SELECT productCode, image, price, name, sale_price, stock_level FROM
bgy_CubeCart_inventory WHERE description LIKE '%%%s%%'",
mysql_real_escape_string($searchStr));
break;

case 4: //Search by barcode, title, description
$searchQuery = sprintf("SELECT productCode, image, price, name, sale_price, stock_level MATCH
(productCode, name, description) AGAINST ('%s') FROM
bgy_CubeCart_inventory",
mysql_real_escape_string($searchStr));
break;
}

//Connect to the database and run the query
$link = mysql_connect("$hostname" , "$username" , "$password")or die(mysql_error());
mysql_select_db('barkersgifts');
$searchResult = mysql_query($searchQuery);
mysql_close($link);


if (mysql_num_rows($searchResult)) {

return $searchResult;
} else {
return "No Results Found";
}

}
[/code]

if i change
[code]$searchQuery = sprintf("SELECT productCode, image, price, name, sale_price, stock_level FROM bgy_CubeCart_inventory WHERE productCode='%s'",
mysql_real_escape_string($searchStr));[/code]

to

[code]$searchQuery = sprintf("SELECT productCode, image, price, name, sale_price, stock_level FROM bgy_CubeCart_inventory WHERE productCode='$searchStr'",);[/code]

Then it works ok but using mysql_real_escape_string() the query that get constructed is as follows

SELECT productCode, image, price, name, sale_price, stock_level FROM bgy_CubeCart_inventory WHERE productCode=''

I have used mysql_real_escape_string() in other functions and it seems to work just fine :-S

any help would be very much appreciated

[mattbrown]

After a little bit more messing around and comparing this to my other functions the answer has become obvious

$link = mysql_connect("$hostname" , "$username" , "$password")or die(mysql_error());
mysql_select_db('barkersgifts');


needs to be before the line where mysql_real_escape_string() is used, for obvious reasons.