At 3:27 PM -0700 6/29/02, Brad Melendy wrote:
>Hi All,
>I've stumped myself here. In a nutshell, I have a function that returns my
>array based on a SQL query and here's the code:
>
>-------------begin code-------------------
>function getCourses($UID)
> {
> global $link;
> $result = mysql_query( "SELECT C.CourseName FROM tblcourses C, tblusers U,
>tblEnrollment E WHERE C.ID = E.CourseID AND E.UserID = U.ID AND U.ID =
>$UID", $link );
> if ( ! $result )
> die ( "getRow fatal error: ".mysql_error() );
> return mysql_fetch_array( $result );
> }
>------------end code ----------------
>
>I call this from a PHP page with the following code:
>
>------------begin code--------------
>$myCourses = getCourses($session[id]);
>foreach ($myCourses as $value)
> {
> print "<br>$value";
> }
>------------end code---------------
>
>Now, when I test the SQL from my function directly on the database, it
>returns just want I want it to return but it isn't working that way on my
>PHP page. For results where there is a single entry, I am getting the same
>entry TWICE and for records with more than a single entry I am getting ONLY
>the FIRST entry TWICE.
>
>Now I know my SQL code is correct (I am testing it against a MySQL database
>using MySQL-Front) so I suspect I'm doing something stupid in my foreach
>loop.
I think your problem lies in a misunderstanding of the
mysql_fetch_array() function. It doesn't return the entire result set
in an array - just one record at a time. You can fix this in one of
two ways:
(1) Loop though the entire result set in your function:
function getCourses($UID)
{
global $link;
$ResultSet = array();
$result = mysql_query( "SELECT C.CourseName FROM tblcourses C, tblusers U,
tblEnrollment E WHERE C.ID = E.CourseID AND E.UserID = U.ID AND U.ID =
$UID", $link );
if ( ! $result )
die ( "getRow fatal error: ".mysql_error() );
while ($Row = mysql_fetch_array( $result ))
{
$ResultSet[] = $Row['C.CourseName'];
}
return $ResultSet;
}
...
$myCourses = getCourses($session[id]);
foreach ($myCourses as $value)
{
print "<br>$value";
}
or (2) set a flag in getCourses() so that the query is only executed
once, otherwise returning a result line - something like:
function getCourses($UID)
global $link;
static $result = false;
if (!$result)
{
$result = mysql_query( "SELECT C.CourseName FROM tblcourses C,
tblusers U,
tblEnrollment E WHERE C.ID = E.CourseID AND E.UserID = U.ID AND U.ID =
$UID", $link );
if ( ! $result )
die ( "getRow fatal error: ".mysql_error() );
}
{
return mysql_fetch_array( $result );
}
...
while ($Row = getCourses($session[id]) as $value)
{
print "<br>", $Row['C.CourseName'];
}
(standard caveats about off-top-of-head, untested code apply)
The reason you are getting the first record TWICE is becaouse of the
default behaviour of the mysql_fetch_array() function. It returns
both an associative array - ie, elements of the form <field-name> =>
<value> - and a numerically indexed array (0, 1, 2, etc.). You can
alter this behaviour by the second parameter of the function: see
http://www.php.net/manual/en/function.mysql-fetch-array.php
-steve
>I'm hoping someone will spot my dumb mistake. Thanks very much for any help
>at all on this.
>
>....Brad
>
>
>
>--
>PHP General Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
--
+------------------------------------------------------------------------+
| Steve Edberg [EMAIL PROTECTED] |
| University of California, Davis (530)754-9127 |
| Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ |
+------------------------------------------------------------------------+
| The end to politics as usual: |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
+------------------------------------------------------------------------+
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
