Using Wildcards on Integers MySql
Tom Denmark
SQL PHP/MySql development.
Since 8.02 I cannot use LIKE '%' on integers.
Specific problem:
On a search form I have a dynamic menu/list. The menu is populated with
categories (values are integers, labels the names of the categories).
When submitted everything works fine - showing the records belonging to the
selected category of another recordset.
If I add a static option with the value of % (or _) to the dynamic menu for
"All Categories" nothing shows up.
Prior to 8.02 this worked.
Does anyone how to get this working?
Thanks.
Tom
David Powers
> Since 8.02 I cannot use LIKE '%' on integers.
>
Yes, select "Text" as the data type instead of "Numeric".
LIKE is a pattern comparison operator that requires a string, and all
strings must be wrapped in quotes. If you select "Text", Dreamweaver
escapes any control characters and wraps the string in quotes before
presenting it to MySQL. If you select "Numeric", Dreamweaver looks for
an integer and presents it to MySQL without quotes. If you use a
wildcard, such as % or _, Dreamweaver suspects a SQL injection attack
and replaces the number with 0.
Although it may seem counter-intuitive, Dreamweaver 8.0.2 is treating
LIKE correctly.
--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/
Tom Denmark
Tom
David Powers
> Thank you very much. That solved the issue. - Even if I'm not sure that integers wouldn't query faster than text...
That's not the point. LIKE is for pattern matching - it *requires* a
string as an argument. It's part of the SQL definition.
Tom Denmark
string as an argument. It's part of the SQL definition.
the subject LIKE is for pattern matching strings. None the less the
non-standard LIKE '%' has worked on integers in DW prior to 8.02.
I do not have sufficient insight to evaluate whether this was good or bad :-)
Tom
David Powers
> Yes, according to the documentation on MySql and a couple of books I have on
> the subject LIKE is for pattern matching strings. None the less the
> non-standard LIKE '%' has worked on integers in DW prior to 8.02.
That's not non-standard. By putting quotes around %, you're turning it
into a string. MySQL happily uses LIKE to match integers, but whatever
follows LIKE *must* be a string.
What's different in Dreamweaver 8.0.2 is that specifying "Numeric"
leaves out the quotes. It also insists that you use a number. % is not a
number, but a *string* wildcard.
New Guy
Tom Denmark
suppose):
To do this, you can write at the beginning of the file (before the
recordset) the following lines:
$extraC;
if(!empty($_POST['fieldName'])) {
$extraC . intval($_POST['fieldName']);
}
Then, in the recordset you can write:
SELECT * FROM tableName WHERE colname
where colname is a parameter that you define with a runtime value of
$extraCondition.
Regards,
Razvan RACASANU
P.S.
Smart move on the part of Adobe to acquire Interakt.