user3080562 user3080562 - 3 months ago 19
SQL Question

PHP SQL Returning only one result

Im trying to migrate my website from my local wamp project to my live server for testing, everything is working fine on my local site but on my live site the following code only returns one result. There are definitely multiple SQL entries that meet the sql query criteria. Any suggestions?

$opentickets = $db->query("SELECT tID, id, date, userid, category, department FROM ticket WHERE userid = '$_SESSION[id]' AND status='Open'");

if(count($opentickets) > 0) {
echo "<h2>Your open tickets:</h2>";
echo "<table class='table table-striped'>
<tr>
<th>Ticket</th>
<th>Date Submitted</th>
<th>Category</th>
<th>Department</th></tr>";
$o = $opentickets->fetch(PDO::FETCH_ASSOC);
echo "<tr><td><a href='ticket.php?id=" . $o['tID'] . "'>" . $o['tID'] . </td>";
echo "<td>" . $o['date'] . "</td>";
echo "<td>" . $o['category'] . "</td>";
echo "<td>" . $o['department'] . "</td></tr>";
echo "</table>";
}
else
{
echo "<h2>You have no open tickets</h2>";
}

Answer

You are not itterating over the results of your query, you are just fetching the first row...

Please reference to the examples on the PHP Website on how to do this.

I have just changed your code below, please check for the manuals for more information

while($o = $opentickets->fetch(PDO::FETCH_ASSOC)){
        echo "<tr><td><a href='ticket.php?id=" . $o['tID'] . "'>" .$o['tID'] . </td>";
        echo "<td>" . $o['date'] . "</td>";
        echo "<td>" . $o['category'] . "</td>";
        echo "<td>" . $o['department'] . "</td></tr>";
}