PDO echoing data from two tables

[jackholden112]

This may seem like a duplicate but I have clicked on all the ones and tried it but it won’t fix it so I thought id post my code and ask for help!

I’m trying to echo data into a table and so far the only ones that work are the replies and subject and I can’t figure out why the others won’t work and as stated above I have tried all PDO posts but none of them have fixed it so please can you help because I’m relatively new to PDO so want to be able to see where I went wrong.

What i’m expecting it to do is how the subject of the ticket, the date, the publisher and whether its active but only the replies and subject show so i want to know why because its confusing me.

   <?php
				
	/* Ticket Info */
	$stmt = $dbh->prepare("SELECT * FROM support_tickets WHERE username = :username ORDER BY id DESC");
	$stmt->bindParam(':username', $userName);
	$stmt->execute();
	$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);
						
	foreach ($tickets as $myticket)
	{
		$ticket_id = $myticket['id'];
		$stmt = $dbh->prepare("SELECT * FROM support_messages WHERE on_ticket = :on_ticket");
		$stmt->bindParam(':on_ticket', $ticket_id);
		$stmt->execute();
		$getReplies = $stmt->fetchAll(PDO::FETCH_ASSOC);
						
		$replyNo = count($getReplies);
		$simpleHash = sha1($ticket_id . $companyName);
							
		echo '<tr>';
		echo '<td><a href="index.php?t=' . base64_encode($myticket['id']) . '&h=' . $simpleHash . '">' . $myticket['subject'] . '</a></td><td>' . $myTicket[0]['date'] . '</td><td>' . $tickets[0]['from_name'] . '</td> <td>' . $replyNo . '</td> <td>' . $myTicket['status'] . '</td>';
		echo '</tr>';
	}

?>

[ahundiak]

echo 
$myticket['id']
$myticket['subject'] 
$myTicket[0]['date']
$tickets[0]['from_name'] 
$replyNo 
$myTicket['status']

See any problems?

Treating $myTicket as an array is probably not what you want.

$tickets is an array though it is not clear why you are using it as such.

Be consistent: $myticket vs $myTicket

Add error_reporting(E_ALL) to detect some of these problems. And get yourself an IDE with syntax highlighting.

[droopsnoot]

If the only thing you do with the results of the second query is use it to create the number of replies in $replyNo, you might be better doing something like

$stmt = $dbh->prepare("SELECT count(*) FROM support_messages WHERE on_ticket = :on_ticket");
$stmt->bindParam(':on_ticket', $ticket_id);
$stmt->execute();
$replyNo = $stmt->fetchColumn();

There may be a way you can combine the two into a single query but I can’t quite think of the syntax. Rather than making the database retrieve all matching rows just to see how many there are, let the database do the count for you.

ETA: Seems like you might be able to do something like this:

$stmt = $dbh->prepare("SELECT st.*, (select count(*) from support_messages where support_messages.on_ticket = st.id) as ticketcount FROM support_tickets as st WHERE username = :username ORDER BY id DESC");
$stmt->bindParam(':username', $userName);
$stmt->execute();

The count for each support_message row should be in ‘ticketcount’, obviously use something else if you have a column of that name.

[colshrapnel]

@droopsnoot

I can’t quite think of the syntax.

Here you go:

SELECT t.*, count(m.id)  as ticketcount FROM support_tickets t, support_messages m 
WHERE on_ticket = t.id AND username = :username 
GROUP BY t.id
ORDER BY t.id DESC

@jackholden112

I’m trying to echo data into a table

both your SQL and PDO are awfully inefficient. Just remember the rune: never ever select the data only to count it. Instead, ask a database to count it for you.
And of course you should use a JOIN instead of running a query in a loop.
Neither you should ever out put a single HTML tag while getting the data from database.
There are also some tricks you could use to make your code more tidy.

So your code should be

$sql = "
SELECT t.*, count(m.id)  as ticketcount, sha(concat(t.id,companyName)) simpleHash
FROM support_tickets t, support_messages m 
WHERE on_ticket = t.id AND username = ? 
GROUP BY t.id
ORDER BY t.id DESC";
$stmt = $dbh->prepare($sql);
$stmt->execute([$userName]);
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);