Re^2: Retrieving multiple records from database

Thanks gmax (I was secretly hoping you'd see this OP). Ended up with a more familier:

$stmt = qq/SELECT somedata FROM table WHERE id IN (/ . join(',', ('?')
+ x @ids_to_match ) . qq/)/;
 $sth = $dbh->prepare($stmt);
 $sth->execute(@ids_to_match);
 my $results = $sth->fetchall_arrayref({});
[download]

My next question is why does fetchrow_array() only return an array of 1 element, but fetchall_arrayref({}) nabs them all?

—Brad
"Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton

Comment on Re^2: Retrieving multiple records from database Select or Download Code

Replies are listed 'Best First'.
Re^3: Retrieving multiple records from database by jZed (Prior) on Oct 16, 2004 at 19:07 UTC
My next question is why does fetchrow_array() only return an array of 1 element, but fetchall_arrayref({}) nabs them all? Well the difference between fetchrow and fetchall explains why fetchrow gets just one row and fetchall gets them all. The difference between fetchrow_array and fetchrow_arrayref explains why using a scalar returns 1 value for an array and all values into an arrayref. If you want the full row, you need to do @row = fetchrow_array(), not $row = fetchrow_array().	[reply]
Re^3: Retrieving multiple records from database by davido (Cardinal) on Oct 17, 2004 at 06:38 UTC
The array that "fetchrow_array()" returns is the selected columns (each in their own element of the array) from a single row. If your query grabbed multiple rows, you need to keep calling fetchrow_array() to get the next row, and so on. Undef will indicate you're done. fetchall_arrayref() fetches all of the rows, in a multidimensional array where the 2nd dimension is the columns. Dave	[reply]
Re^4: Retrieving multiple records from database by bradcathey (Prior) on Oct 17, 2004 at 11:52 UTC
Thanks, davido, I ended up with this in my final app: `while ($row = $sth->fetchrow_array()) { push (@categories, $row); }` [download] I guess `fetchrow` and `fetchall` we appropriately named ;^) Now, I wish there were a `fetchcol` that just retrived a known column of a given row (intersection, so `selectcol_arrayref` wouldn't work) so I don't have to do: `$single_value = $categories[0];` [download] after the fetch, and could just do: `$single_value = $sth->fetchcol_justone;` [download] —Brad "Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton	[reply] [d/l] [select]
Re^5: Retrieving multiple records from database by davido (Cardinal) on Oct 17, 2004 at 15:41 UTC
You have to build the column into your SQL statement. The first statement here will select all columns. `SELECT * FROM mytable WHERE age = ?` [download] And this will select just one column: `SELECT firstname FROM mytable WHERE age = ?` [download] In the case of the second SQL statement, fetchrow_array() will return an array of one single element. There is a pretty good basic SQL tutorial at http://www.w3schools.com/sql/default.asp. Another alternative is the $slice parameter of the `fetchall_arrayref()` method. By passing an anonymous arrayref containing the element numbers you're after, you can "slice" the arrayref for each row so that only certain columns are seen. This is documented in DBI. Dave	[reply] [d/l] [select]