By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,053 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,053 IT Pros & Developers. It's quick & easy.

PHP and Regular Expressions

P: n/a
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
'%$imageLocationPath/[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
Jul 17 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Phil Powell wrote:
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
'%$imageLocationPath/[a-zA-Z0-9\\-_\\.%]+\$%'"
[/PHP]


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_c...functions.html

Chris

--
Chris Hope
The Electric Toolbox Ltd
http://www.electrictoolbox.com/
Jul 17 '05 #2

P: n/a
In article <40********@news.athenanews.com>,
Chris Hope <bl*******@electrictoolbox.com> wrote:
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.


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

SELECT * FROM table WHERE column REGEXP 'some_regular_expression'

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

JP

--
Sorry, <de*****@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.
Jul 17 '05 #3

P: n/a
Chris Hope wrote:
Check the string comparison functions in the MySQL manual:
http://www.mysql.com/doc/en/String_c...functions.html


check particularly this part of "String comparison functions"
http://www.mysql.com/doc/en/String_c...s.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 :
Jul 17 '05 #4

P: n/a
Jan Pieter Kunst wrote:
You can use regular expressions in a select statement like this:

SELECT * FROM table WHERE column REGEXP 'some_regular_expression'

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


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/
Jul 17 '05 #5

P: n/a
"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:40********@news.athenanews.com...
Jan Pieter Kunst wrote:
You can use regular expressions in a select statement like this:

SELECT * FROM table WHERE column REGEXP 'some_regular_expression'

<http://www.mysql.com/doc/en/Pattern_matching.html>
<http://www.mysql.com/doc/en/Regexp.html>
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.


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).
Jul 17 '05 #6

P: n/a
Chung Leong wrote:
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).


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/
Jul 17 '05 #7

P: n/a
I got it to work using this:

$sql = "SELECT * FROM image WHERE image_path REGEXP
'$imageLocationPath/[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 <bl*******@electrictoolbox.com> wrote in message news:<40********@news.athenanews.com>...
Chung Leong wrote:
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).


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

Jul 17 '05 #8

P: n/a
Phil Powell wrote:
I got it to work using this:

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

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


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.database.mysql) as this is a PHP
group :)

Chris

--
Chris Hope
The Electric Toolbox Ltd
http://www.electrictoolbox.com/
Jul 17 '05 #9

P: n/a
"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:40********@news.athenanews.com...
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.


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.
Jul 17 '05 #10

P: n/a
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 <bl*******@electrictoolbox.com> wrote in message news:<40********@news.athenanews.com>...
Phil Powell wrote:
I got it to work using this:

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

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


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.database.mysql) as this is a PHP
group :)

Chris

Jul 17 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.