why is this Query creating seemingly random results?

Question

This makes no sense to me, but this query display some records in full some in partial and some in a single line. With seemingly no rhythm or reason. Even worse when I run the query in MySql it works just fine with no issues at all. Currently I am thinking it is a demon and/or super demon.

$sql =  "SELECT phonenumber,date,dataplan AS currentplan, SUM(datamb) AS value_sum FROM maindata2 WHERE email='".($_POST['email'])."' GROUP BY phonenumber,date";

$result = mysql_query($sql);

$row = mysql_fetch_assoc($result);

$query = mysql_query($sql) or die(mysql_error());

$header_printed = false;

while($row = mysql_fetch_array($query)) {
    if ($row['phonenumber']) {
        if ($header_printed === false) {

            echo "   
            <table id='display'>
            <tr>
            <th>Phone Number:</th>
            <th>Data Plan:</th>
            <th>Data Usage This Period:</th>
            <th>Remaining:</th>
            <th>Date Reporting:</th>
            </tr>";

            $header_printed = true;
        }
    }
} 

while ($row = mysql_fetch_assoc($result)){
    echo "<tr>";
    echo "<td>".$row['phonenumber'] . "</td> ";
    echo "<td>".$row['currentplan'] . "MB</td> ";
    echo "<td>".ROUND ($row["value_sum"],2) . "MB</td> ";
    echo "<td>".($row['currentplan'] - ROUND ($row["value_sum"],2)) . "MB</td>";
    echo "<td>".$row['date'] . "</td></tr>";
}
echo "</table>";

Any thoughts would be one more than I have right now!

Are the data well-formed in your database? No stray newlines at the end of fields? Can you check with phpMyAdmin? — John, Oct 11 '13 at 18:15
Can you provide a structure sample of the table so we can test and see what exactly is the problem? Also, you're creating a tag inside your loop. Generally you want create the table tag before the loop. — Jesse, Oct 11 '13 at 18:16
Why are you querying the database with the same query twice and making two result sets? Why do you have this line `$row = mysql_fetch_assoc($result);` and do nothing with it (it will end up just advancing your result set pointer)? — Mike Brant, Oct 11 '13 at 18:16
**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Oct 11 '13 at 18:21
@MikeBrant That was it! I didn't need it, and it was causing an issue! Thank you! — user2848080, Oct 11 '13 at 18:22

Labib Ismaiel · Answer 1 · 2013-10-11T18:24:44.270

0

just notice that the is inside the loop which is resulting in the creation of multiple tables, this could be a reason, another note, why are you making the same query twice, that's a useless over head on the db engine ?! and a last note, NEVER EVER use the user input directly in your query without cleaning.

edited Oct 11 '13 at 18:24

answered Oct 11 '13 at 18:23

Labib Ismaiel

1,240
2
11
21

score 0 · Answer 2 · answered Oct 14 '13 at 16:39

Your problem seems to be that you are running the same query twice. On this line:

$row = mysql_fetch_assoc($result);

You fetch the first result row of the result set, yet you don't actually do anything with it at all.

You mix your usage of $query and $result as well.

You likely need to output your table header before you loop through the result set. Then loop through the result set once, outputting each row.

why is this Query creating seemingly random results?

2 Answers2