Different Number of Rows Returned (PHP v MySQL)

[cr750]

I've written a piece of SQL that works at the MySQL prompt, but doesn't work as expected through php.

MySQL version 5.0.95

 
 
mysql> SELECT    ->   
a.date,    ->   a.author,    
->   a.comment,    ->   b.first AS 
p_first,    ->   b.last  AS 
p_last,    ->   c.first AS 
a_first,    ->   c.last  AS 
a_last    -> FROM    ->   
pilot_activity a,    ->   person 
b,    ->   person c    -> 
WHERE    ->   b.id = 
a.pilot_id    ->   AND c.id = 
a.author    -> ORDER BY    
->   p_last ASC    -> 
;+---------------------+--------+-----------------------------+---------+-----------+---------+-----------+| 
date                
| author | 
comment                     
| p_first | p_last    | a_first | a_last    
|+---------------------+--------+-----------------------------+---------+-----------+---------+-----------+| 
2010-03-21 10:02:06 |     50 | OPERATIONAL 
PROCEDURES....  | Phill   | Glasson   | 
John    | Wilkinson || 2013-08-12 00:00:00 
|     50 | 1 
1                         
| John    | Wilkinson | John    | Wilkinson 
|+---------------------+--------+-----------------------------+---------+-----------+---------+-----------+2 
rows in set (0.00 sec)

But the PHP script, using the same query

 
  $sql = <<<SQLSELECT  a.date,  
a.author,  a.comment,  b.first AS p_first,  
b.last  AS p_last,  c.first AS a_first,  c.last  AS 
a_lastFROM  pilot_activity a,  person b,  person 
cWHERE  b.id = a.pilot_id  AND c.id = a.authorORDER 
BY  p_last ASCSQL;

returns three rows!

 

 

 

<br>Phill Glasson<br>2010-03-21 10:02:06 OPERATIONAL PROCEDURES.... (John 
Wilkinson)<br>
<br>Phill Glasson<br>2010-03-21 10:02:06 OPERATIONAL PROCEDURES.... (John 
Wilkinson)<br>
<br>John Wilkinson<br>2013-08-12 00:00:00 1 1 (John Wilkinson)<br>

 

 

 

 

 

[mac_gyver]

cannot really help you with what your php code is doing without seeing the relevant code from the point where you are executing the query through to the end of your loop processing the rows and possibly your code displaying the result if you are not doing that inside of the loop.

[cr750]

  Here's a more complete code fragment...

$sql = <<<SQL
SELECT 
a.date,  
a.author,
a.comment,
b.first AS p_first,  
b.last  AS p_last, 
c.first AS a_first, 
c.last  AS a_last
FROM
pilot_activity a,
person b,
person c
WHERE  b.id = a.pilot_id  AND c.id = a.author
ORDER BY  p_last ASC
SQL;
    $result = mysql_query($sql, $db);    
    require ('QueryCheck.inc');
    while ($myrow = mysql_fetch_array($result)) {
        $line .= "<br>". $myrow["p_first"]. " ". $myrow["p_last"];
        $line .= "<br>". $myrow["date"];        
        $line .= " ".    $myrow["comment"];
        $line .= " (".   $myrow["a_first"]. " ". $myrow["a_last"]. ")<br>";
        echo $line;
    }

[mac_gyver]

that's pretty messy code. i had to clean it up to even tell what is was doing.

 

the problem is you are concatenating $line to itself and echoing it at the end of each pass through the loop, so it keeps adding each new row of data to what has already been built and echoed.

 

the first assignment to $line needs to remove the concatenation dot so that you are not reusing the previous contents in $line.

[cr750]

Sorry about the messy code. A problem with copy/paste between putty and the "code" box. I tidied it up pretty much as soon as I posted it, but you really jumped the gun!  

 

And yes, the reuse of $line was the problem. Nicely sorted now.

 

Thanks mac_gyver!

 

Best regards,

Phill.