DBI - MySQL Help Needed

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

This should be simple but I'm a newbie. I've looked and searched for the answer but I can't seem to figure it out.

I can place data in my MySQL DB but can't figure out how to get the info back out and loaded into useable variables..

For this example my table is called "testtable" and it has been loaded with these simple records "id", "username", and "email". I want to check "testtable" for the existence of a particular email address, and if found I want the corresponding "username" to be loaded into a variable that I can use elsewhere in the program. If I use this basic SELECT command: .

$sth = $dbh->do("SELECT username FROM testtable WHERE email = 'myname@mydomain.com'"); .

and then print $sth it returns "1". This apears to represent the number of matches. If no match is found it returns "0E0"; I don't know what OEO is but assume it means something like NULL. Printing "$username" returns nothing..

How do I get the username into a variable that I can use?

Comment on DBI - MySQL Help Needed

Replies are listed 'Best First'.
Re: DBI - MySQL Help Needed by rdfield (Priest) on Dec 02, 2002 at 14:51 UTC
`$dbh->do` is generally used for non select statements. Read up on `$sth = $dbh->prepare(...); $sth->execute(...); $result_var = $sth->fetch...;` [download] (Note that there are many different 'fetch'es, so take your pick as suits your needs). Tutorials is a good place to start, and there are plenty of DBI examples in the various nodes in the Monestary. rdfield	[reply] [d/l] [select]
Re: DBI - MySQL Help Needed by Abigail-II (Bishop) on Dec 02, 2002 at 15:07 UTC
RTFM. `man DBI`, and early on, in the outline usage section, you find: `The typical method call sequence for a "SELECT" statement is: prepare, execute, fetch, fetch, ... execute, fetch, fetch, ... execute, fetch, fetch, ...` [download] And further down, when discussing the `do` method: `It should not be used for "SELECT" statements because it does not return a statement handle (so you can't fetch any data).` [download] Consider consulting the manual before asking questions like this. Abigail	[reply] [d/l] [select]
Re: DBI - MySQL Help Needed by gjb (Vicar) on Dec 02, 2002 at 14:43 UTC
Please check out the PerlMonks tutorials, especially the one on reading from a database. I'm sure this will help ;-) -gjb-	[reply]
Re: DBI - MySQL Help Needed by Anonymous Monk on Dec 02, 2002 at 16:42 UTC
personally, I prefer to use methods `prepare()` and `execute()` which makes it really easy to re-use queries and whatnot. After this point... To get one row into hash references: ------------------------------------ `my $row = $sth->fetchrow_hashref; Then grab your values with: $row->{'my_column'}` [download] For multiple rows in hash references: ------------------------------------- `while(my $row = $sth->fetchow_hashref) $row->{'my_column'}` [download] Take a look at the function definitions that come with DBI for explicit definitions, but you can fetch rows in a number of ways with hashes and arrays. DBI ROX!!! `--Andyram27--`	[reply] [d/l] [select]
THANK YOU! THANK YOU!! by GreenMnMs (Initiate) on Dec 02, 2002 at 22:39 UTC
Wow! Like I said, I'm new to DBI and SQL in general and appreciate all the fantastic advise and help! My code is working now thanks! I'm also new to Perl Monks (as if you couldn't tell) and although I looked around before asking, some how I totaly missed the tutorial section, what an awesome resource! Monks Rock!!	[reply]