Wildcard Use in MySQL Query
I am using PHP/MySQL with Dreamweaver CS5, v.11. I want to query my table for a color entered by the user, and return results for words like "Brown" even if there are words before or after in the table cell. E.g., :
Silver Brown Yellow
Grey Blue Brown
Brown Blue Yellow
Here is a query I made in Dreamweaver that I thought would accomplish that:
if (isset($_GET['color'])) {
$color_items = $_GET['color']; }
$query_items = sprintf("SELECT * FROM Items WHERE Items.Color Like '%$color_items%'", GetSQLValueString($color_items, "text"));
$query_limit_items = sprintf("%s LIMIT %d, %d", $query_items, $startRow_items, $maxRows_items);
$items = mysql_query($query_limit_items, $CollectionLocal) or die(mysql_error());
$row_items = mysql_fetch_assoc($items);
Regardless of whether I use "$color_items", "%s", or an actual term like "brown," I am having problems.
If I use "Like '%$color_items%'" as in the example above, it returns a syntax error message indicating that it has changed what should be "%brown%" to "0row0". In other words, it's treating my text variable like an integer.
I tried putting making $color_items='%'.$_GET['Color'].'%', but that produces the same result.
If I zap the single quotes from the query, it assumes that $color_items is a column name, and because there's no column called $color_items, that produces an error.
If I don't use the GetSQLValueString formulation, and do something like this, it works:
| $query_items = "SELECT * FROM Items WHERE Color Like '%$color_items%'"; |
$items = mysql_query($query_items, $CollectionLocal) or die(mysql_error());
$row_items = mysql_fetch_assoc($items);
$totalRows_items = mysql_num_rows($items);
And it works in MySQL alone, as well. I want to use the GetSQLValueStrin