Question writing a query

patsfans

I have a database with rows that have the following data:

I'm trying to figure out the best way to write a query and counts the number of 0's in between the 1's but haven't been able to come up with one and wanted to see if someone had a good suggestion. 😕

NogDog

Not sure if this makes sense for what you actually need to accomplish, but I might let the PHP code handle it, e.g.:

$string = '';
while(($row = whateverFetchFunctionYouUse($dbResult)) !== false) {
	$string .= $row['field'];
}
preg_match_all('/0+/', $string, $matches);
$results = array();
foreach($matches[0] as $match)
$results[] = strlen($match);
print_r($results);

Ashley_Sheridan

Are there no other fields that accompany these rows? Something that at least identifies the row? Typically there will be something that makes each row unique. This is what I would do:

SELECT COUNT(*) as total
FROM table
GROUP BY field_full_of_1s_and_0s

patsfans

Ashley Sheridan;11020629 wrote:
Are there no other fields that accompany these rows? Something that at least identifies the row? Typically there will be something that makes each row unique. This is what I would do:
SELECT COUNT(*) as total
FROM table
GROUP BY field_full_of_1s_and_0s

There is. Basically what I'm looking to do is count the number of occurrences in-between a circumstance that happened during a specific event. There's the event name, and an id # for each for each event, as well as several fields signified by either 0 for no, or 1 for yes. NogDog had a good idea and I'm working on expanding on that, but it's the main query that I'm still struggling with. SELECT count(*) was the one I tried, but I'm having counting the in-between rows.

So basically it's set up like this:

eventname
open
closed
id

So in other words, if I was looking to count the number of days in a row a specific event (there are multiple ones in the database) was open (1) vs closed (0), that's what I'm trying to accomplish, as well as the number of consecutive days since it was last closed 🙂

bradgrafelman

While it could probably be done in a MySQL procedure (or maybe even a single query), I would probably follow NogDog's suggestion and implement it in PHP... but that's just because I'm way out of practice at writing SQL procedures. 😉

Also, don't forget to add an ORDER BY clause in there; in the absence of one, you shouldn't expect the results to be ordered in any meaningful or even deterministic manner.