php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #24879 Some data lost in returned array by mysql_fetch_array
Submitted: 2003-07-31 05:26 UTC Modified: 2003-08-04 10:37 UTC
From: sm at grand-prix dot ru Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.3.2 OS: Linux RedHat 9
Private report: No CVE-ID: None
View Developer Edit
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: sm at grand-prix dot ru
New email:
PHP Version: OS:

 

 [2003-07-31 05:26 UTC] sm at grand-prix dot ru 
Description:
------------
In some cases mysql_fetch_array returns numbered array element, but does'nt returns it's named copy.

This script was tested as a part of vbulletin's code.

Bug: in the first case $user[userid] is empty, but $user[0] contains correct data. For other user - 2'nd case - all is OK. Behavior depends from data, returned by mysql.


Reproduce code:
---------------
$query_string = "SELECT *,FROM_UNIXTIME(joindate) AS joindate,FROM_UNIXTIME(lastvisit) AS lastvisit,FROM_UNIXTIME(lastactivity) AS lastactivity,FROM_UNIXTIME(lastpost) AS lastpost,
                               avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar
                               FROM user
                               LEFT JOIN avatar ON avatar.avatarid=user.avatarid
                               LEFT JOIN customavatar ON customavatar.userid=user.userid
                               WHERE user.userid=412";

$res = mysql_query($query_string,$link_id);
$user = mysql_fetch_array($res);
print_r($user);


$query_string = "SELECT *,FROM_UNIXTIME(joindate) AS joindate,FROM_UNIXTIME(lastvisit) AS lastvisit,FROM_UNIXTIME(lastactivity) AS lastactivity,FROM_UNIXTIME(lastpost) AS lastpost,
                               avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar
                               FROM user
                               LEFT JOIN avatar ON avatar.avatarid=user.avatarid
                               LEFT JOIN customavatar ON customavatar.userid=user.userid
                               WHERE user.userid=287";

$res = mysql_query($query_string,$link_id);
$user = mysql_fetch_array($res);
print_r($user);


Expected result:
----------------
 Array (
 [0] => 412
 [userid] => 412
 [1] => 6
 [usergroupid] => 6
 [2] => SM_1
 [username] => SM_1
 .......
 
 Array (
 [0] => 287
 [userid] => 287
 [1] => 6
 [usergroupid] => 6
 [2] => SM
 [username] => SM
 .......

Actual result:
--------------
 Array (
 [0] => 412
 [userid] =>
 [1] => 6
 [usergroupid] => 6
 [2] => SM_1
 [username] => SM_1
 .......
 
 Array (
 [0] => 287
 [userid] => 287
 [1] => 6
 [usergroupid] => 6
 [2] => SM
 [username] => SM
 .......

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-07-31 13:33 UTC] sniper@php.net 
Please try using this CVS snapshot:

  http://snaps.php.net/php4-STABLE-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php4-win32-STABLE-latest.zip

Also let us know what mysql version you're using,
and what configure line was used to configure PHP.
 [2003-08-01 03:59 UTC] sm at grand-prix dot ru 
I have installed recommended version (last stable, 4.3.2). Problem persist.

Additional info: 
 
MySQL v3.23.56
Linux kernel v2.4.20 SMP
 [2003-08-01 04:02 UTC] sm at grand-prix dot ru 
./configure :
 --with-mysql=/home/mysql --with-apache=../apache_1.3.27rusPL30.17_match --enable-track-vars
 [2003-08-01 08:45 UTC] sniper@php.net 
Please provide a complete but short example script and mysql db schema with which we can reproduce this ourselves.
(we're not going to install vbulletin just to test this)
 [2003-08-01 08:45 UTC] sniper@php.net 
And I asked you to try the SNAPSHOT, NOT the latest stable release..
 [2003-08-01 12:08 UTC] sm at grand-prix dot ru 
I can provide more accurate test script and tables only on monday... And I can't install RC version, I'm not a hoster...

But some important information - this problem persist only for such database records, for which left joined records does'nt exist.
 [2003-08-04 03:13 UTC] sm at grand-prix dot ru 
As i mean, bug occurs when both tables (main and left joined) contains columns with equal names and left-joined table does'nt contain needed row. In the such case named array elements with names, equal to such column names are cleared.

Here is test script :
------------------------------------------------------------

<?php
  error_reporting(7);

  $srv = "mysql.server.ru";
  $usr = "username";
  $pwd = "password";
  $db = "database";

  $link_id=mysql_connect($srv,$usr,$pwd);  

  if (!$link_id) {
    echo "Can't connect to mysql server<br>";
    exit;
  }

  if (!mysql_select_db($db, $link_id)) {
    echo "Can't connect to mysql server<br>";
    exit;
  }

  if ($action == "create_bases") {

    $query = "CREATE TABLE test1table (
              userid int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
              usergroupid smallint(5) unsigned DEFAULT '0' NOT NULL,
              username varchar(50) NOT NULL,
              password varchar(50) NOT NULL,
              PRIMARY KEY(userid) )";

    $query_id = mysql_query($query,$link_id);
    if (!$query_id) {
      echo "invalid SQL:<br>$query<br>";
      exit;
    }


    $query = "CREATE TABLE test2table (
              userid int(10) unsigned DEFAULT '0' NOT NULL,
              field1 char(250) NOT NULL,
              field2 char(250) NOT NULL,
              field3 char(250) NOT NULL,
              field4 char(250) NOT NULL,
              PRIMARY KEY (userid) )";

    $query_id = mysql_query($query,$link_id);
    if (!$query_id) {
      echo "invalid SQL:<br>$query<br>";
      exit;
    }

    $query = "INSERT INTO test1table VALUES (1,6,'user1','password1')";
    $query_id = mysql_query($query,$link_id);
    if (!$query_id) {
      echo "invalid SQL:<br>$query<br>";
      exit;
    }
    $query = "INSERT INTO test1table VALUES (2,6,'user2','password2')";
    $query_id = mysql_query($query,$link_id);
    if (!$query_id) {
      echo "invalid SQL:<br>$query<br>";
      exit;
    }

    $query = "INSERT INTO test2table VALUES (1,'dummy1','dummy2','dummy3','dummy4')";
    $query_id = mysql_query($query,$link_id);
    if (!$query_id) {
      echo "invalid SQL:<br>$query<br>";
      exit;
    }

    echo "<br>tables created and initialized<br>";
    exit;

  }

  $query = "SELECT * FROM test1table
            LEFT JOIN test2table ON test2table.userid=test1table.userid
            WHERE test1table.userid=1";
  $query_id = mysql_query($query,$link_id);
  if (!$query_id) {
     echo "invalid SQL:<br>$query<br>";
     exit;
  }
  $array = mysql_fetch_array($query_id);
  print_r($array); 
  echo "<br><br>";
  mysql_free_result($query_id); 

  $query = "SELECT * FROM test1table
            LEFT JOIN test2table ON test2table.userid=test1table.userid
            WHERE test1table.userid=2";
  $query_id = mysql_query($query,$link_id);
  if (!$query_id) {
     echo "invalid SQL:<br>$query<br>";
     exit;
  }
  $array = mysql_fetch_array($query_id);
  print_r($array); 
  echo "<br><br>";
  mysql_free_result($query_id); 


?>
 [2003-08-04 08:22 UTC] georg@php.net 
see #21589
 [2003-08-04 08:33 UTC] sniper@php.net 
Bug #21589 is bogus -> bogus.
 [2003-08-04 10:37 UTC] sm at grand-prix dot ru 
Hmmm... But why numbered element is not equal to named?
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Thu Jul 03 11:01:34 2025 UTC