PHP/MySQL Query returns one less result than expected

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Razbojnik
Forum Newbie
Posts: 5
Joined: Thu Oct 07, 2010 11:42 am

PHP/MySQL Query returns one less result than expected

Post by Razbojnik »

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:

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 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!
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: PHP/MySQL Query returns one less result than expected

Post by Darhazer »

You are fetching the first row right after executing the questy

Code: Select all

$result = mysql_query($query);
$row = mysql_fetch_array($result); // <-- here the first row is fetched - this line have to be removed
$num_results = mysql_num_rows($result);
Razbojnik
Forum Newbie
Posts: 5
Joined: Thu Oct 07, 2010 11:42 am

Re: PHP/MySQL Query returns one less result than expected

Post by Razbojnik »

That solved it! Thanks!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: PHP/MySQL Query returns one less result than expected

Post by VladSun »

There are some issues and possible improvements in your code, Razbojnik ( Is it Разбойник? :) )
First, *always* use mysql_real_escape_string() for every user input used in an SQL query - i.e. don't use unescaped $_POST input in your code.

It's better to use mysql_fetch_assoc(). Using mysql_fetch_array() without the second argument is the worst one to use from the mysql_fetch_* function family.

Instead of using this:

Code: Select all

$result = mysql_query($query);
$num_results = mysql_num_rows($result);

for ($s=0; $s <$num_results; $s++)
{
$row = mysql_fetch_array($result);
it's better to use this:

Code: Select all

if ( ($result = mysql_query($query)) )
{
	while ( ($row = mysql_fetch_assoc($result)) )
	{
Use aliases in you SQL.
Instead of:

Code: Select all

timediff(pi.end_downtime, pi.start_downtime),
use

Code: Select all

timediff(pi.end_downtime, pi.start_downtime) as downtime_period,
then you access it in PHP like this:

Code: Select all

"<td align=\"left\" valign=\"top\"><p>".$row["downtime_period"]."</td>"
Close your <p> tags everywhere.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply