PHP and Regular Expressions

[Phil Powell]

I need to do a very simple query in MySQL versions 3.23.41 and 4.0.10
whereby it would be something like this:

[PHP]
$sql = "SELECT * FROM image WHERE image_path LIKE
'%$imageLocatio nPath/[a-zA-Z0-9\\-_\\.%]+\$%'"
[/PHP]

However, I can't figure out how to do this. I went to various online
tutorials including www.mysql.com and I'm sorry not a single one of
them made an ounce of sense. Could someone please help me with this
very simple task or point me in the right direction as to how to write
this?

Thanx
Phil

[Chris Hope]

Re: PHP and Regular Expressions

Phil Powell wrote:
[color=blue]
> I need to do a very simple query in MySQL versions 3.23.41 and 4.0.10
> whereby it would be something like this:
>
> [PHP]
> $sql = "SELECT * FROM image WHERE image_path LIKE
> '%$imageLocatio nPath/[a-zA-Z0-9\\-_\\.%]+\$%'"
> [/PHP]
>[/color]

As far as I am aware you can't use regular expressions in MySQL using "like"
in a select query. You can use % to match any number of characters and _ to
match just one character but that's just about it.

Check the string comparison functions in the MySQL manual:

http://www.mysql.com/doc/en/String_comparison_functions.html

Chris

--
Chris Hope
The Electric Toolbox Ltd

http://www.electrictoolbox.com/

[Jan Pieter Kunst]

Re: PHP and Regular Expressions

In article <4071b83d_3@new s.athenanews.co m>,
Chris Hope <blackhole@elec trictoolbox.com > wrote:
[color=blue]
> As far as I am aware you can't use regular expressions in MySQL using "like"
> in a select query. You can use % to match any number of characters and _ to
> match just one character but that's just about it.[/color]

You can use regular expressions in a select statement like this:

SELECT * FROM table WHERE column REGEXP 'some_regular_e xpression'

<http://www.mysql.com/doc/en/Pattern_matchin g.html>
<http://www.mysql.com/doc/en/Regexp.html>

JP

--
Sorry, <devnull@cauce. org> is een "spam trap".
E-mail adres is <jpk"at"akamail .com>, waarbij "at" = @.

[Pedro Graca]

Re: PHP and Regular Expressions

Chris Hope wrote:[color=blue]
> Check the string comparison functions in the MySQL manual:
> http://www.mysql.com/doc/en/String_c...functions.html[/color]

check particularly this part of "String comparison functions"

http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1292

And the Appendix F of the manual

http://www.mysql.com/doc/en/Regexp.html

--
USENET would be a better place if everybody read: : mail address :
http://www.catb.org/~esr/faqs/smart-questions.html : is valid for :
http://www.netmeister.org/news/learn2quote2.html : "text/plain" :
http://www.expita.com/nomime.html : to 10K bytes :

[Chris Hope]

Re: PHP and Regular Expressions

Jan Pieter Kunst wrote:
[color=blue]
> You can use regular expressions in a select statement like this:
>
> SELECT * FROM table WHERE column REGEXP 'some_regular_e xpression'
>
> <http://www.mysql.com/doc/en/Pattern_matchin g.html>
> <http://www.mysql.com/doc/en/Regexp.html>[/color]

Very cool. Now the only thing the parent poster needs to be aware of is that
the regular expression will be applied to every row in the table (or those
matching other parts of the where clause) when running the select query.
This is not a problem on smaller tables but if you're dealing with millions
of records it may present a problem.

Chris

--
Chris Hope
The Electric Toolbox Ltd

http://www.electrictoolbox.com/

[Chung Leong]

Re: PHP and Regular Expressions

"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:4071cfbc_6 @news.athenanew s.com...[color=blue]
> Jan Pieter Kunst wrote:
>[color=green]
> > You can use regular expressions in a select statement like this:
> >
> > SELECT * FROM table WHERE column REGEXP 'some_regular_e xpression'
> >
> > <http://www.mysql.com/doc/en/Pattern_matchin g.html>
> > <http://www.mysql.com/doc/en/Regexp.html>[/color]
>
> Very cool. Now the only thing the parent poster needs to be aware of is[/color]
that[color=blue]
> the regular expression will be applied to every row in the table (or those
> matching other parts of the where clause) when running the select query.
> This is not a problem on smaller tables but if you're dealing with[/color]
millions[color=blue]
> of records it may present a problem.[/color]

Perhaps adding some secondary conditions would help a bit. E.g. image_path
IS NOT NULL,
LEN(image_path) > $path_len

I wonder if MySQL would use an index on the column to prequalify rows if you
match against 'abcd%' (and the index is based on the first four letters).

[Chris Hope]

Re: PHP and Regular Expressions

Chung Leong wrote:
[color=blue]
> I wonder if MySQL would use an index on the column to prequalify rows if
> you match against 'abcd%' (and the index is based on the first four
> letters).[/color]

It will if you do 'abcd%' (but not '%abcd%') but I would imagine a regexp
has to be run against every field, as I cannot see a way of evaluating a
regexp using an index.

Chris

--
Chris Hope
The Electric Toolbox Ltd

http://www.electrictoolbox.com/

[Phil Powell]

Re: PHP and Regular Expressions

I got it to work using this:

$sql = "SELECT * FROM image WHERE image_path REGEXP
'$imageLocation Path/[a-zA-Z0-9%\\-_\\.]+\$'"

My next question, then would be if I need to create a compound
FULLTEXT index on the field 'image_path'.

Thanx for your help!
Phil

Chris Hope <blackhole@elec trictoolbox.com > wrote in message news:<407228c9_ 7@news.athenane ws.com>...[color=blue]
> Chung Leong wrote:
>[color=green]
> > I wonder if MySQL would use an index on the column to prequalify rows if
> > you match against 'abcd%' (and the index is based on the first four
> > letters).[/color]
>
> It will if you do 'abcd%' (but not '%abcd%') but I would imagine a regexp
> has to be run against every field, as I cannot see a way of evaluating a
> regexp using an index.
>
> Chris[/color]

[Chris Hope]

Re: PHP and Regular Expressions

Phil Powell wrote:
[color=blue]
> I got it to work using this:
>
> $sql = "SELECT * FROM image WHERE image_path REGEXP
> '$imageLocation Path/[a-zA-Z0-9%\\-_\\.]+\$'"
>
> My next question, then would be if I need to create a compound
> FULLTEXT index on the field 'image_path'.[/color]

I am doubtful that an index would help with the regexp pattern but you
should probably post the query itself (and not the actual PHP code) as a
question to the MySQL newsgroup (mailing.databa se.mysql) as this is a PHP
group :)

Chris

--
Chris Hope
The Electric Toolbox Ltd

http://www.electrictoolbox.com/

[Chung Leong]

Re: PHP and Regular Expressions

"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:407228c9_7 @news.athenanew s.com...[color=blue]
> It will if you do 'abcd%' (but not '%abcd%') but I would imagine a regexp
> has to be run against every field, as I cannot see a way of evaluating a
> regexp using an index.[/color]

Looking at the regexp in the OP again, I wonder if regexp is needed at all.
The pattern appears to match all legal filenames in the specified folder.

[Phil Powell]

Re: PHP and Regular Expressions

Thanx I'll consider doing just that.. here would be an example of the
query:

SELECT id, image_name, image_path FROM image WHERE image_path REGEXP
'/images/[a-zA-Z0-9%\-_\.]+$'

Phil

Chris Hope <blackhole@elec trictoolbox.com > wrote in message news:<40731061_ 3@news.athenane ws.com>...[color=blue]
> Phil Powell wrote:
>[color=green]
> > I got it to work using this:
> >
> > $sql = "SELECT * FROM image WHERE image_path REGEXP
> > '$imageLocation Path/[a-zA-Z0-9%\\-_\\.]+\$'"
> >
> > My next question, then would be if I need to create a compound
> > FULLTEXT index on the field 'image_path'.[/color]
>
> I am doubtful that an index would help with the regexp pattern but you
> should probably post the query itself (and not the actual PHP code) as a
> question to the MySQL newsgroup (mailing.databa se.mysql) as this is a PHP
> group :)
>
> Chris[/color]