CODEWITHSUNDEEP
phpmysqlsql
Frankish EQG
Frankish EQG

Reputation: 114

PHP & MySQL Query results are confusing

Working on a quick script to display recent activity of a customer in my database - I have the script outputting results but what it's out putting is confusing me.

            <?php
                //Search for customer recent history
                $q = "SELECT * FROM txn_log WHERE customer_no = $customer_no ORDER BY datetime DESC LIMIT 3";
                $r = mysql_query($q) or die(mysql_error());

                while($row = mySQL_fetch_array($r)) {
                    $recent_history = '';

                    $str .= '<a href="#" class="list-group-item">';
                    $str .= '   <span class="badge">' . gmdate("Y-m-d\TH:i:s\Z", $row['datetime']) . '</span>';
                    $str .= '   <i class="fa fa-check"></i> ' . $row['txn_id'] . ': ' .  $row['txn_type'] . ' ' . $row['amount_dif'];
                    $str .= '</a>';

                    echo $str;
                }

            ?>

In my database I have one test customer with three records associated with them, starting at ID 2.

My query above SHOULD be outputting only the three records in order of the UNIX timestamp used, which should produce the three records in the following order => ID2, ID3, ID4

What it IS doing is outputting the following => ID2, ID2, ID3, ID2, ID3, ID4

I don't understand what I did wrong to get it to produce the first three (ID2, ID2, ID3) in addition to the correct results. Each time I run the query, the results are the same.

Upvotes: 0

Views: 59

Answers (4)

Frankish EQG
Frankish EQG

Reputation: 114

Woops... found it..

Derp in my While()

I was clearing the wrong string! $recent_history = ''; should have been $str = '';

EDIT:

Thanks all, I didn't see you guys posting until I submitted my correction. I'm definitely going to be securing the script, I'm just making sure that i can do what I want it to do :)

Upvotes: 0

EGN
EGN

Reputation: 2592

You need to use mysql_fetch_assoc, and echo outside the loop

Few things to keep in mind,

security has to be your top priory, check for SQL injection characters and always enclose your variables with single quotes in the query. see below

    <?php
        //Search for customer recent history
        $customer_no = str_replace("'", '', $customer_no);
        $q = "SELECT * FROM txn_log WHERE customer_no = '$customer_no' ORDER BY datetime DESC LIMIT 3";
        $r = mysql_query($q) or die(mysql_error());

        while($row = mySQL_fetch_assoc($r)) {
            $recent_history = '';

            $str .= '<a href="#" class="list-group-item">';
            $str .= '   <span class="badge">' . gmdate("Y-m-d\TH:i:s\Z", $row['datetime']) . '</span>';
            $str .= '   <i class="fa fa-check"></i> ' . $row['txn_id'] . ': ' .  $row['txn_type'] . ' ' . $row['amount_dif'];
            $str .= '</a>';
        }
            echo $str;
    ?>

Upvotes: 1

nempnett
nempnett

Reputation: 225

Reset $str for each loop iteration. Add:

$str="";

After $recent_history = '';

Upvotes: 1

Alf
Alf

Reputation: 852

You are using $str.=

so on the first iteration of your loop, the $str being echoed is ID2 second iteration, $str being echoed is ID2 + ID3 third iteration, $str being echoed is ID2 + ID3 + ID4

In the end, it will look like ID2, ID2, ID3, ID2, ID3, ID4 Hope that helps!

To Fix this, try declaring the variable as $str = "" at the start of your loop, and then your echo will only echo each ID once.

Upvotes: 1

Related Questions