Ryan Smith Ryan Smith - 4 months ago 6
PHP Question

MySQL returning only one row (most recent one)

I've got an issue, basically I'm making a ticket system just for fun and I'm running into a few problems, most of which I've been able to fix with a quick google search or just by messing around with it for a little bit.

I'm not able to solve this issue though, basically when you click on your specific ticket you have open it brings you to a link like that looks like this:
158.xx.xxx.xxx/site/support?view=ID (35, 36, 37). When viewing the page it does display the ticket information but the same information on all three tickets.

$stmt3 = $auth_user->runQuery("SELECT * FROM ticket"); $stmt3->execute();

if(isset($_GET['view'])){
if($stmt3->rowCount()){
while($r = $stmt3->fetch(PDO::FETCH_OBJ)) {
$name = $r->name;
$id3 = $r->id;
$subject = $r->subject;
$ticket = $r->ticket_date;
$desc = $r->body;
$ticid = $r->ticket_id;
}
}

if($_GET['view'] == $id3){

echo
'
<div class="ticket">
<div class="ticket-date">
'.$ticket.'
</div>
<div class="ticket-name">
'.$name.'
</div>
<div class="ticket-desc">
'.$desc.'
</div>
</div>
';

$displayticket = $auth_user->runQuery("SELECT * FROM ticket_replies WHERE ticket_id=:ticid");
$displayticket->execute(array(':ticid'=>$user_id));
$ticketsrow = $displayticket->fetchAll();
$count = count($ticketsrow);
foreach($ticketsrow as $row9){
echo
"
<br />
<div class='ticket'>
<div class='ticket-date'>
".$row9['timestamp']."
</div>
<div class='ticket-name'>
".$row9['uid']."
</div>
<div class='ticket-desc'>
".$row9['text']."
</div>
</div>

<br />";
}

echo '
<form method="POST" action="support?view='.$id3.'">
<textarea id="text" name="addsupportbody"></textarea><br/>
<input type="submit" name="addsupportcomment" class="btn btn-dark" style="margin-top: 5px;" value="Add Comment">
<input type="submit" name="closeticket" class="btn btn-danger" value="Close Ticket">
</form>';

if(isset($_POST['addsupportcomment'])){
$ticketid = $id3;
$uidc = $user_id;
$ttext = $_POST['addsupportbody'];

if($ttext == ""){
echo "You must enter a comment to send.";
}else{

try
{

if($auth_user->insertTicketComment($ticketid, $uidc, $ttext)){
echo "Your comment has been added!";
header("url=index");
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}

}
}

}else{
echo "This page does not exist.";
} }


Speak with what you will, I do not consider this code safe for publicizing or use, nor do I consider it good or organized. I'm simply trying to learn from my own mistakes and hopefully receive a little help on the way. If there is some missing code just let me know and I'll include it. This block of code is what isn't working correctly for me.

Answer

Select just the ticket you want to display. Change

$stmt3 = $auth_user->runQuery("SELECT * FROM ticket"); 

To

$id = isset($_GET['view'])? (int)$_GET['view']: -1;
$stmt3 = $auth_user->runQuery("SELECT * FROM ticket WHERE id = $id"); 

The problem with your code is that you're selecting all the tickets, then you're looping through them:

while($r = $stmt3->fetch(PDO::FETCH_OBJ))

In each iteration of the loop, you capture the data in the row. No matter what ticket the user wants, the variables in the loop always end up with the last row's values. Instead, you should only fetch the ticket you're interested in as I did earlier. If a match is found, that's the ticket! No need to loop.