Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Help with database query

by htmanning (Pilgrim)
on Mar 15, 2013 at 02:06 UTC ( #1023596=perlquestion: print w/replies, xml ) Need Help??
htmanning has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I'm currently building pages after extracting an ID number from the database. So for example:
$SQL="SELECT * FROM $tbl WHERE ID = '$ID' AND expireddate!=''";
What I need to do however, is build an HTML page for every ID number that is not in the database. We are up to about 10,000 IDs now (we started at zero) but a lot of those IDs have been deleted from the database. I'm not sure how to build a page for every number, starting at 1, that does not already have an ID in the database. Thanks.

Replies are listed 'Best First'.
Re: Help with database query
by NetWallah (Canon) on Mar 15, 2013 at 02:37 UTC
    Untested Pseudocode.. (Assuming you use DBI )
    # Assumes DBI RaiseError is set ... my $sth = $dbh->prepare ("SELECT ID from $tbl where ID=?"); for my $id (1..10_000){ $sth->execute ($id); next if $sth->fetchrow_arrayref; # This ID ($id) was absent # Generate web page here... }

                 "I'm fairly sure if they took porn off the Internet, there'd only be one website left, and it'd be called 'Bring Back the Porn!'"
            -- Dr. Cox, Scrubs

Re: Help with database query
by rnewsham (Chaplain) on Mar 15, 2013 at 06:49 UTC

    Rather than executing thousands of queries to test each ID individually, get all ids then just test for each potential ID in the results.

    my $results = $dbh->selectall_arrayref("SELECT ID from table", { Slice + => {} }); my %ids = map { $_->{ID} => '1' } @$results; for ( 1 .. 10000 ) { next if $ids{$_}; #do site stuff here or push to an array to use later print "$_\n"; }
Re: Help with database query
by sundialsvc4 (Abbot) on Mar 15, 2013 at 09:08 UTC

    Here’s an even more clever solution, I think:

    Now, just read the rows.   If the first ID that comes back is, say, 50, then you know that IDs 1-49 are not in the table.   Read some more, and say you get 51, 52, 53, 60.   Okay, the next range of missing IDs is therefore 54-69.   Any time there is a gap in the returned sequence (when using ORDER BY), the keys in that space do not exist.

    Furthermore, if the rows are indexed by this field, using the customary B-tree style index, then SQL does not have to physically sort the rows; it simply reads them in index order.   (Use the EXPLAIN verb, separately (not in your program), to confirm that this is what it will do.)

    It’s an old (punched...) card trick.

    If you find that the table is not so conveniently indexed and you are willing to scan it multiple times, you could read ranges of IDs, note them in a hash, and then loop through that range, one value at a time, finding the keys that are not in your hash.   Then empty the hash and repeat.   Grabbing 10,000 records or more in each chunk should not be a concern.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1023596]
Approved by vinoth.ree
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (6)
As of 2018-04-20 09:01 GMT
Find Nodes?
    Voting Booth?