Inner Join in MySQL Not Displaying Some Fields When One Condition Is Added

I am at a loss as to why this inner join is not working.

       $Conn = mysql_connect($HOST, $USER, $PASSWORD);
       mysql_select_db($DBName, $Conn);
    
    $Q = "
        SELECT
            Credit.ID,
            Credit.UserName,
            Credit.Contact,
            Credit.Phone,
            Credit.Status,
            Credit.OriginDate,
            Credit.Approval,
            Accounts.StoreTitle,
            Accounts.AppType,
            Accounts.AcctName
        FROM
            Accounts
        INNER JOIN
            Credit ON Credit.ID = Accounts.AppID
        WHERE
            Credit.UserName = '$_SESSION[User]'
            AND
                (Credit.Approval IS NULL OR Credit.Approval = ' ')
            AND
                Accounts.AppType != 'Location'
            AND
                CreditApp2.Status = 'Store'
           ORDER BY
                AccountsMain.StoreTitle";
    $Result = mysql_query($Q,$Conn);
    if(!$Result){$Error[] = "Database Error!";}
    else
    {
       if(mysql_num_rows($Result) > 0)
       {
          while($A = mysql_fetch_array($Result))
          {
             $ODate = dFormat($A['OriginDate'],5);
             $StTitle = strlen($A['StoreTitle']) > 34 ? substr($A['StoreTitle'],0,32).'...' : $A['StoreTitle'];
             $Cust = strlen($A['AcctName']) > 34 ? substr($A['AcctName'],0,32).'...' : $A['AcctName'];
             echo('
             <a class="StoreRow" href="somefile.php?AppID='.$A['ID'].'">
                <div style="clear:left; float:left; width:240px; text-align:left;">'.$StTitle.'</div>
                <div style="float:left; width:240px; text-align:left;">'.$Cust.'</div>
                <div style="float:left; width:50px;">'.$A['AppType'].'</div>
                <div style="float:left; width:70px; text-align:left;">'.$A['UserName'].'</div>
                <div style="float:left; width:50px; text-align:left;">'.$A['Status'].'</div>
                <div style="float:left; width:70px; text-align:left;">'.$ODate.'</div>
             </a>');
          }
      }
   }

Open in new window

The query works fine until it gets to

    (Credit.Approval IS NULL OR Credit.Approval = ' ')

What this means is that if I run the query without that phrase, all fields ($A[ID], $STitle, $Cust, $A[AppType], $A[UserName], $A[Status], $ODate) will display.

If I run it with that phrase, the only fields that will display are $A[ID], $A[UserName], $A[Status], $ODate, which are the fields from the Credit table. The fields **not** showing are from the Accounts table.
 
The Approval field is valid.

---

Just so I don't have to explain later, Yes, this is mysql (not mysqli or PDO) and yes, I know it is obsolete. It is what I have to work with.
LVL 1
RationalRabbitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohan singhWeb developerCommented:
I Think you should be use RIGHT OUTER JOIN


Thank you

Regards Mohan Singh
0
RationalRabbitAuthor Commented:
There is actually more to the query, which I just added.  I'll have to read up on RIGHT OUTER JOIN.
0
RationalRabbitAuthor Commented:
Doesn't seem to make a difference.
I should mention that all ID's in Credit have a matching AppID in Accounts. The Accounts table simply holds the account information (Company name, address, etc) It is used in other queries with other tables.
Ideally, the "ID" would be in Accounts, with AppID being the foreign key, but, due to the original code, the ID in Credit is the originating ID key. (Just an explanation)
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Chris StanyonWebDevCommented:
When you say the columns won't display, I'm assuming you mean the columns are empty.

Without seeing your data, it's difficult to say. For example, it looks like those Account columns are blank in the DB for Credit records that don't have an Approval.
0
RationalRabbitAuthor Commented:
If they don't have an Approval, they do not appear on the list. For example
Without the condition, since Approval has only 2 conditions (the equivalent of true or false) the list will appear like this:
(The only difference is that records with an approval will show as well as those that don't)
File1.png
With the condition, since I haven't formatted for blank entries (there shouldn't be any), it will look like this:
(The Accounts columns are not produced by the query)
File2.png
0
Chris StanyonWebDevCommented:
OK. First off, there looks to be a couple of typos in your code that need fixing.

You're missing a closing parenthesis from the while clause:

while($A = mysql_fetch_array($Result) )

In your output, you have the wrong variable name:

text-align:left;">'.$STitle.'</div>

It should be $StTitle

You should turn on error reporting at the start of your script. Add this right at the beginning:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Open in new window

Fix those issues and run your code again. Currently, those screengrabs of rows don't seem to match the code that you've posted.

You say the Accounts columns aren't being generated by the DB - pretty sure that's not correct. For debugging purposes, instead of outputting  some formatted HTML, just do a simple var_dump() of the data and see what you get:

while($A = mysql_fetch_array($Result)
{
    var_dump($A);
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RationalRabbitAuthor Commented:
Sorry for the typos. They are not in the original code, though. I'll correct them if I can still edit the question.

Seems that I have jumped to some conclusions here.  Originally, all this data was in one file. The accounts data was removed from that file and moved to the new Accounts file. It appears that, for some reason, the newer, un-Approved records are missing StoreTitles and CompanyNames and those were the only records that showed up when the condition was active. :)

Yes, the simulated lists should not have shown CompanyName.

Thanks for making me think. Should have done a var dump in the first place. At the end of an 18-hour day. Will address this in the AM but yes, it appears you are correct. The problem appears to be in the database.
0
Chris StanyonWebDevCommented:
Haha - developing after 18 hours always benefits from fresh eyes (or a really big cup of coffee!)
0
RationalRabbitAuthor Commented:
Yep, that was the problem. Thanks, Chris!
0
RationalRabbitAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.