Steve, Thanks a ton! I'm now running slick as a whistle using your option #1 (looping through the results in the function) and using the newer mysql_fetch_assoc() function. I have a much better understanding of this now and I really appreciate your help. Hopefully I'll get a chance to help someone else next time. :-)
"Steve Edberg" <[EMAIL PROTECTED]> wrote in message news:p05100300b943f2a7feef@[168.150.239.37]... > 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 General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
