Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Re^3: DBI disconnect database errors

by EvanCarroll (Chaplain)
on Nov 16, 2009 at 15:55 UTC ( #807496=note: print w/replies, xml ) Need Help??

in reply to Re^2: DBI disconnect database errors
in thread DBI disconnect database errors

OK, being able to look at that code, I wold explicitly NOT to do that. I would not explicitly prepare something for a single select, use selectrow_hashref instead, and put in the sql an explicit `LIMIT 1`. Also, using '*' should be reserved, when you want all the rows in the table. This should hold true if one of those rows is a 50 meg block of text.

Also, in your second example you could just as well do that in one query right? Through a join. This saves work on the DB.

$dbh->selectrow_hashref( 'SELECT t1.* FROM table AS t0' . ' JOIN table1 AS t1' . ' USING ( primarykeyfield )' );
Or, if you wanted precisely the same as above, which will render the row from table1 random because the lack of a sort order on table, you can still do that in one statement
$dbh->selectrow_hashref( 'SELECT t1.* FROM table AS t0' . ' WHERE t1.primarykeyfield IN ( SELECT primarykeyfield FROm tabl +e LIMIT 1 ) );

Evan Carroll
The most respected person in the whole perl community.

Replies are listed 'Best First'.
Re^4: DBI disconnect database errors
by Anonymous Monk on Nov 16, 2009 at 16:51 UTC
    Thanks for that...

    I normally just use all the data in both, if I only need info from one table, I do it like this:
    # I need the members information for their profile update... #$_un carries the username they are logged in as: $sth = $dbh->selectrow_array(qq{select * from `members` where `USERNAM +E` = ? LIMIT 1}); $sth->execute($_un); my $_mbrInfo = $sth->fetchrow_hashref(); $sth->finish(); # then go build the html page and form for them to update their profil +e, prepopulating the data that is already in their
    That is how I get their data, however, if I need multiple tables of information, since I store data in multiple tables, I get each of them:
    $sth = $dbh->selectrow_array(qq{select * from `members` where `USERNAM +E` = ? LIMIT 1}); $sth->execute($_un); my $_mbrInfo = $sth->fetchrow_hashref(); $sth->finish(); # Got member record, now get their billing address: $sth = $dbh->prepare(qq{select * from `shippingAddresses` where `mid` += ?}); $sth->execute($_mbrInfo->{mid}); my $_addy = $sth->fetchrow_hashref(); $sth->finish();
    That is how I get all the info I need, I don't usually get all the fields if I only need a few... If I only need one or two fields I do it this way:
    # Get Members Id, Name, Phone, Email: my($_mbrId,$_mbrName,$_mbrPhone,$_mbrEmail) = $dbh->selectrow_array(qq +{select `mid`,`name`,`phone`,`email` from `members` where `USERNAME` += ?}, undef, $_un);
    Or if I need data only out of another table, but it is based on the members id, and I only have the useranme, I do it like this:
    # Get address: $sth = $dbh->selectrow_array(qq{select * from `shippingAddresses` wher +e `Id` = (select `mid` from `members` where `USERNAME` = ?)}); $sth->execute($_un); my $_addy = $sth->fetchrow_hashref(); $sth->finish();
    That is how I would get just the fields I need... Since usernames are unique, I don't need to put a limit of 1 on it since there should only be one matching record.

    That is how I handle those in my programming.

    I am always open to new ideas though. I am currently learning Ajax to make some of the things I do much better, I am loving what I am learning! That is a GREAT language.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://807496]
[Corion]: Yay! As I had to reset my phone again, I now also back up my SMS automatically. Highly conveniently, there is a program to back up my SMS to my IMAP server as mails ;)
[1nickt]: Corion this is an android phone? If so, are you encountering problems with recent upgrades? Lately my phone dies suddenly quite often, seemingly coredumps caused by interaction of two actions, eg phone playing a sound and user swiping simultaneously :-/
[Corion]: 1nickt: Yeah, it's Android, but it's CM 11, so fairly old. I'm looking for a newer (china) phone, but I think I'll wait until Oreo ROMs get out for them
[Corion]: Otherwise, the Vernee Mars looks tempting
[Corion]: ... but alas, no Oreo support in sight yet, and I prefer to flash a stock Android over the custom ROMs, and Oreo promises to make the userland more maintainable.
[LanX]: Corion: shouldn't pmdev have permission to view code of nodelets?
[1nickt]: And of course everything is "unlocked" in EU, right?
[1nickt]: I think there are far more choices than in the US
LanX You don't have access to  Function Nodelet.

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2017-11-18 15:11 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (277 votes). Check out past polls.