Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

checking mysql rows continuously

by bigup401 (Pilgrim)
on Dec 08, 2020 at 11:31 UTC ( #11124823=perlquestion: print w/replies, xml ) Need Help??

bigup401 has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: checking mysql rows continuously
by LanX (Cardinal) on Dec 08, 2020 at 11:49 UTC
    these are mostly SQL questions, not Perl.

    Well ... to get you started with DBI

    > then how can i run the query continuously

    ->fetchrow will give you row after row if you don't LIMIT you can run it in a loop

    > skip to next row if the previous one status eq to no

    • either you select status too and decide in Perl to go to the next loop iteration
    • or if no action is needed you insert a where clause WHERE status == "yes"

    I recommend you use DBI#fetchrow_hashref

    Fetches the next row of data and returns it as a reference to a hash containing field name and field value pairs. ...

    If there are no more rows or if an error occurs, then fetchrow_hashref returns an undef

    something like

    my $query = $dbh->prepare("SELECT * FROM items ORDER BY created ASC"); + $query->execute(); while ( my $h_fields = $query->fetchrow_hashref() ) { next if $h_fields->{status} eq 'no'; # not needed w +ith WHERE clause ... }

    (untested)

    Please see SQL tutorials

    UPDATE

    ) uhm ... DBI has no fetchrow! But ...

    $ary_ref = $sth->fetchrow_arrayref; $ary_ref = $sth->fetch; # alias

    down-voted for showing wrong code. (Again)

    Cheers Rolf
    (addicted to the Perl Programming Language :)
    Wikisyntax for the Monastery

      thanks for your response, am going to try it now

Re: checking mysql rows continuously
by Anonymous Monk on Dec 08, 2020 at 14:03 UTC
    How about something vaguely like this ... an inner-join of a table to itself:

    select A.id from table1 A inner join table1 B on (B.id = A.id - 1) where B.status = 'no' and A.status = 'yes'

    I think that would give you the id's rows where a "yes" answer immediately follows a "no" answer. (It will not give you initial "yes" answers that are not followed by "no," so manually add "id#1" to your list.) Armed with this answer, select the rows with greater-or-equal IDs and walk them until you encounter a "no."

    Although I think that this query will still require "a full table scan," at least it will be the database engine that's doing the heavy lifting.

    A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11124823]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (3)
As of 2021-03-04 04:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (98 votes). Check out past polls.

    Notices?