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

go to bug id or search bugs for

Bug #20078 Error with left join with mysql_fetch_array()
Submitted: 2002-10-25 04:07 UTC Modified: 2002-10-25 09:08 UTC
From: f dot giovannini at netechnologies dot it Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.2.1 OS: Linux 2.4.18
Private report: No CVE-ID: None
View Add Comment Developer Edit
 [2002-10-25 04:07 UTC] f dot giovannini at netechnologies dot it 
Using mysql_fetch_array function if submit an query with left join es.:
select * from A left join B on (A.index_c=B.index_c)

the resulting array contains fields of table B with NULL value if condition is not matched
es.:

A table:
----------------------------
| index | index_c | X | P  |
----------------------------
|   1   |    1    | 1 |  1 |
----------------------------
|   1   |  NULL   | 1 |  1 |
----------------------------
B table:
-------------------
| index_c | P | D |
-------------------
|    1    | 1 | 2 |
-------------------
Array result:
---------------------------------
| index | index_c | X | P  |  D |
---------------------------------
|   1   |    1    | 1 | 1  |  2 |
---------------------------------
|   1   |  NULL   | 1 |NULL|NULL|
---------------------------------

with command line mysql:
---------------------------------
| index | index_c | X | P  |  D |
---------------------------------
|   1   |    1    | 1 | 1  |  2 |
---------------------------------
|   1   |  NULL   | 1 | 1  |    |
---------------------------------

bye

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-10-25 04:08 UTC] derick@php.net 
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.php.net/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to "Open".

Thank you for your interest in PHP.
 [2002-10-25 04:15 UTC] f dot giovannini at netechnologies dot it 
This problem is verified with any mysql version....
$array
 [2002-10-25 04:23 UTC] msopacua@php.net 
What 'not enough information' meens in this case, is the lack of a short, self contained, copy/paste/run script, so we can try to reproduce the problem.
 [2002-10-25 04:32 UTC] georg@php.net 
Sorry, but your problem does not imply a bug in PHP itself.  For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system is not the
appropriate forum for asking support questions. 

Thank you for your interest in PHP.

If you use mysql_fetch_array with MYSQL_FETCH_ASSOC, be sure that fieldnames are unique.

Georg
 [2002-10-25 04:49 UTC] hholzgra@php.net 
that's one of the reasons why you should never use 
'select * from ...' -> you haven't specified *which*
P column you want, A.P or B.P

php gives you B.P as P while the cmd line cliebt
chose to display A.P ...
 [2002-10-25 05:44 UTC] f dot giovannini at netechnologies dot it 
Why in the php 4.1.2 is ok?
The problem is the value NULL.... it not exist in database... why php retrive an inexistent value
 [2002-10-25 06:05 UTC] georg@php.net 
As I told you before, it's not a bug in PHP, it's the expected  result.

Please visit http://www.php.net/support.php or 
http://www.mysql.com/documentation/lists.html

Georg
 [2002-10-25 09:08 UTC] hholzgra@php.net 
Please understand how left joins work,
it is expected behavior that they return
NULL values for the right side tables field
if no matching entry for the join condition
is found

so B.P is NULL on the second row,
it is just that you have not defined *which*
P you want and sometimes you get A.P which is 1,
while under other circumstances you get B.P
which is NULL

try 

  select A.P as A_P, B.P as B_P from A left join B on (A.index_c=B.index_c)

try to avoid ambiguos field names in result sets using
'AS', and you will get predictable results. 'nough said!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Fri May 17 02:01:32 2024 UTC