Help adjusting a LIKE statement

[SauloA]

I'm not an expert php programmer and I have been using Dreamweaver to produce PHP webpages.  I have run into a problem producing a sql statement.

 

$colname_rsCustomers = "A";
if (isset($_GET['letterID'])) {
  $colname_rsCustomers = $_GET['letterID'];
}
mysql_select_db($database_connCid, $connCid);
$query_rsCustomers = sprintf("SELECT * FROM customer_tbl WHERE customer_last_name LIKE %s ORDER BY customer_last_name ASC", GetSQLValueString($colname_rsCustomers, "text"));
$query_limit_rsCustomers = sprintf("%s LIMIT %d, %d", $query_rsCustomers, $startRow_rsCustomers, $maxRows_rsCustomers);
$rsCustomers = mysql_query($query_limit_rsCustomers, $connCid) or die(mysql_error());
$row_rsCustomers = mysql_fetch_assoc($rsCustomers);

 

I have been trying to produce this statement:

 

SELECT * FROM customer_tbl WHERE customer_last_name LIKE 'A%' ORDER BY customer_last_name ASC

 

The current code works with no errors.  I don't know how to add the "%" without getting an error.  So, the current code is only pulling last names that are "A" and I want last names that start with "A".

 

I'd appreciate some help.  Thanks in advance.

[jim_keller]

try using two percent signs in your sprintf string, which will print a single % in the query:

 

$query_rsCustomers = sprintf("SELECT * FROM customer_tbl WHERE customer_last_name LIKE '%%%s' ORDER BY customer_last_name ASC", GetSQLValueString($colname_rsCustomers, "text"));

[SauloA]

Thanks for the response jim.  I get the following error when doing what you've stated:

 

"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 'A'' ORDER BY customer_last_name ASC LIMIT 0, 10' at line 1"

 

This is how my code looks:

 

$colname_rsCustomers = "A";
if (isset($_GET['letterID'])) {
  $colname_rsCustomers = $_GET['letterID'];
}
mysql_select_db($database_connCid, $connCid);
$query_rsCustomers = sprintf("SELECT * FROM customer_tbl WHERE customer_last_name LIKE '%%%s' ORDER BY customer_last_name ASC", GetSQLValueString($colname_rsCustomers, "text"));
$query_limit_rsCustomers = sprintf("%s LIMIT %d, %d", $query_rsCustomers, $startRow_rsCustomers, $maxRows_rsCustomers);
$rsCustomers = mysql_query($query_limit_rsCustomers, $connCid) or die(mysql_error());
$row_rsCustomers = mysql_fetch_assoc($rsCustomers);

 

Any thoughts?

[PFMaBiSmAd]

Unfortunately, the DW GetSQLValueString() function puts the single-quotes that are part of the SQL syntax for strings around the value it returns, rather than YOU putting the necessary SQL syntax in the query.

 

You would either need to remove the leading and trailing single-quotes that are being put around the value that GetSQLValueString() returns or you will simply need to escape the string data values yourself by using mysql_real_escape_string instead of the GetSQLValueString() function.

[PFMaBiSmAd]

Another alternate 'fix' would be to append/prepend the % wild-card character to the data value before (or while) calling the GetSQLValueString() function for the specific parameter in the query that is being put into the LIKE '...' term.

[SauloA]

Hello PFMaBiSmAd

 

Thanks for the response.  I fiddled around with what you stated and combined what jim stated and ended up with the following:

 

$colname_rsCustomers = "A";
if (isset($_GET['letterID'])) {
  $colname_rsCustomers = $_GET['letterID'];
}
mysql_select_db($database_connCid, $connCid);
$query_rsCustomers = sprintf("SELECT * FROM customer_tbl WHERE customer_last_name LIKE '%s%%' ORDER BY customer_last_name ASC", mysql_real_escape_string($colname_rsCustomers));
$query_limit_rsCustomers = sprintf("%s LIMIT %d, %d", $query_rsCustomers, $startRow_rsCustomers, $maxRows_rsCustomers);
$rsCustomers = mysql_query($query_limit_rsCustomers, $connCid) or die(mysql_error());
$row_rsCustomers = mysql_fetch_assoc($rsCustomers);

 

Seems like everything is running smoothly.

 

Thanks for the help.  I'm happy to say this problem is solved.