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
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
