No results are returned to the table

[jasper1106]

Hi guys,

 

I am working with an old script at the moment, there is one page which just will not populate the table results.

I have tried running multiple debugging commands but the only one it flags is the line displaying

last;

saying it's not a used function.

If I comment out this line, no errors are produced but the results do not enter the table.

Can anyone shed some light on this please, I've spent hours and hours and banging my head against a brick wall would probably be more constructive right now.

 

Many thanks indeed for any help or advice.

 

<?php

mysql_connect("localhost", "$db_user","$db_upwd") or die ("Error - Could not connect: " . mysql_error()); 
mysql_select_db("$database");

$query="select host,count(*) from badc_mis_prog group by host";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());	

$count=0;
$hosts=array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {	
    $html_hlname=$row[0];
    $html_hlname=preg_replace("/</","<",$html_hlname);
    $html_hlname=preg_replace("/>/",">",$html_hlname);
            array_push($hosts, $html_hlname,$row[1],0);
    $count++;
        }

$query="select host,count(*) from badc_mis_prog where reported=1 group by host";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());	
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {	
    $html_hlname=$row[0];
    $html_hlname=preg_replace("/</","<",$html_hlname);
    $html_hlname=preg_replace("/>/",">",$html_hlname);
            for ($i=0; $i<($count*3); $i+=3) {
        if ($hosts[$i] == $html_hlname) {
	    $hosts[($i+2)]=$row[1];
	   last;
	}
    }
        }

for ($i=0 ; $i<(($count-1)*3); $i+=3){
    for ($j=$i+3 ; $j<($count*3); $j+=3){
        if ($hosts[($i+1)] < $hosts[($j+1)]){
            $temp=array();
	    $temp[0]=$hosts[$i];
	    $temp[1]=$hosts[($i+1)];
	    $temp[2]=$hosts[($i+2)];
	    $hosts[$i]=$hosts[$j];
	    $hosts[($i+1)]=$hosts[($j+1)];
	    $hosts[($i+2)]=$hosts[($j+2)];
	    $hosts[$j]=$temp[0];
	    $hosts[($j+1)]=$temp[1];
	    $hosts[($j+2)]=$temp[2];
                }
    }
}
print "<br><br><br><center><table border=\"1\">\n";
print "<tr><td>Host Name</td><td>Hosted</td><td>Reported</td><td>Ratio H/R</td></tr>\n";

for ($i=0; $i<($count*3); $i+=3) {
   if ($hosts[($i+1)]<15){ break;}
           printf ("<tr><td>  %s  </td><td> %d </td><td> %d </td><td>%.1f %%</td></tr>\n",$hosts[$i],$hosts[($i+1)],$hosts[($i+2)],(($hosts[($i+2)]/$hosts[($i+1)])*100));
}

print "</table></center>\n";


?>

[jasper1106]

anyone any ideas or could advise how to debug this page?

 

[Psycho]

Yeah, start over. That code appears to be overly complex for what you are trying to achieve. I've read through it a few times and it hurts my head. I think you can probably get all the data you need with a single query using a JOIN of the table upon itself.  You have a table of records and you are using the host name and the count of host names along with whether some of the records have a 'reported' value of 1. After that it all becomes fuzzy due to the over complication.

 

Can you explain what you are trying to accomplish in plain English?

[jasper1106]

Hi mjdamato,

 

Well in someways I am glad someone else thinks this is a complete mess! I get half way through the script and my head explodes.

 

I am a beginner at PHP and trying to teach myself, I did attempt to rewrite this with a table JOIN but again I couldn't get the results to display.

 

In plain English this is what the script tries to do:

Connects to the table: badc_mis_prog

Counts the "host" column only if the reported column =1 and groups the result by the host name.

 

For example.

John 

John

Paul

Tim

Tim

 

Would display:  John - 2  |  Paul - 1  | Tim - 2

 

The rest of the script then works out percentages and report numbers.

This is the stuff I just cannot get to work, I am looking to display these results in a table like:

 

Host Name  |  Games Hosted (number of times reported=0 and 1) |  Games Reported (number of times reported=1 appears for each host) |  Ratio (percentage of successful reports, Games reported divided by Games hosted multiply by 100).

 

All these columns must relate to each Host name.

The database information is there, I am really struggling to put together the correct queries though.

 

Any help or advice would be greatly received. Thanks a lot.

 

 

 

[Psycho]

OK, now we are getting somewhere. Based uponn what I understand this looks like a simple solution - no JOIN needed. You want to see a row for each host with the total number of records, number of records where reported is 0, number of records where reported is 1 and then percentages based upon that.

 

Here's some modified code that is much simpler. By the way you should rely upon CSS/Styles for adjusting presentation as opposed to non-breaking spaces.

 

<?php
    
mysql_connect("localhost", "$db_user","$db_upwd") or die ("Error - Could not connect: " . mysql_error()); 
mysql_select_db("$database");
    
$query = "SELECT `host`, COUNT(`host`) as `total_hosted`, SUM(`reported`) as reported
          FROM `badc_mis_prog`
          GROUP BY `host`
          ORDER BY `host`";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());
    
//Create table output from results
$tableDate = '';
while($row = mysql_fetch_assoc($result))
{
    $name     = htmlspecialchars($row['host']);
    $ratio    = round($row['reported']/$row['total_hosted']*100);
    
    $tableDate .= "  <tr>\n";
    $tableDate .= "    <td>{$name}</td>\n";
    $tableDate .= "    <td>{$row['total_hosted']}</td>\n";
    $tableDate .= "    <td>{$row['reported']}</td>\n";
    $tableDate .= "    <td>{$ratio}%</td>\n";
    $tableDate .= "  </tr>\n";
}
    
?>
<html>
<head>
<style>
td { padding-right: 5px; padding-left: 5px; }
</style>
</head>
<body>
    
<br /><br /><br />
<table border="1" style="align:center;">
  <tr>
    <th>Host Name</th>
    <th>Hosted</th>
    <th>Reported</th>
    <th>Ratio H/R</th>
  </tr>
  <?php echo $tableData; ?>
</table>
    
</body>
</html>

[jasper1106]

Wow, thank you mjdamato, I really do appreciate your time.

I was over complicating it as well by the looks of your suggestion, I had joins and unions and all sorts of stuff.

 

I've tested your script, surprisingly it produces the same as the poorly written version. The table displays, no data is populated and no errors, script connects to the database and table ok... very strange indeed. Any ideas what could be causing this?

[Psycho]

If the table headers are displayed with no data, then the query is running but not returning any results. Have you checked that ther eis data in the table?

 

Try the following which will let you know if there are no results

<?php
    
mysql_connect("localhost", "$db_user","$db_upwd") or die ("Error - Could not connect: " . mysql_error()); 
mysql_select_db("$database");
    
$query = "SELECT `host`, COUNT(`host`) as `total_hosted`, SUM(`reported`) as reported
          FROM `badc_mis_prog`
          GROUP BY `host`
          ORDER BY `host`";
$result = mysql_query($query) or die ("Error - Query: $query" . mysql_error());
    
//Create table output from results
$tableData = '';
if(mysql_num_rows($results)==0)
{
    $tableData = "<tr><td colspan=\"4\">No results returned.</td></tr>\n";
}
else
{
    while($row = mysql_fetch_assoc($result))
    {
        $name     = htmlspecialchars($row['host']);
        $ratio    = round($row['reported']/$row['total_hosted']*100);
        
        $tableData .= "  <tr>\n";
        $tableData .= "    <td>{$name}</td>\n";
        $tableData .= "    <td>{$row['total_hosted']}</td>\n";
        $tableData .= "    <td>{$row['reported']}</td>\n";
        $tableData .= "    <td>{$ratio}%</td>\n";
        $tableData .= "  </tr>\n";
    }
}
?>
<html>
<head>
<style>
td { padding-right: 5px; padding-left: 5px; }
</style>
</head>
<body>
    
<br /><br /><br />
<table border="1" style="align:center;">
  <tr>
    <th>Host Name</th>
    <th>Hosted</th>
    <th>Reported</th>
    <th>Ratio H/R</th>
  </tr>
  <?php echo $tableData; ?>
</table>
    
</body>
</html>

[jasper1106]

Well... I am truely puzzled now. Thank you once more for your help.

 

You are correct in saying that no results are being returned. Double checked the connection details, table and column names are correct.

 

If I simply just echo your alias on the page the data is read.... so I guess this proves the connections are working?

 

echo "Host: ".$row['total_hosted'];

echo " Reported: ".$row['reported'];

 

[Pikachu2000]

Copy your query into phpMyAdmin, run it and see what, if any, results are returned.

[jasper1106]

Thanks for your input Pikachu.

The query executes and returns results through phpmyadmin.

[Psycho]

OK, so you are saying you can echo the values inside the loop, but when you create the table there is nothing there. Either:

 

1. There is an error in the code where the values are lost (I don't see anything in the code I provided, perhaps there was something when you implemented it?)

 

OR

 

2. The content is there, but there is a problem with the HTML that is causing invalid HTML causing a display problem and/or there are CSS/Style issus making the content "invisible". have you checked the HTML source code of the rendered page to see if the content is there?

[jasper1106]

Thank you for your continued help, this is basically the problem I've been battling with. It cannot be a conincidence that both scripts are failing at this point.

I have directly copied your scripting mjdamato the only thing I've added is an include file at the top of the script containing the database connection details.

 

I can confirm when echoing the values within the loop I get results.

 

When viewing the source of the page this is all that displays:

 

<html>
<head>
<style>
td { padding-right: 5px; padding-left: 5px; }
</style>
</head>
<body>
   
<br /><br /><br />
<table border="1" style="align:center;">
  <tr>
    <th>Host Name</th>
    <th>Hosted</th>

    <th>Reported</th>
    <th>Ratio H/R</th>
  </tr>
  </table>
   
</body>
</html>

[Pikachu2000]

There's a typo in this line if(mysql_num_rows($results)==0) $results should be $result, which makes me wonder what is going on. That should always evaluate to TRUE with that typo, and produce the "No results returned" error. But it doesn't seem to be doing that, unless you've already corrected it . . .

[jasper1106]

Pikachu thank you for your reply again.

The server did flag an error when I tested this earlier however I thought this was part of the debugging and the "No results returned" also displayed.

Noob error I apologize, been looking at this script way too long today. I have now corrected the variable.

 

The "debugging" version now pulls results from the database but mjdamato's original script does not populate the table.

Any ideas why this might be as I am really confused as to why this is.... it's a darn sight further than I've managed to get over the past few days, thank you so much for your help guys.