Re: Usiing FOREACH to loop through Array

At 7:35 PM -0700 6/29/02, Brad Melendy wrote:

Steve, Thanks very much.

You're welcome!

This make total sense to me. Now, I was pretty sure that I was getting an array back from mysql_fetch_array because when I do a 'print $myCourses' without specifying any value, I just get 'array' which I believe I am supposed to, IF it is truly an array. Anyway, I'm going to revisit the mysql_fetch_array function right now. Thanks for your help, it is greatly appreciated. ...Brad

Just to clarify a bit - mysql_fetch_array() DOES return an array - thus the name - but it is an array containing one record from the result set. For instance, if the result of the statement SELECT C.CourseName FROM tblcourses C, tblusers U, tblEnrollment E WHERE C.ID = E.CourseID AND E.UserID = U.ID AND U.ID = 1 through the MySQL commandline was +-----------------+

   | C.CourseName    |

+-----------------+ | Basketweaving | | Noodle twirling | | Poodle furling | | Puddle curling | +-----------------+ then the first use of mysql_fetch_array($result) through PHP would retrieve the array 'C.CourseName' => 'Basketweaving', 0 => 'Basketweaving' the second call would retrieve 'C.CourseName' => ' Noodle twirling', 0 => ' Noodle twirling' and so on. If you had two columns in the result set: +-----------------+------------+

   | C.CourseName    | C.CourseNo |

+-----------------+------------+

   | Basketweaving   | 12         |
   | Noodle twirling | 23         |
   | Poodle furling  | 24         |
   | Puddle curling  | 25         |

+-----------------+------------+ you would get results like 'C.CourseName' => 'Basketweaving', 0 => 'Basketweaving', 'C.CourseNo' => 12, 1 => 12 and so on. The reason you get the doubled keys (associative & numeric) is for historical compatibility; to turn off the feature, and retrieve only the associated indexes (the behavior you normally want), you can use mysql_fetch_array($result, MYSQL_ASSOC); Incidentally, I just learned something here myself; there's a relatively new (>= version 4.0.3) function mysql_fetch_assoc() which does what you want...return ONLY the associated indexes. See: http://www.php.net/manual/en/function.mysql-fetch-assoc.php -steve

"Steve Edberg" <sbedberg@ucdavis.edu> 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

-- +------------------------------------------------------------------------+ | Steve Edberg sbedberg@ucdavis.edu | | 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/) | +------------------------------------------------------------------------+