MySQL fetch array not collecting and returning all the rows | PHP | MySQL

Question

I have a function (see below)

public function ReadBets_Open($id) {
    $queryBase = "SELECT * FROM `vr_wp_bets` WHERE `is_open` = 'true' AND `id` = '%s';";
    $queryBase2 = sprintf($queryBase, $id);
    $selectQuery = mysql_query($queryBase2);
    $return = "<div style='max-height: 400px; overflow: scroll;'>";
    while ($result = mysql_fetch_array($selectQuery)) {
        //var_dump($result);
        $return = "<div style='border: 1pt solid black; width: 99%;'>";
        $return .= "<h2>" . $result['title'] . "</h2>";
        $return .= "<table>";

        $return .= "<tr><td style='width:50%;'>Sport: </td><td>" . $result['sport'] . "</td></tr>";
        $return .= "<tr><td style='width:50%'>Participant: </td><td>" . $result['participant'] . "</td></tr>";
        $return .= "<tr><td>Market: </td><td>" . $result['market'] . "</td></tr>";
        $return .= "<tr><td>Time: </td><td>" . date("H:i", strtotime($result['bettilltime'])) . "</td></tr>";
        $return .= "<tr><td>Odds: </td><td>" . $result['odds'] . "</td></tr>";
        $return .= "<tr><td>Stake: </td><td>&pound;" . $result['stake'] . "</td></tr>";

        if ($result['is_ew'] == "true") {
            $return .= "<tr><td>Each Way: </td><td>" . $this->CalculateEachWay($result['odds'], $result['ew_odds']) . "</td></tr>";
            $return .= "<tr><td>Estimated Return:</td><td>" . "N/A" ."</td></tr>";
        }
        else if ($result['odds'] == "SP") {
            $return .= "<tr><td>Estimated Return:</td><td>" . "N/A" ."</td></tr>";
        }
        else {
            $return .= "<tr><td>Estimated Return:</td><td>&pound;" . $result['estimated_return'] ."</td></tr>";
        }

        $return .= "</table>";
        $return .= "</div><br>";
    }
    $return .= "</div>";
    return $return;
}

And I know for a fact that there is 4 results in this that should be selected, however only one is returned by the fetch array, I was wondering if anyone can see an issue with this and if so what is it? This has had me stumped for a few days now and I really need an answer to this.

The way that I call the method is:

$classInstance->ReadBets_Open($current_user->ID);

NOTE
I am aware that I am using depreciated mysql_* functionality, this will be changed in the future, this needs to be made and released.

You are resetting `$return` each time you loop. So by the end of the loop `$return` will only have the last row's details. — Vex, May 08 '15 at 13:55
@Vex, such a stupid mistake on my part! Would you be happy to put that as an asnwer for me and I will mark it as correct as soon as I can? — Can O' Spam, May 08 '15 at 13:56
Sure. No problem. It's an easy mistake to make though. Happens to the best of us! — Vex, May 08 '15 at 13:58
wouldn't it make sense using `mysql_fetch_assoc()` and not bother about concatenating and simply wrap those in an echo? — Funk Forty Niner, May 08 '15 at 14:00
I know you're aware, but please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 08 '15 at 14:27
For now, the system needs to be live, I am aware of the depreciation of mysql_* functions, however due a restricted time frame at the minute it is not viable to change from mysql to mysqli. — Can O' Spam, May 08 '15 at 14:58

score 2 · Accepted Answer · answered May 08 '15 at 13:57

2

You are resetting $return each time you loop. So by the end of the loop $return will only have the last row's details.

answered May 08 '15 at 13:57

Vex

1,489
13
20

MySQL fetch array not collecting and returning all the rows | PHP | MySQL

1 Answers1