CODEWITHSUNDEEP
phpmysql
Gopipuli
Gopipuli

Reputation: 393

mysql query giving incorrect result in for loop

The code belows gives me only 18 records

<?php

$result4 = mysql_query("select Distinct Country  from trendsmtable where WHORegionAC='Europe - all countries' GROUP BY Country ");

echo "<table width=880 align=center>";
echo "<tr><td colspan=4 style='font-family:Arial;'><b>European Region</b></td></tr>";
$num_columns4 = 4;
$num_rows4 = mysql_num_rows($result4);
$i=0;
while($row4 = mysql_fetch_assoc($result4)){
    $results[$i] = $row4['Country'];
    $i++;
}
unset($i);
$k=0;
for ($i=0;$i<=($num_rows4/($num_columns4+1));$i++){
    echo '<tr>';

    for($j=1;$j<=$num_columns4;$j++){
        echo "<td width=220 style='font-family:Arial; font-size:12px'>".$results[$k].'</td>';
        $k++;
    }

    echo '</tr>';
$k++;
}
echo "</table>";

?>

while the select statement

select Distinct Country from trendsmtable where WHORegionAC='Europe - all countries'

returns 22 rows while I execute it in mysql which is correct!

Please help me to found the error.

Upvotes: 0

Views: 121

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

I think the PHP looks a little odd, it looks like you are looping from 0 to (22 / 5) then in the inner loop 4 times.

The group by should not make any difference as you are doing a select distinct

Upvotes: 0

cwallenpoole
cwallenpoole

Reputation: 81988

Well, you have an extra $k++ in there that you don't need:

        $k++; // keep this one
    }

    echo '</tr>';
    $k++; // remove this one
}

Edit

I've gone through your code and I've made some edits. Hopefully they'll clean your issue along the way:

// got rid of group by. With a select distinct, it isn't necessary
$result4 = mysql_query("select Distinct Country  from trendsmtable where ".
                       "WHORegionAC='Europe - all countries'");

// a good practice is to always double-quote your HTML attributes.
echo "<table width=\"880\" align=\"center\">";
echo "<tr><td colspan=4 style=\"font-family:Arial;\">".
          "<b>European Region</b></td></tr>";

$num_columns4 = 4; // where does 4 come from.
// $results was never initialized before.
$results = array();
while($row4 = mysql_fetch_assoc($result4)){
    // This is generally thought of as "cleaner" than using an index. 
    // And since there cannot be more than, say, 220 rows, cleanliness
    // should be a high-priority standard.
    $results[] = $row4['Country'];
}

// mysql_num_rows rarely provides any major benefit at all.
$num_rows4 = count($results);

foreach( $results as $key => $val )
{
    // This happens every time we fill all columns and need a new row.
    if( !( $key % $num_columns4 ) ) 
    {
        // makes it so that after the first time this closes the prev 
        // row before starting a new one.
        if( $key ) echo '</tr>'; 
        echo '<tr>';
    }
    // insert nag about CSS
    echo '<td width="220" style="font-family:Arial; font-size:12px">'.
              $val.
         '</td>';
}
echo '</tr></table>';

Upvotes: 3

nfechner
nfechner

Reputation: 17525

Your SQL code in the PHP file contains a GROUP BY clause, which can reduce the number of rows returned, if you have a country more than once in the db.

Upvotes: -1

Related Questions