Re: Assistance on Query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux