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
fetching mutiple rows from mysql in an array
Moderator: General Moderators
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
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
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";
}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).
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).
I'd suggest you don't do that. Each fn should do just one thing.the db_query function handles everything in one place (i.e., database connection, validation, query, error handling, etc...)
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
}
?>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 />'
);
}
?>
Last edited by McGruff on Tue Aug 09, 2005 5:33 pm, edited 1 time in total.
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
}
?>