Regular expression for mysql query [duplicate]

Question

I have three records in mysql:

1. a:7:{s:10:"state_name";s:11:"West Bengal";s:7:"city_id";a:40:{i:0;s:4:"1166";i:1;s:4:"1454";i:2;s:4:"1455";i:3;s:4:"1456";i:4;s:4:"1458";i:5;s:4:"1459";i:6;s:4:"1460";i:7;s:4:"1461";i:8;s:4:"1463";i:9;s:4:"1464";i:10;s:4:"1465";i:11;s:4:"1466";i:12;s:4:"1468";i:13;s:4:"1469";i:14;s:4:"1470";i:15;s:4:"1471";i:16;s:4:"1473";i:17;s:4:"1474";i:18;s:4:"1475";i:19;s:4:"1476";i:20;s:4:"1478";i:21;s:4:"1479";i:22;s:4:"1480";i:23;s:4:"1481";i:24;s:4:"1483";i:25;s:4:"1484";i:26;s:4:"1485";i:27;s:4:"1486";i:28;s:4:"1488";i:29;s:4:"1489";i:30;s:4:"1490";i:31;s:4:"1491";i:32;s:4:"1493";i:33;s:4:"1494";i:34;s:4:"1495";i:35;s:4:"1496";i:36;s:4:"1498";i:37;s:4:"1499";i:38;s:4:"1500";i:39;s:4:"1501";}s:6:"gender";s:4:"male";s:3:"age";s:0:"";s:12:"category_ids";s:2:"68";s:16:"product_type_ids";s:2:"30";s:18:"min_purchase_price";s:1:"0";}

2. a:7:{s:10:"state_name";s:11:"West Bengal";s:7:"city_id";a:8:{i:0;s:4:"1465";i:1;s:4:"1466";i:2;s:4:"1467";i:3;s:4:"1471";i:4;s:4:"1475";i:5;s:4:"1476";i:6;s:4:"1478";i:7;s:4:"1479";}s:6:"gender";s:0:"";s:3:"age";s:4:"0-30";s:12:"category_ids";s:2:"58";s:16:"product_type_ids";s:0:"";s:18:"min_purchase_price";s:2:"50";}

3. a:7:{s:10:"state_name";s:11:"West Bengal";s:7:"city_id";a:1:{i:0;s:4:"1475";}s:6:"gender";s:6:"female";s:3:"age";s:4:"0-30";s:12:"category_ids";s:2:"58";s:16:"product_type_ids";s:0:"";s:18:"min_purchase_price";s:3:"100";} with the column name conditions_serialized.

Now I am trying to write a sql query to fetch all the records having city_id 1475. My code is like :

SELECT `main_table`.*, `rule_coupons`.`code` FROM `salesrule` AS `main_table` LEFT JOIN `salesrule_coupon` AS `rule_coupons` ON main_table.rule_id = rule_coupons.rule_id AND rule_coupons.is_primary = 1 WHERE (conditions_serialized regexp 'city_id";a:[0-9]*:{.*(i:[0-9]*;s:[0-9]*:"1475";)}')

But by this only 2 records are displaying 2nd and 3rd, not the first one. Could you please check my query and rectify that why it is not displaying all?

Thanks in advance.

Don't search the serialized data. Extract the data you'll be using in your WHERE clause later and store it in a separate column (this will allow indexing and normalisation) — ʰᵈˑ
– ʰᵈˑ, Commented Apr 27, 2015 at 13:51
Your regex is trying to match the closing brace straight after matching 1475. This should only match when city_id 1475 is the last city in the array - this is the case in example 3. — Phylogenesis
– Phylogenesis, Commented Apr 27, 2015 at 14:00
using json serialization would be much better than php serialization, however as @hd mentioned store the data in separate columns or consider using a vertical table. — Jonathan
– Jonathan, Commented Apr 27, 2015 at 14:01
Try changing your regexp to 'city_id";a:[0-9]*:{.*(i:[0-9]*;s:[0-9]*:"1475";)' — web-nomad
– web-nomad, Commented Apr 27, 2015 at 14:42

javier_domenech · Accepted Answer · 2015-04-27 14:01:47Z

0

You are looking explicitly for 1475

city_id";a:[0-9]*:{.*(i:[0-9]*;s:[0-9]*:"**1475**";)}

change it to this instead to get records from the 3 records. Note: from 3 to 5 digits

city_id";a:[0-9]*:{.*(i:[0-9]*;s:[0-9]*:"\d{3,5}";)}

answered Apr 27, 2015 at 14:01

javier_domenech

6,3036 gold badges41 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regular expression for mysql query [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related