Vinny Gullotta wrote:
What I want to do is find the top 10 servers where the column steps =
iisreset. The following code works great except that the page is not
displaying the servername in the 'Server Name' column of my results
(nothing appears, the column is just blank).
servername and steps are the important columns in the database table.
$_POST[time1] and $_POST[time2] come from a form submitted.
When I copy and paste the entire select statement into the SQL tab in
phpmyadmin (and replace the time variables with actual times
corresponding to the timestamp column), it displays the correct results
including servername. Everything works in the php page's results except
for the servername. I feel like it's right in front of my face and
that's why I can't see it lol. Any help would be greatly appreciated.
Thanks in advance =)
My code...
$query = "SELECT servername, COUNT(steps) FROM monitoring WHERE steps
LIKE 'iisreset' AND timestamp <= '$_POST[time2]' AND timestamp >=
'$_POST[time1]' GROUP BY servername ORDER BY COUNT(*) DESC LIMIT 10";
$result = mysql_query($query) or die(mysql_error());
# display column titles
echo "<center><table class='table'><tr>";
echo "<td class='tableHeader'><center><small><b>Count</b></small></td>";
echo "<td class='tableHeader'><center><small><b>Server
Name</b></small></td>";
echo "</tr>";
#display results
while($i = mysql_fetch_row($result))
{
echo "<tr><td><small><center>", $i[COUNT('steps')],
"</center></small></td>";
echo "<td><small><center>", $i[servername] ,"</center></small></td></tr>";
}
echo "</table></center><br>";
just a few little notes.. because I'm like this today
(none of it is a you must do, or meant badly, just aiming to save you
some time by passing on a few *things*)
1: SQL
in mysql queries /should/ use backticks (`) around database, table and
column names, stop's them getting confused with variables or reserved
words (like timestamp) and saves you future trouble :)
further, you'll be needing to use AS to turn COUNT(steps) into a nice
name like "stepcount"
so..
$query = 'SELECT `servername`, COUNT(`steps`) AS stepcount FROM
`monitoring` WHERE `steps` LIKE "iisreset" AND `timestamp` <= ' .
$_POST[time2] . ' AND `timestamp` >= ' . $_POST[time1] . ' GROUP BY
`servername` ORDER BY COUNT(*) DESC LIMIT 10';
2: you should be cleaning those posts before you add them to a mysql
query; this has been covered many times so I won't repost it
(mysql_real_escape() or sprintf or.. many different methods]
3: Needless multiple echo's; one will suffice just fine and show it up
in your editor as a nice easily visible block of html :)
echo '<center><table class="table"><tr>
<td class="tableHeader"><center><small><b>Count</b></small></td>
<td class="tableHeader"><center><small><b>Server Name</b></small></td>
</tr>';
4: valid xhtml; in my opinion there's no excuse now; it's been years
since it came out (and you're already using css); this will do the same
as above:
echo '<table class="center">
<tr>
<th>Count</th>
<th>Server Name</th>
</tr>';
[css to center the table would be]
table.center {
margin: 0 auto;
}
5: mysql_fetch_row() returns a numerical indexed array.. not associative
thus:
$i = mysql_fetch_row($result)
print_r($i);
will show..
$i[0] => the server name
$i[1] => stepcount value
you'll be needing
$i = mysql_fetch_assoc($result);
print_r($i);
which will show
$i['servername'] => the server name
$i['stepcount'] => stepcount value
6: " vs '
when you use " php will parse the enclosed string for variables, when
you use ' it won't; so ' leads for faster code, and also encourages you
to code strongly by closing strings and concatenating variables.
Further it allows you to use valid html " around attributes rather than
the invalid '
7: , vs .
there is no vs :) to concatenate we use . (period) not , (comma)
so for 6 & 7..
echo '<td>' . $i['servername'] . '</td>';
I'm going to stop there, hope it helps a little bit; and I won't go any
further as half the fun is learning; so you finding out how to save time
on queries and write your own db handlers etc is not my domain I reckons
Regards
nathan
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
