topic Re: Perl or PHP or KSH and SQL in Operating System - HP-UX

Perl or PHP or KSH and SQL

Nabil_2 — Sat, 20 Jul 2002 08:50:06 GMT

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 ..

Re: Perl or PHP or KSH and SQL

Nabil_2 — Sat, 20 Jul 2002 08:52:46 GMT

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

Thanks

Re: Perl or PHP or KSH and SQL

Nabil_2 — Sat, 20 Jul 2002 09:10:15 GMT

"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...

Re: Perl or PHP or KSH and SQL

Victor Geere — Mon, 22 Jul 2002 06:18:45 GMT

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.

Re: Perl or PHP or KSH and SQL

Nabil_2 — Mon, 22 Jul 2002 22:30:33 GMT

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 ...

Re: Perl or PHP or KSH and SQL

harry d brown jr — Mon, 22 Jul 2002 23:51:33 GMT

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

Re: Perl or PHP or KSH and SQL

Nabil_2 — Fri, 26 Jul 2002 22:19:08 GMT

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...