Page 1 of 1

fetching mutiple rows from mysql in an array

Posted: Tue Feb 17, 2004 8:45 am
by crandym
I have written database a query function which handles all select, update, insert and delete queries. In the event there is an error, the function logs the error to a log file.

The SELECT portion of the function returns an array via. mysql_fetch_array while the other return mysql_fetch_row results.

I would like all queries in my application to be handled through this single function. There area that doesn't currently work is when performing SELECT queries which return multiple rows. The mysql_fetch_array seems to work fine when retrieving mutiple columns from the same row, but not when retrieving multiple rows.

Currently, I am handling multiple rows in a while loop with mysql_fetch_array($result). However I am not able to get the multiple rows from the db_query function I wrote which returns data via. mysql_fetch_row.

Anyone have any suggestions?

thanx

crandym

Posted: Tue Feb 17, 2004 9:56 am
by McGruff
The code?

Posted: Tue Feb 17, 2004 11:31 am
by crandym
the db function performs something like this:

function db_query ($query) {
$conn = db_connect();
$result = @mysql_query($query, $conn);
return @mysql_fetch_array($result);
}

Calling routine looks something like this:

$query = "select name, address from customer where custid = '2'";
$list = db_query(query);

In this case, the $list end up with an array of containing name and address entries.


The following function selects multiple rows that are inserted into a combo.

function create_state_combo($select_state) {
// get list of all models
$conn = db_connect();
$out = "";
$query = "select code from state_codes order by code asc";
$list = mysql_query($query, $conn);

while ($row = mysql_fetch_array($list)) {
$state_name = $row["code"];
if ($select_state == $state_name) {
$out = $out.'<OPTION value="'.$state_name.'" selected>'.$state_name.'</OPTION>';
} else {
$out = $out.'<OPTION value="'.$state_name.'">'.$state_name.'</OPTION>';
};
};
return $out;
}


I would prefer to pass the query off to the db_query function since it handles error messaging/logging, etc....

If I change
$list = mysql_query($query, $conn);

to
$list = db_query($query);

The return results only get a single row of data.

thanx


crandym

Posted: Tue Feb 17, 2004 11:36 am
by Illusionist
i dont really understand what your trying to do, but maybe this will help:

Code: Select all

while ($row = mysql_fetch_array($result)) {
    echo $row['name'];
    echo " - ";
    echo $row['phone'];
    echo "<br />/n/r";
}

Posted: Tue Feb 17, 2004 11:45 am
by crandym
I would like to pass the query to a general database interface function (i.e, db_query) and have it execute the query and return the results to the calling routine. Then in the calling routine, I would like to perform the while loop to get the data.

The reason I would like to do this is because the db_query function handles everything in one place (i.e., database connection, validation, query, error handling, etc...). In the example I provided, the state combo function currently would have to handle this itself.

Bottom line, the db_query function works fine if you are retrieving multiple collumns of data from same row, but does not work correctly if the query is selecting multiple rows (for use in while loop).

Posted: Tue Feb 17, 2004 12:56 pm
by McGruff
the db_query function handles everything in one place (i.e., database connection, validation, query, error handling, etc...)
I'd suggest you don't do that. Each fn should do just one thing.

If you want to encapsulate this lot behind an interface that means a class - or rather classes - of some kind - not a single fn.

It's best practice (but not required) if a wrapper fn for a native php fn returns the same values as the native fn. That would mean refactoring out the db connect:

Code: Select all

<?php
function dbConnect($server, $user_name, $password) 
{
    return mysql_connect($server, $user_name, $password); // etc
} 
?>
Not all queries have a result to return; you might want to check mysql_num_rows before fetching table rows; another script might need to check affected rows before proceeding. dbQuery should simply returning the [db]_query() call directly. One fn, one task.

Code: Select all

<?php
// $location (__FILE__  . ' | ' . __LINE__) is handy for development to find the query which failed.
function dbQuery ($query, $conn, $location) 
{
    return mysql_query($query, $conn) or die('sql error: ' . mysql_errno() . ' - '
                                        . mysql_error() . '<br/>' . $location . 
                                        '<br />'
                                        );
}

?>
Refactoring helps with debugging since it's easier to focus on the part of the script which isn't behaving as expected.

Posted: Tue Feb 17, 2004 8:15 pm
by DuFF
Right now, if you want to do it that way you will have to take out a line in your function:

Code: Select all

<?php
function db_query ($query) {
$conn = db_connect();
$result = @mysql_query($query, $conn);
return @mysql_fetch_array($result);  // REMOVE THIS LINE AND JUST RETURN $result
} 
?>