Operating System - HP-UX
1748128 Members
3546 Online
108758 Solutions
New Discussion юеВ

Re: Perl or PHP or KSH and SQL

 
Nabil_2
Advisor

Perl or PHP or KSH and SQL

Hello,
It seems like, I keep coming back here because everytime I have a question, I always get a good answer here..

Here is my new question

I am trying to query mysql database based on a rule and would really appreciate any help

I need to be able to query any records that match the result where V3 is > 1 and has occured 4 times in a row based on the date field.
for example in this record:
DATE V1 V2
2002/07/01 Test1 0
2002/07/02 Test1 1
2002/07/03 Test1 1
2002/07/01 Test2 3
2002/07/02 Test2 9
2002/07/03 Test2 5
2002/07/04 Test2 7
2002/07/04 Test1 0
2002/07/01 Test4 3
2002/07/02 Test4 5
2002/07/03 Test4 7
2002/07/04 Test4 9
2002/07/05 Test4 35

So in this case above, my report should show this:
DATE V1 V2 V3
2002/07/04 Test2 7 4
2002/07/05 Test4 35 5
where
Date is last date of ocurrnace and
V3 value of the number of times it happended where V2 > 1 > 4 days days in a row.


Thanks for any help ..

6 REPLIES 6
Nabil_2
Advisor

Re: Perl or PHP or KSH and SQL

BTW:
MySQL Record can be as big as 300,000 lines...

Thanks
Nabil_2
Advisor

Re: Perl or PHP or KSH and SQL

"I need to be able to query any records that match the result where V3 is > 1 and has occured 4 times in a row based on the date field.
for example in this record:"


I ment where V2 or last field...
Victor Geere
Frequent Advisor

Re: Perl or PHP or KSH and SQL

In the example above the records

2002/07/04 Test2 7 4
2002/07/05 Test4 35 5

have not occured four times in a row based on the date field.

But

the answer that you are looking for is:

select `date`, count(*)
from tablename where
V2 > 1
group by date
having count(*) >4

MySQL can't do subqueries yet so you can't display all four fields as the result, unless you create the above query as view and join it to the original table on DATE.

Good luck.
I thought that I was because I had thought.
Nabil_2
Advisor

Re: Perl or PHP or KSH and SQL

Thanks for replying but all that gave me is how many times V1 occured if V2 > 1 ???

Does not help ..

Basically in a nut shell;
Need how many times an event occured for V1 when V2 >1 and happened 4 days in a row .. based on date ...must be dates in a row ...

Thanks for any more help ...

harry d brown jr
Honored Contributor

Re: Perl or PHP or KSH and SQL


Why not return the rows to perl and use perl to perform your logic? look at the dbi modules to interface to mysql.

http://www.mysql.com/downloads/api-dbi.html

look here for a sample of code, looping through the rows:

http://hotwired.lycos.com/webmonkey/98/24/index3a_page3.html?tw=backend

live free or die
harry
Live Free or Die
Nabil_2
Advisor

Re: Perl or PHP or KSH and SQL

harry,
I am cluless when comes to Perl,..But I do know that Perl did prove many things for me before many times ...

I have tried this before with PHP and performance was very poor ...So I do like to give it a shot using Perl but I have not experience in Perl.


Do you think you can help me with this script ...

I really do appreciate any help you could provide ..

Thanks in advance...