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" <[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
--
+------------------------------------------------------------------------+
| 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
