Mysql/php query using REGEXP issue

[myristate]

Hi there, im looking for a little bit of help, could someone who knows how to use REXEXP properly tell me how i would select only the uppercase titles from title column and display them

 

+------------+-----------+
| id             | title        |
+------------+-----------+
| 1             | one        |
| 2             | TWO       |
| 3             | three      |
| 4             | FOUR      |
+------------+-----------+

 

<?php

$username="user";
$password="pw";
$database="db";


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT title FROM template WHERE name REGEXP '^(A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z)'";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    echo  "Custom Template Name : {$row['title']} <br><br>";
} 

mysql_close();
?>

 

This was an attempt i made but it did not work i got the following error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\ProgramData\xampp\htdocs\test.php on line 14

 

I did attempt to do a search on it but if im honest i didnt fully understand what they were saying.

 

 

 

Im using php 5.3.5 and mysql 5.5.8

 

Thanks for your help

[xyph]

Try using this regex

 

^[A-Z]++$

[myristate]

seems no matter how i rearranged the formatting after regexp it would always select upper and lower case, after stumbling across a page seems you have to add COLLATE into the mix

$query = "SELECT * FROM template WHERE (title COLLATE latin1_general_cs) REGEXP '^[A-Z]+$'"; 

[xyph]

I see where that came from.

 

charset_ci = case-insensitive

charset_cs = case-sensitive

 

Hope this doesn't bog this down. Might be worth changing the table to charset_cs to avoid the call in every regex function, unless there's a point where you need case insensitivity 

 

Further interesting reading - from the MySQL doc comments

 

 

Posted by Guido Dieterich on December 20 2005 11:46am [Delete] [Edit]

This sql statements:

SELECT 'WORD' REGEXP '[[:upper:]]{4}'; # => 1;
SELECT 'WORD' REGEXP '[[:lower:]]{4}'; # => 0
work right only when collate is _cs and NOT _ci (case insensitive)

created tables eg. the collate 'latin1_swedish_ci' have to be changed, if you want to use case sensitive REGEXPs like [[:upper:]] or [[:lower:]]!
ALTER TABLE <name> CONVERT TO CHARACTER SET latin1 COLLATE latin_general_cs 

I set in my.cnf now:
[mysqld]
default-collation= latin1_general_cs

#default was latin1_swedish_ci

 

Posted by Koy Kragh on March 4 2006 7:33pm [Delete] [Edit]

The above post by Guido Dieterich (about collation and case sensitivity) is a good point. However, there is a way to match in a case-sensitive manner without having to change the collation of your existing tables: use the "BINARY" keyword.

Here's an extended example (based on the one previously posted):

SELECT
('WORD' REGEXP '[[:upper:]]{4}') AS `upper_match`, # this will be a 1
('WORD' REGEXP '[[:lower:]]{4}') AS `lower_match`, # this will be a 1 on an "*_ci" collation
# -BINARY- matches below
(BINARY 'WORD' REGEXP '[[:upper:]]{4}') AS `bin_upper_match`, # this will be a 1
(BINARY 'WORD' REGEXP '[[:lower:]]{4}') AS `bin_lower_match` # this will be a 0 even on an "*_ci" collation

[myristate]

That is the post i actually found that i found to fix it.