fetching mutiple rows from mysql in an array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
crandym
Forum Newbie
Posts: 21
Joined: Thu Jan 29, 2004 8:52 am

fetching mutiple rows from mysql in an array

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

The code?
crandym
Forum Newbie
Posts: 21
Joined: Thu Jan 29, 2004 8:52 am

Post 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
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post 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";
}
crandym
Forum Newbie
Posts: 21
Joined: Thu Jan 29, 2004 8:52 am

Post 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).
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
Last edited by McGruff on Tue Aug 09, 2005 5:33 pm, edited 1 time in total.
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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
} 
?>
Post Reply