Skip to main content
bronzino1
Participant
December 5, 2011
Answered

Wildcard Use in MySQL Query

  • December 5, 2011
  • 2 replies
  • 773 views

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

This topic has been closed for replies.
Correct answer bregent

A solution here and more in the comments:

http://joshhighland.com/blog/2008/07/06/php-sprintf-sql-like/

2 replies

bregentCorrect answer
Participating Frequently
December 5, 2011

A solution here and more in the comments:

http://joshhighland.com/blog/2008/07/06/php-sprintf-sql-like/

bronzino1
bronzino1Author
Participant
December 6, 2011

Wonderful - a little fussing and I am getting exactly what I wanted. Short form is here:

query_items=sprintf("SELECT * FROM Items WHERE Items.Color Like '%s' ORDER BY Flower", "%" . $color_items . "%");

I did not realize the problem was "sprintf" or I would have Googled for that myself.

Thank you very much, bregent!

bronzino1
bronzino1Author
Participant
December 5, 2011

I apologize that the post ends so abruptly - an error occurred and I couldn't edit it any further.

I tried changing the variable type to "defined" and "long," and it didn't change my result.

Can anyone advise as to how I should be constructing my variable "color_items" so that it will work with the GetSQLValueString formulation? This query is just a snippet of a larger one with numerous variables all formatted using GetSQLValueString. I'd rather not have to change the whole query. Plus I don't know that much about escape strings, and I assume GetSQLValueString is Dreamweaver's way of helping to prevent a SQL injection attack.

Thank you!

Elizabeth