PHP MySQL query doesn't return all results

[billyjeffjoe]

I am having a wierd problem. I have a MySQL database with some data in it. When I query it from the command line, it returns 179 rows with the expected content. If I do this:

$rows = mysql_num_rows($result);
printf("ROWS = $rows\n");

from PHP I get 179 back. However the actual number of rows output to the screen by PHP vary between 35 and 80 each time I reload the page. It is as if the connection is lost in the middle of the results being returned. There doesn't seem to be any rhyme or reason for it. There is no change to the database between reloads. The query is this:

$result = mysql_query("SELECT SourceIp, IF(COUNT(distinct Device)>1,'YES','') AS 'MULTIDEV', SUM(IF(MessageType='IDS',1,0)) AS 'IDS', SUM(IF(MessageType='NtwkACL',1,0)) AS 'ACL' FROM DredgeEvent GROUP BY SourceIp ORDER BY inet_aton(SourceIp);",$db) or die ("MySQL error: " . mysql_error());

I am, of course, not an expert at PHP or MySQL, but I have never had this problem before and I can't find a similar problem on Google. PHP and MySQL are on the same device. I don't get any errors when I reload the page. Any advice?

-Matt

 

[sleipnir214]

Can we see your script?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Olavxxx]

can you do this:

echo "SELECT SourceIp, IF(COUNT(distinct Device)>1,'YES','') AS 'MULTIDEV', SUM(IF(MessageType='IDS',1,0)) AS 'IDS', SUM(IF(MessageType='NtwkACL',1,0)) AS 'ACL' FROM DredgeEvent GROUP BY SourceIp ORDER BY inet_aton(SourceIp);";

Then, you copy that query, paste it in your mysql admin interface and check what happends?

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[billyjeffjoe]

Yes, I tried the echo statement and pasted the output into my MySQL command line. I got all 179 rows intact.

Here is my script. (Please don't laugh--I'm new and this is in the early stages...)

<?php

if ($OB = $_GET['OB']) {
} else {
$OB = 'TimeStamp DESC';
}




$WidthOne='40px';
$WidthTwo='110px';
$WidthThree='75px';
$WidthFour='50px';
$WidthFive='50px';
$Dent='out';
HeaderRow ($WidthOne, $WidthTwo, $WidthThree, $WidthFour, $WidthFive, $Dent);

$result = mysql_query("SELECT SourceIp, IF(COUNT(distinct Device)>1,'YES','') AS 'MULTIDEV', SUM(IF(MessageType='IDS',1,0)) AS 'IDS', SUM(IF(MessageType='NtwkACL',1,0)) AS 'ACL' FROM DredgeEvent GROUP BY SourceIp ORDER BY inet_aton(SourceIp);",$db) or die ("MySQL error: " . mysql_error());

echo "SELECT SourceIp, IF(COUNT(distinct Device)>1,'YES','') AS 'MULTIDEV', SUM(IF(MessageType='IDS',1,0)) AS 'IDS', SUM(IF(MessageType='NtwkACL',1,0)) AS 'ACL' FROM DredgeEvent GROUP BY SourceIp ORDER BY inet_aton(SourceIp);";


DataRows ($WidthOne, $WidthTwo, $WidthThree, $WidthFour, $WidthFive, $result, $db);

function HeaderRow ($WidthOne, $WidthTwo, $WidthThree, $WidthFour, $WidthFive, $Dent) {
printf("<DIV Class=\"row\">
<DIV Style=\"Width: $WidthOne;\" Class=\"leftcell$Dent\">#</DIV>
<DIV Style=\"Width: $WidthTwo;\" Class=\"leftcell$Dent\">Source IP</DIV>
<DIV Style=\"Width: $WidthThree;\" Class=\"centered$Dent\"># MULTIDEV</DIV>
<DIV Style=\"Width: $WidthFour;\" Class=\"centered$Dent\"># IDS</DIV>
<DIV Style=\"Width: $WidthFive;\" Class=\"centered$Dent\"># ACL</DIV><DIV>\n");
}

function DataRows ($WidthOne, $WidthTwo, $WidthThree, $WidthFour, $WidthFive, $result, $db) {
$rows = mysql_num_rows($result);
printf("ROWS = $rows\n");
while ($myrow = mysql_fetch_array($result)) {
$i++;
if ($i%2==0) {
$dent='out';
} else {
$dent='in';
}
printf("\n\t\t<DIV Class=\"row\">
<DIV Style=\"Width: $WidthOne;\" Class=\"leftcell$dent\">$i</DIV>
<DIV Style=\"Width: $WidthTwo;\" Class=\"leftcell$dent\">%s</DIV>
<DIV Style=\"Width: $WidthThree;\" Class=\"centered$dent\">%s</DIV>
<DIV Style=\"Width: $WidthFour;\" Class=\"midcell$dent\">%s</DIV>
<DIV Style=\"Width: $WidthFive;\" Class=\"rightcell$dent\">%s</DIV>\n",
$myrow["SourceIp"], $myrow["MULTIDEV"], $myrow["IDS"], $myrow["ACL"]);
}
}

?>

 

[ericbrunson]

I don't use the mysql_query directly, myself, but is the semicolon at the end of the query messing it up?

 

[Olavxxx]

try:
echo mysql_num_rows($result);

you can also try mysql_db_query().

I have the $db before the $query inside the function to run the query.

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[billyjeffjoe]

OK, so I tried the suggestions (thanks!) but no luck. So I cut the script down to this:
--------------------------
<?php

$result = mysql_query("SELECT SourceIp FROM DredgeEvent GROUP BY SourceIp ORDER BY inet_aton(SourceIP);",$db);

while ($myrow = mysql_fetch_array($result)) {
printf("\n\t\t<DIV Class=\"row\"><DIV Style=\"Width: 120px;\" Class=\"rightcellout\">%s</DIV>\n",
$myrow["SourceIp"]);
}
?>
--------------------------------

Still, no dice. I never use rebooting or blowing away a database as a means of troubleshooting, but I am just about ready to...

-Matt

 

[billyjeffjoe]

OK. If I put the db initialization in this script and go straight to it (instead of using it as an include) I get the results I expect.

------------------------
<?php

$db = mysql_connect('localhost', '****', '****');
mysql_select_db('****',$db);


$result = mysql_query("SELECT SourceIp FROM DredgeEvent GROUP BY SourceIp ORDER BY inet_aton(SourceIP);",$db);

while ($myrow = mysql_fetch_array($result)) {
printf("\n\t\t<DIV Class=\"row\"><DIV Style=\"Width: 120px;\" Class=\"rightcellout\">%s</DIV>\n",
$myrow["SourceIp"]);
}

?>
----------------------------

Even with the db initialization in the script, if I use it as an include it doesn't work. I am not sure how to account for that. The include is pretty simple. I have one document (index.php) that is a shell for a couple of others, including a header, a menu, and this. If I include another script instead of this one that queries the same database, it works fine.