function myFunction($options = false){
$q = "
SELECT
*
FROM
".DB_PREFIX."ticket t
LEFT JOIN
".DB_PREFIX."ticket_c c
ON
c.ticketID = t.ticketID
WHERE t.requesterID = '".$ID."'
ORDER BY
c.commentDate DESC
";
$result = $this->query($q);
while($row = $this->fetch_assoc($result)){
$rows[] = $this->makeTicketRow($row);
}
return '
<table id="ticketTable">
<thead>
<tr>
<td> </td>
<td>TicketID</td>
<td>Category</td>
<td>Subject</td>
<td>Date Opened</td>
<td>Options</td>
</tr>
</thead>
<tbody>
'.@implode('', $rows).'
</tbody>
</table>
';
}
The problem I’m basically having is that all data is returned fine, but the ticketID as used to join the tables is only returned for the first row. All other data is perfect. I’m new to JOINing tables so I’m not sure if that’s where I’m going wrong?
Okay so the query is returning 3 rows of data - it’s just not showing you an ID for the other two rows (It IS however returning a title for each). This is good information to have.
Stupid question i’m sure, but… do those other two rows have ID #'s?
Not stupid at all, in fact you just gave me the answer! Thanks. The other 2 don’t have any links to the second table ticket_c. Now the problem I have is, the rows in ticket won’t have any data in ticket_c until somebody adds a comment.
Is there a was to still show the rows IDs regardless of the data in title_c, as that is only required if there is any comments? The alternative is to not have any links or IDs on that row until it has been answered and a comment added.
Not sure if this will work, but try this instead for your query:
$q = "
SELECT
t.ticketID,t.ticketStatus,t.ticketCategory,t.ticketDate
FROM
".DB_PREFIX."ticket t
LEFT JOIN
".DB_PREFIX."ticket_c c
ON
c.ticketID = t.ticketID
WHERE t.requesterID = '".$ID."'
ORDER BY
c.commentDate DESC
";
I want to say it should instead be…
$q = "
SELECT
t.ticketID,t.ticketStatus,t.ticketCategory,t.ticketDate
FROM
".DB_PREFIX."ticket t
LEFT JOIN
(SELECT ticketID,commentDate FROM ".DB_PREFIX."ticket_c GROUP BY ticketID ORDER BY commentDate DESC ) AS c
ON
c.ticketID = t.ticketID
WHERE t.requesterID = '".$ID."'
ORDER BY
c.commentDate DESC
";
Because you only want each ticket to show up ONCE, right?
the problem with “ticketId” only showing a value for the first row is easy to understand once you know what’s causing it
it is the dreaded, evil “select star” and you were warned not to use it
SELECT * will return all columns from all tables in the FROM clause
so the query result set actually has two columns called tickedId, the first one from the first table, t, and the second one from the second table, t_c
and since it’s a LEFT OUTER JOIN, the valule in the second one will often be NULL
i don’t do php, but i’m pretty sure that a php reference to a column called “ticketId” will reference the second one
solution: don’t use the dreaded, evil “select star” – specify only the columns you want, and if you have multiple columns with the same name that need to be returned, give them distinct column aliases
I never consider myself an expert, because I know there’s always so much more that I could learn. It is my goal to learn something every day of my life.
Off Topic:
A Pilot with 100 hours thinks he knows it all.
A Pilot with 1,000 hours knows he knows it all.
A Pilot with 10,000 hours knows he’ll never know anything.
*Words of a Captain, not me
