Can you do what you require by grouping by your type. setting a count
field and then a where count > 3? However if you then need to know the
details from the three consecutive fields then I don;t think it can be
done in SQL so you'll need to revert to php.
graeme
Jochem Maas wrote:
Shannon Doyle wrote:
Hi Jochem,
My apologies, when I typed consecutive, I meant by date. Basically
the query
is for a report. The client wants to be able to see when there have
been 3
or more entries of non_services_type between services_type entries,
and then
display these entries.
I am using MySQL and would Order the entries by date.
I hope that clears things up a little.
yeah! I can't think how to do that offhand in SQL but in php it would
go a little like this...(I'm assuming an array of results is being
passed - where each item in the array is an object.):
function filter3ConsecutiveNST($rows)
{
$output = $tmp = array();
$cNSTcount = 0;
foreach ($rows as $row) {
if ($row->type == 'non_service_type') {
$cNSTcount++;
if ($cNSTcount == 3) {
$output =+ $tmp;
$output[] = $row;
$tmp = array();
} else if ($cNSTcount > 3) {
$output[] = $row; } else {
$tmp[] = $row;
}
} else {
$tmp = array();
$cNSTCount = 0;
}
}
return $output;
}
hope that give you an idea.
- Shannon
-----Original Message-----
From: Jochem Maas [mailto:jochem@xxxxxxxxxxxxx] Sent: Monday, January
17, 2005 1:37 AM
To: Shannon Doyle
Cc: php-db@xxxxxxxxxxxxx
Subject: Re: Assistance on Query
Shannon Doyle wrote:
Hi People,
I would like some assistance on the following scenario.
I have to pull out all records for a particular userid (easy enough)
and
then only show those entries where the follwing occurs.
These records from the table will contain either an entry in the
services_type field or the non_services_type field. What I need to
do is
show only those where the number of consecutive records that contain an
entry in the non_services_type field is greater than or equal to 3
so example:-
record 1 contains an entry in non_services_type
record 2 contains an entry in services_type
record 3 contains an entry in non_services_type
record 4 contains an entry in non_services_type
record 5 contains an entry in non_services_type
record 6 contains an entry in services_type
so I would need to display records 3,4,5 only
Can anyone assist me with this?
Could you explain what the logic behind doing this is. i.e. Why?
(it might help to understand the problem, and possibly give a
solution that does not rely on 'consecutiveness')
I'm guessing you are using MySQL which is a relational DB, that means
the order of records stored is essentially abstracted from the DB
user, for instance using an ORDER BY clause will change the order of
the result and therefore the 'consectiveness' of the values.
It looks as if you will need to post-process the result set in PHP to
get what you want, alternatively your DB may support stored
procedures which could be an alternative (but harder to do than to do
it in PHP) i.e. the stored procedure works out the correct rows and
then outputs the result when you something like:
SELECT * FROM getConsecServiceRows( 3 )
rgds,
Jochem
Cheers,
Shannon
--
Experience is a good teacher, but she sends in terrific bills.
Minna Antrim
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
