DBI/MySQL question.

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

I'm using a MySQL database to store information, and the DBI module for all of my database related stuff. Now, I have a table called user_items, with multiple rows that are going to have the same index number. One method I've been shown for fetching things is:

$sth= $dbh->prepare( "SELECT * FROM user_items WHERE  indexnum = $valu
+e");
$sth->execute();
$sth->bind_col( 2, \$val1);
$sth->bind_col( 3, \$val2);
$sth->bind_col( 4, \$val3);
while($sth->fetch) {
 print "$val1, $val2, $val3\n";
}
[download]

However, this method only appears to work on things where the value in the column I'm checking is unique to that row. What would I do to fetch the results of a SELECT statement, when multiple rows have the same value for something?

thanks,
Spidy

My Website

Comment on DBI/MySQL question. Download Code

Replies are listed 'Best First'.
Re: DBI/MySQL question. by Zaxo (Archbishop) on Jul 21, 2005 at 02:51 UTC
I don't see anything wrong with what you're doing, and your select should work the way you expect it to. What evidence do you have that you're only getting uniquely indexed rows as results? You might try a sharper-focused retrieval method, like `fetchrow_array()`. Do you have RaiseError set in the dbh? After Compline, Zaxo	[reply]
Re^2: DBI/MySQL question. by Spidy (Chaplain) on Jul 21, 2005 at 04:27 UTC
Well, the two values I'm storing in this table are indices from two other tables. If a user(the first index being stored) has more than just one of something out of the storage(second index), and I'm selecting/fetching by the user index, what should I do? I'm not sure what exactly would happen if I used the fetch method I've got above. My Website	[reply]
Re: DBI/MySQL question. by CountZero (Bishop) on Jul 21, 2005 at 19:38 UTC
I tried your code and it works the way it should, but personally I don't like the `$sth->bind_col( 2, \$val1);` bits. I find this rather opaque and doing too much of action at a distance. Also the `$sth->fetch` method does not say much what it is doing: is it fetching one row at a time or all at once; in which fields do the results go (you have to check your `$sth->bind_col` incantations to know); ... I would re-write your code as follows: `my $sth= $dbh->prepare( 'SELECT * FROM user_items WHERE indexnum = ?') +; $sth->execute($value); while(my (undef, $val1, $val2, $val3) = $sth->fetchrow_array) { print "$val1, $val2, $val3\n"; }` [download] At a glance you can see that you fetch one row at a time and return the fields as an array and that the second, third and fourth field are being stored in the (lexical) variables `$val1`, `$val2` and `$val3` Note that I pulled the `$value` variable out of the SQL statement and used a placeholder to avoid any quoting problems. The `$value` goes into the `execute`-method. Much cleaner that way! CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l] [select]