PHP/MySQL Query returns one less result than expected
Posted: Wed Mar 09, 2011 4:03 am
Hello, all!
I am running into a problem with my query returning one less result than expected.
To get started, I am using WAMP Server running on localhost. System versions are:
Windows XP
Apache: 2.2.1
MySQL: 5.1.36
PHP: 5.3.0
Here's my code:
I have an option where the user can select a single printer, or "all." If choosing "all" when I run the code in a PHP page, it returns five results, but when I run the generated query in PHPMyAdmin, it returns six results (the number of entries in the database.) When I choose a single printer, I get zero results to display in the PHP page, but against the MySQL database in PHPMyAdmin, I see the expected one result or two results for each printer. It seems to "drop" the first result in the PHP page.
I've looked online, and there seems to be an updated version ("mysqli," or "MySQL Improved") of mysqli_query, mysqli_fetch_array and mysqli_num_rows, which they recommend using. But this seems a clunky and not a little bit illogical: After all, why does it work in one place, and not in another? Also, I have NEVER had problems with this before in more than 10 years of using PHP and MySQL, so why now, all of a sudden?
What was working yesterday doesn't work today, and what works in one page doesn't work in another. There is no logical reason for either situation, and no desire on my part to implement a "new fix" when it should be possible to make the old work via sound code and syntax. I'm not Alice, and this ain't Wonderland, so no rabbit holes are in play here. Anybody got any ideas why this is so? And possible solutions or the ability to point out errors in my code?
Thanks in advance for your help!
I am running into a problem with my query returning one less result than expected.
To get started, I am using WAMP Server running on localhost. System versions are:
Windows XP
Apache: 2.2.1
MySQL: 5.1.36
PHP: 5.3.0
Here's my code:
Code: Select all
<?php
//connection.php connects to database
include("connection.php");
$query = "select pl.printer_name,
pc.component_name,
cp.part_name,
pi.start_downtime,
pi.end_downtime,
timediff(pi.end_downtime, pi.start_downtime),
pi.issue_description,
pi.tech_called,
pi.issue_resolution
from printer_issues pi
inner join printer_lines pl on pl.line_id = pi.line_id
inner join printer_components pc on pc.component_id = pi.component_id
inner join component_parts cp on cp.part_id = pi.part_id
where (start_downtime >= '".$_POST["start_report"]."' or start_downtime <= '".$_POST["end_report"]."')
and end_downtime not like '%00:00:00'
order by pl.printer_name, pc.component_name, cp.part_name";
<table width=\"100%\" cellpadding=\"2\" cellspacing=\"2\" border=\"0\">
<tr>
<td align=\"left\" valign=\"top\"><p>Printer</td>
<td align=\"left\" valign=\"top\"><p>Component</td>
<td align=\"left\" valign=\"top\"><p>Part</td>
<td align=\"left\" valign=\"top\"><p>Start Downtime</td>
<td align=\"left\" valign=\"top\"><p>End Downtime</td>
<td align=\"left\" valign=\"top\"><p>Downtime (HH:MM:SS)</td>
<td align=\"left\" valign=\"top\"><p>Issue Description</td>
<td align=\"left\" valign=\"top\"><p>Tech?</td>
<td align=\"left\" valign=\"top\"><p>Resolution</td>
</tr>";
//query and results here
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$num_results = mysql_num_rows($result);
for ($s=0; $s <$num_results; $s++)
{
$row = mysql_fetch_array($result);
echo "<tr>
<td align=\"left\" valign=\"top\"><p>".$row["printer_name"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["component_name"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["part_name"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["start_downtime"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["end_downtime"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["timediff(pi.end_downtime, pi.start_downtime)"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["issue_description"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["tech_called"]."</td>
<td align=\"left\" valign=\"top\"><p>".$row["issue_resolution"]."</td>
</tr>";
}
echo "</table>";
?>
I've looked online, and there seems to be an updated version ("mysqli," or "MySQL Improved") of mysqli_query, mysqli_fetch_array and mysqli_num_rows, which they recommend using. But this seems a clunky and not a little bit illogical: After all, why does it work in one place, and not in another? Also, I have NEVER had problems with this before in more than 10 years of using PHP and MySQL, so why now, all of a sudden?
What was working yesterday doesn't work today, and what works in one page doesn't work in another. There is no logical reason for either situation, and no desire on my part to implement a "new fix" when it should be possible to make the old work via sound code and syntax. I'm not Alice, and this ain't Wonderland, so no rabbit holes are in play here. Anybody got any ideas why this is so? And possible solutions or the ability to point out errors in my code?
Thanks in advance for your help!