Menu
Community /Pin to ProfileBookmark
@NogDogOct 01.2009 — #I don't think you can use a place-holder for a column name, as it would be replaced with a quoted string literal, not a MySQL identifier. If you need the column name to be dynamic, you'll need to set it in the query string itself, not via bind_param(). Likewise, I'm not entirely sure how bind_param() treats "%" characters, e.g. does it escape them? I suspect you may have to do something like:
And if the value for $field comes from an external source, you'll need to escape it via[url=http://www.php.net/manual/en/mysqli.real-escape-string.php]mysql::real_escape_string[/url] (), since you won't be using the binding functionality.
Mysqli binding problem
I need an extra set of eyes on this one. Any help will be greatly appreciated. This is a very simple search query, but for whatever reason I cannot find the bug. Well, I know where the bug is. I just can’t get past it. Anyway…..
I am taking a search value from a POST variable, setting that variable and then setting a column variable as follows…
$term = “‘%”.$_POST[‘searchTerm’].”%'”;
$field = “columnName”;
When I echo these after they come up perfectly. So if I type “a” in the form I would be echoing ‘%a%’ and columnName.
I then prepare the query and bind the parameters as follows…
$suquery=$dbCon->prepare(“select * from Table where ? LIKE ?”);
$suquery->bind_param(‘ss’, $field, $term);
$suquery->execute();
The result always returns 0 rows. What I am finding as I play with this is that neither bound parameter is working correctly even though it echoes as it should. For instance, when I change the query so that the column is hard coded and only bind the search term….
$suquery=$dbCon->prepare(“select * from Table where columnName LIKE ?”);
$suquery->bind_param(‘s’, $term);
$suquery->execute();
I still get zero returned rows. This tells me that even though $field echoes as ‘%a%’ something is still off. I really am at a loss on that one. Likewise, when I hard wire the search term and bind the column….
$suquery=$dbCon->prepare(“select * from Table where ? LIKE ‘%a%'”);
$suquery->bind_param(‘s’, $field);
$suquery->execute();
I get far too many rows returned. It is actually pulling rows from the table where the value in any column contains the letter “a”. So, neither column or term is binding correctly. Mayday!
Sign in
to post a comment1 Comments(s) ↴
0
@NogDogOct 01.2009 — #I don't think you can use a place-holder for a column name, as it would be replaced with a quoted string literal, not a MySQL identifier. If you need the column name to be dynamic, you'll need to set it in the query string itself, not via bind_param(). Likewise, I'm not entirely sure how bind_param() treats "%" characters, e.g. does it escape them? I suspect you may have to do something like:[code=php]
$sql = "SELECT * FROM Table WHERE $field LIKE CONCAT('%', ?, '%')"
$suquery = $dbCon->prepare($sql);
$suquery->bind_param('s', $term);
[/code]And if the value for $field comes from an external source, you'll need to escape it via