Find pattern in some mysql row with php

Question

I have a table with 2 column in mysql: id(auto_increment) and value(int 0/1). I want to find how many presence match the pattern "101" in the table with PHP. Example:

id | value
----------
1  |   1
2  |   1   --
3  |   0    |  => (1)
4  |   1   --
5  |   0     | => (2)
6  |   1   -- 
7  |   0
8  |   0
9  |   1
10 |   1
.. |   ..
.. |   ..
5000 |  1  --
5001 |  0    |  => (n)
5002 |  1  --
5003 |  1
...  |  ...

Using Array is faster than search the pattern through mysql (import the value of mysql to an array) ?

Any body know how to find how many presence of "101" in the whole rows using PHP ?

Thanks -jack-

Barmar · Accepted Answer · 2013-09-08 02:01:03Z

3

Here's how you can do it in SQL.

select count(*) match_count
from TheTable t1
join TheTable t2 on t1.id+1 = t2.id
join TheTable t3 on t1.id+2 = t3.id
where t1.value = 1 and t2.value = 0 and t3.value = 1

edited Sep 8, 2013 at 2:01

answered Sep 8, 2013 at 1:36

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

10 Comments

Jochem Kuijpers Over a year ago

Just a question, as I'm starting to learn more advanced queries: Could you use t1.id after select in order to return the id(s) of the row(s) that start this sequence?

Barmar Over a year ago

Yes, you could. I originally wrote it that way, then noticed that the question said you wanted to know how many matches, not the specific positions.

Barmar Over a year ago

@user2758001 My answer only references one table. It self-joins twice, assigning aliases t1, t2, and t3 to the instances of the table.

Barmar Over a year ago

Replace TheTable in my query with the actual name of your table.

user2758001 Over a year ago

Hi Barmar, thanks for the solution. Is there any restriction, how much is the maximum table aliasing we can have ? I mean, is it possible to make about 15 aliasing, so i can do it for 15 digits like "100100010010111" and we make it as ..... join TheTable t15 on t1.id+14 = t15.id where t1.value = 1 and ...... and t15.value = 1 ?

|

Collectives™ on Stack Overflow

Find pattern in some mysql row with php

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related