Find pattern in some mysql rows with php

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
...  |  ...

How to find how many presence of “101” in the whole rows using PHP ? Is it better to import the data from mysql to an array, then do the pattern searching ?

Thanks

Hi jackprobe, welcome to the forums.

If I understand you, you want to find every row that is preceded by a row with the value “1” and followed by a row with the value “1” and only those such rows?

I can’t think of any SQL queries that could do that.
And doing it with PHP sounds like it would be extremely resource intensive.

I guess you could do it in manageable sized batches.

His question is vague.

The best way to read it is horizontal.

What if it reads like this 101010101010.
So the description o fthe question is not clear enough.

Yes this is possible just make a PHP method that will process the logic, but first fetch the values from DB first.
A better technique of coding this is use “Divide and Conquer” approach to formulate the algorithm for this problem.
Cut the one big problem into pieces first. And then you can start from there…

It’s doable. Got a little tricky when I came to repeating matches, which is why I added the $cnt 4 reset to 1.

<?php
//make fake $row array for testing
$row = array();
foreach(range(0,200) as $id){
	$value = rand(0, 1);
	$row[$id] = $value;
}
// Uncomment for varifing
//echo "<pre>";
//print_r($row);
//echo "</pre>";

$matches = array();
$keys = array();
//flip-flop starting with 1 but will use % 2
$x = 0;
//looking for a pattern of three
$cnt = 0;

/*
For testing I'll use foreach with $id and $value.
This would be replaced by your WHILE loop/
*/	

foreach($row as $id => $value){
		if($cnt==4){
			$x = 1;
			$cnt = 1;	
		}
	$search = ($x == 0 || $x % 2 == 0 ? 1 : 0);
	if($value==$search){
		$x++;
		$cnt++;
		$keys[] = $id;
		
		if($cnt==1 && count($keys)>1){
			unset($keys);
			$keys[] = $id;		
		}
		
		if($cnt==3){
			$ids = $keys;
			$matches[] = $ids;
			unset($keys);
			$keys[] = $id;
			$x = 0;
			$cnt = 4;	
		}
	}else{
		unset($keys);
		$x = 0;
		$cnt = 0;
	}
}
echo "Total Matches:  " . count($matches) . "<br />";

foreach($matches as $record_ids){
	echo "Record IDs:  " . implode(", ",$record_ids) . "<br />";
}
?>

Thank you Mittineague, solidcodes and Drummin.
@Drummin, thanks … i will try it. I like PHP, and yes it’s more complex for this solution than mysql … :smiley: , but i will try your code … it’s very interesting.

I got the solution using mysql at other forum :


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

Thank you.