Using Wildcards on Integers MySql

[Guest]

Hi,

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

[Newsgroup_User]

Tom Denmark wrote:
> Since 8.02 I cannot use LIKE '%' on integers.
>
> Does anyone how to get this working?

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/

[Guest]

Thank you very much. That solved the issue. - Even if I'm not sure that integers wouldn't query faster than text...
Tom

[Newsgroup_User]

Tom Denmark wrote:
> 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.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[Guest]

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.
I do not have sufficient insight to evaluate whether this was good or bad :-)
Tom

[Newsgroup_User]

Tom Denmark wrote:
> 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.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[Guest]

As a matter of interest I got this answer from Interakt (now Adobe Interakt, I suppose):

To do this, you can write at the beginning of the file (before the recordset) the following lines:

$c=" 1 = 1";
if(!empty($_POST['fieldName'])) {
$c=" fieldName = " . 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 $c.

Regards,
Razvan RACASANU

P.S.
Smart move on the part of Adobe to acquire Interakt.

[Newsgroup_User]

POWNED!

David Powers wrote:
> Tom Denmark wrote:
>> 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.
>