Getting MySQL Data into an Array.

[cdherold]

Hey Monks,

I'm trying to get information from a database into an array in Perl. The database (MySQL) has company names each as a separate row. There are about 300. I would like to put these into an array called "@companies". Its been a long time since i've worked with Perl ...

$dsn = "DBI:mysql:yip;db.yip.com";
$dbh = DBI->connect($dsn,'me','pass');        
$sth = $dbh->prepare("SELECT company FROM company_public;");
$sth->execute(@companies);
$dbh->disconnect;

I thought that this (above) might put the values into the array for me, but no go on this first try. What do you think?

thanks Monks,

Penguin In The Dark

[coolmichael]

You are very close. The problem is that you first have to execute the query, which in itself does not return any results. After you execute the query, you then fetch the results, one row at a time. I'm not aware of a way to get all the results at once, sorry. Perhaps another monk can enlighten? For more information, see perldoc DBI and also references, or try this:

$dsn = "DBI:mysql:yip;db.yip.com";
$dbh = DBI->connect($dsn,'me','pass');
$sth = $dbh->prepare("SELECT company FROM company_public;");
$sth->execute();

while($ref = $sth->fetchrow_arrayref) {
    push @companies, $ref->[0];
}

$dbh->disconnect;

---

update: added the semicolon. Thanks Parham. </code>

[Parham]

i think coolmichael explained is pretty well... but if you want to dump all your information into an array before starting to work and avoiding a while loop altogether, try this:

note: coolmichael, you forgot the semicolon at the end of your code ;)

$dsn = "DBI:mysql:yip;db.yip.com";
$dbh = DBI->connect($dsn,'me','pass');
$sth = $dbh->prepare("SELECT company FROM company_public;");
$sth->execute();
$companies = $sth->fetchall_arrayref; #a reference to an array of arra
+ys of references to each row
$dbh->disconnect;

foreach $row (@$companies) {
($company) = @$row;
print "$company\n"; #just printing it out for fun
}

if your starting out new with mysql and perl... this tutorial should help you a lot: perlguy

[Corion]

There are some nitpicks I do have with this tutorial.

First of all, it's the absence of use strict;, which is not really good in code that will be cut, pasted and modified, possibly with typos.

The mix of implicit return values and explicit return ($sth) statements is also likely to bother the newbie.

The author also seems to never have heard of the quote method (see http://mysql.turbolift.com/mysql/DBD_3.21.X.php3#quote for example). I'm not sure if this method is available under every database driver, but it's at least worth a mention.

Another problem comes from the naive error-handling approach when mixing CGI and the rest of the world - the author prints the full error message back to the end user, something which will at best confuse the end user, in the worst case this will provide crucial information to an attacker. Of course, the topic of the tutorial is databases and not CGI, but why mix in CGI in the first place ?

perl -MHTTP::Daemon -MHTTP::Response -MLWP::Simple -e ' ;    # The  
$d = new HTTP::Daemon and fork and getprint $d->url and exit;#spider
($c = $d->accept())->get_request(); $c->send_response( new   #in the
HTTP::Response(200,$_,$_,qq(Just another Perl hacker\n))); ' #  web

[kal]

cdherold - execute () passes data that you want in any placeholders, e.g.:

$sth = $dbh->prepare ('SELECT * FROM users WHERE id = ?');
$sth->execute ($my_id);

What you're looking for is $sth->fetchrow_array (), or some other variant. You can also use the 'latest' additions to DBI to grab the lot, with a $sth->fetchall_arrayref ()

[miyagawa]

Try this:

@companies = map { $_->[0] } @{$dbh->selectall_arrayref('SELECT compan
+y FROM company_public')};

--
Tatsuhiko Miyagawa
[e-mail]

[Anonymous Monk]

Wow... that is very neat.

[cdherold]

thanks for opening my mind. that's a cool one.

[jeffa]

Yes, that is indeed cool, but don't let your boss catch you using that. DBI has a method that will return a column for you:

my @companies = @{$dbh->selectcol_arrayref('
   SELECT company FROM company_public
')};

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

[Spenser]

This is a minor thing, but you may want to end up your mySQL stanza with a clean finish() in addition to the disconnect().

$sth->finish();
$dbh->disconnect();

It will work without the finish, but it's more explicit and it cuts down on pesky warning messages in /var/log/httpd/error_log like this:

DBI::db=HASH(0x82afc60)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at...

As the warning message states, don't disconnect before finishing.  Also, don't do either before you're done extracting your data from mySQL.

That's Spenser, with an "s" like the detective.

[jonjacobmoon]

Are you trying something new, experientmenting, what? Are you asking why THIS doesn't work or are you really asking how to put the database into an array?

I have to be honest. I am confused why you are asking this when it is so easy to lookup. I want to give you the benefit of the doubt and say your experimenting with DBI's execute method, but then again, you neither asked the question that way, or appear to have looked at the perldoc for DBI. So, it is hard to tell. Perhaps, you need to rephrase the question (or read the perldoc).

---

I admit it, I am Paco

[tilly]

If the questioner is unaware of how to use perldoc, and most questioners indeed are, then your post amounts to saying, You are a moron for not doing something you don't know how to do. Why didn't you look up documentation you probably don't know how to find?

That is an absolutely and utterly useless thing to say. Here is a much better way at getting across the basic point you had:

It looks like you may not have read the documentation for DBI. If you know about it, take this as advice that you should try understanding that before asking questions. If you don't know how to find the documentation try typing perldoc DBI at the command line. To try to find out about a specific thing type </code>/execute</code> and you should jump right there.

An alternative that many people like to use is the documentation at the website http://www.perldoc.com. Note, however, that using this is slower, and using any kind of online documentation means you always have to worry about whether you are reading documentation for a different version of the software than what you have installed locally.

Do you see? The same basic diagnosis. Answering it takes about the same energy as what you did. The answer is far less likely to leave the other person thinking you think they are stupid. And in the likely scenario that they don't have the basic toolkit that you take for granted, the answer will be useful.

[cdherold]

thank you for the objective support,Tilly. It is appreciated.

[jonjacobmoon]

Okay, I have to say that Tilly is right on one point. If I am going to criticize the question, I should have been more specific. (I even gave Tilly a ++ vote for that point.)

I will do that next time.

However, my point is still valid and Tilly does not seem to recognize that. (We had a long discussion about it this morning.) While I am sure the cdherold is normally a fine programmer, s/he sounds like s/he is being lazy here.

S/he is smart and resouceful enough to know about DBI and got those variable names from the documentation or a close proximity there of, but sounds here to by saying:

"I tried once, it failed. Help me!"

I am not trying to scare off some relative newbie. I am trying to challenge them to think things through before asking the questions. Try it a few different ways, do some research. It does him/her a disservice to write their code for them.

I don't believe my tone was as harsh as Tilly says. If so, it was not my intention to sound harsh. I truely wonder if he/she was attempting to use execute() in a new way. If so, I commend that, but the question was worded poorly if that is the case.

---

I admit it, I am Paco.

[cdherold]

I usually send these questions in while I'm working on them on the side in a sort of parallel processing. For me, it's much more productive to get input along the way from the very knowledgable monks which is complementary to what I learn myself ... the monks almost always have a wise word from which I am able to grow further.

A bonus, is that every once in a while a venerated monk will have a very valuable tid-bit of information which is off the beaten path (my path anyways). That happened this time and I thank that monk (miyagawa) for taking the time to share his wisdom.

[tilly]

I would ask that you rethink using PM as a form of "parallel processing". If there are obvious resources that you know about which you have not yet checked, it saves time for all of us for you to check them first. Based on experience you will run across just as many unexpected nuggets, it will save you time, and we will have time and energy to devote to questions that help people more directly..

Note that even while deploring the tone of a response to you I reinforced the basic point that documentation is to be checked first. The answers that come here are a volunteer resource. Furthermore the people giving them are often experienced and qualified professionals. Our time and energy is likely worth at least what yours is if we were not choosing to volunteer it, so please treat it that way. (And our not entirely secret plot is to infect you with the attitudes, values, and knowledge that make us able to volunteer so that you turn around and eventually do likewise. Now please look into this white light and forget all about this...)

For more on the cultural underpinnings that lead to our acting this way, I highly recommend Re: Newby Query Comment by davorg.