[PHP-DB] Re: [PHP] Usiing FOREACH to loop through Array From: Brad Melendy (brad <email protected>)
Date: 06/30/02

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" <sbedberg <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 sbedberg <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