[SOLVED] Odd PHP MySQL query problem

[chris23]

I have a simple MySQL table of customers. Some customers are agencies of other customers.

 

The table has this structure:

CustomerKey  int(5) PRI

Name            varchar(35)

AgencyID      int(5)

...

 

I use the AgencyID column to identify the CustomerKey of the customer that is the agency if there is one, else AgencyID=0.

 

For example:

CustomerKey    Name        AgencyID

100                Customer1        0

101                Customer2      101

 

So Customer2 is an agency of Customer1 and Customer1 is not an agency of anyone.

 

I want to return a list of customers and if it belongs to an agency, the name of the company that is its agency. This query works great from the MySQL command line:

 

select Customers.Name, C2.Name as AgencyName from Customers left join Customers C2 on Customers.AgencyID=C2.CustomerKey;

 

It returns:

 

Name            AgencyName

Customer1      NULL

Customer2      Customer1

 

But when I try to execute that query from within PHP5 I get no results back ("No customers found.").

 

<?php

$query="select Customers.Name, C2.Name as AgencyName from Customers left join Customers C2 on Customers.AgencyID=C2.CustomerKey";

$result=mysql_query($query);

if (!$row=mysql_fetch_array($result))  // no records returned

    die('No customers found.');

?>

 

mysql_errno() returns 0, mysql_error() returns no error, and I get no Warnings.

 

I'm stumped. Anyone have any ideas?

 

Thanks in advance.

Chris

 

 

[Psycho]

Hmm... try to add some debugging info. I'd also use a slightly different process.

 

<?php
<?php

$query="select Customers.Name, C2.Name as AgencyName from Customers left join Customers C2 on Customers.AgencyID=C2.CustomerKey";

$result=mysql_query($query) or Die (mysql_error());

if (!mysql_num_rows($result)) {
  echo 'No customers found.';
} else {
  echo 'There were ' . mysql_num_rows($result) . ' records returned.';

  while ($record = mysql_fetch_array($result)) {
    //Display the results
  }

}

?>



?>

[chris23]

It appears that the server was caching a previous version of the PHP script which was executing a different query.