change search from = to LIKE

[mackin]

Is it possible to change the code below so the search uses LIKE - so as not to be so exact with the results - I assume I need to put est_name LIKE.... but where do the % % go? if I put them around "text" the search doesnt work.

Thx in Advance

 

 

Stu

 

$colname_hotel_name_RS = "-1";
if (isset($_POST['name'])) {
  $colname_hotel_name_RS = $_POST['name'];
}


mysql_select_db($database_contractors, $contractors);
$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", GetSQLValueString($colname_hotel_name_RS, "text"));
$hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error());
$row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS);
$totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS);

[Muddy_Funster]

I think the SQL should look like this (I belive you need to escape the %'s used for the Like as you are using a sprintf to build the string):

"SELECT * FROM hotels WHERE est_name LIKE '\%%s\%'", GetSQLValueString($colname_hotel_name_RS, "text")

 

if that errors out try it without the \'s at the %'s and if that still doesn't work post us up your error messages.

[kickstart]

Hi

 

Or:-

 

$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", '%'.GetSQLValueString($colname_hotel_name_RS, "text").'%');

 

All the best

 

Keith

[mackin]

Hi Keith, when I use your code I get this error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'abbey'%' at line 1

 

That is with a search for abbey

 

muddy - same search with code verbatim gives You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\%s\' at line 1

removing the escaping gives

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%s' at line 1

 

Cheers people

[kickstart]

Hi

 

To me that looks like you have some odd mix of php and mysql.

 

The inverted commas around the % signs should have disappeared before it gets to MySQL (ie, they are just delimiting a php string).

 

All the best

 

Keith

[Muddy_Funster]

Mackin, could you do a print_r($query_hotel_name_RS) for us and post the contents of the query string after it's made?

[mackin]

with the original code I get

SELECT * FROM hotels WHERE est_name = 'abbey'

with the amended codes the query wont display because of the syntax errors

 

 

[kickstart]

Hi

 

Err, something off there as the error you are getting is an SQL error. That should prevent you outputting the SQL after it is created but before it is executed.

 

All the best

 

Keith

[Muddy_Funster]

try this:

$colname_hotel_name_RS = "-1";

if (isset($_POST['name'])) {

  $colname_hotel_name_RS = $_POST['name'];

}

 

 

mysql_select_db($database_contractors, $contractors);

$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name LIKE '\%%s\%'", GetSQLValueString($colname_hotel_name_RS, "text"));

print_r($query_hotel_name_RS);

exit();

$hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error());

$row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS);

$totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS);

[fenway]

Post the SQL query itself, and the error.

[mackin]

$colname_hotel_name_RS = "-1";
if (isset($_POST['name'])) {
  $colname_hotel_name_RS = $_POST['name'];
}


mysql_select_db($database_contractors, $contractors);
$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name LIKE '\%%s\%'", GetSQLValueString($colname_hotel_name_RS, "text"));
print_r($query_hotel_name_RS);
exit();
$hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error());
$row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS);
$totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS);

 

That gives me

 

SELECT * FROM hotels WHERE est_name LIKE '\%s\ - with same abbey search term

[kickstart]

Hi

 

Try this:-

 

$colname_hotel_name_RS = "-1";
if (isset($_POST['name'])) {
  $colname_hotel_name_RS = $_POST['name'];
}


mysql_select_db($database_contractors, $contractors);
$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", '%'.GetSQLValueString($colname_hotel_name_RS, "text").'%'); 
echo "$query_hotel_name_RS";
$hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error());
$row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS);
$totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS); 

 

See what the SQL is that it outputs

 

All the best

 

Keith

[mackin]

Hi

 

Try this:-

 

$colname_hotel_name_RS = "-1";
if (isset($_POST['name'])) {
  $colname_hotel_name_RS = $_POST['name'];
}


mysql_select_db($database_contractors, $contractors);
$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", '%'.GetSQLValueString($colname_hotel_name_RS, "text").'%'); 
echo "$query_hotel_name_RS";
$hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error());
$row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS);
$totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS); 

 

See what the SQL is that it outputs

 

All the best

 

Keith

gives me this keith

 

SELECT * FROM hotels WHERE est_name = %'abbey'%You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'abbey'%' at line 1

[kickstart]

Hi

 

Ah, right. Seem what is confusing me. GetSQLValueString returns a string with inverted commas already in place.

 

Try this:-

 

$colname_hotel_name_RS = "-1";
if (isset($_POST['name'])) {
  $colname_hotel_name_RS = $_POST['name'];
}


mysql_select_db($database_contractors, $contractors);
$query_hotel_name_RS = sprintf("SELECT * FROM hotels WHERE est_name = %s", GetSQLValueString('%'.$colname_hotel_name_RS.'%', "text")); 
echo "$query_hotel_name_RS";
$hotel_name_RS = mysql_query($query_hotel_name_RS, $contractors) or die(mysql_error());
$row_hotel_name_RS = mysql_fetch_assoc($hotel_name_RS);
$totalRows_hotel_name_RS = mysql_num_rows($hotel_name_RS);  

 

All the best

 

Keith

[mackin]

Brilliant Keith - just had to change = to LIKE but worked perfectly - thanks for your patience.

 

Regards

 

Stu

 

(thx to all)