reply to staff on support ticket

Yeah files would be on the ticket so yeah guessing it should be both reply_id and ticket_id. In the query is tr.reply_id and tr.ticket_id but that's only on the ticket_replies db table so guessing the query may possibly also need st.ticket_id for the support_tickets db table ticket_id. Would that be right?

Sorry know it's getting bit confusing and lost where things are at the mo but just need one last bit added it so if a user creates a new support ticket and they upload a file along with their ticket, the file_name of the uploaded file or files is displayed in the original message box similar to how it is if a user uploads a file with their reply on a already open support ticket. I have added two screenshots if it helps, one is the reply with the filename displayed in the reply box and the other is the current original message box with a circle of where it would be good to show the file_name of the uploaded files. Hopefully that all makes sense.

 

Oh right ok, I'll see if I can do that and post a update if get stuck but think I can do that

 

Ian Haney
Its really not clear what you are trying to do.
What is the message box? The textarea box?

Here is what I am thinking:
Get the ticket_id from query using $_GET['ticket_id']
Get user_id/user_name from session by $_SESSION['user_id']

Now check if this ticket_id belongs to this user.

select
t.ticket_id, t.user_id, u.user_name
from
support_tickets as t
left join users as u
on t.user_id = u.user_id
where t.ticket_id='$ticket_id' and t.user_id='$user_id'
limit 1

If you have user_name in session instead of user_id, then use this:
where t.ticket_id='$ticket_id' and u.user_name='$user_name'

If things are valid, you will get one row in query result.


If result is valid, you have one row, simply fetch all replies and files to this ticket id from STF and TR tables, using the last query I posted.

Why is this becoming so complicated, I'm not sure...

 

@sarahk I have just tried the following code but it's saying no files, I ran the query in the sqlfiddle and returns the data needed but unsure why it don't seem to work in php

$stmt = $mysqli->prepare("SELECT
support_tickets.ticket_id, support_ticket_files.file_name, support_tickets.user_name
FROM
support_tickets INNER JOIN support_ticket_files
ON support_tickets.ticket_id=support_ticket_files.ticket_id
WHERE
support_tickets.ticket_id = ? AND
support_tickets.user_name = ?");
$stmt->bind_param("is", $ticket_id, $username);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows > 0) {
while($row=$result->fetch_array(MYSQLI_ASSOC)){

PHP:

 

@JEET Sorry know it's getting bit confusing with it all, you know currently when a user uploads a file in the reply and it displays the filename in the reply box, I want the same if the user creates a new support ticket and uploads a file with the new support ticket, bit like the same way they do for the ticket reply method

Sorry I know I am prob not making much sense half the time haha

The original message box is just a bootstrap panel box but the form is the same for creating a support ticket as the ticket reply form

 

@JEET Sorry lost me abit but tried my best to understand it, I did the following

I put $_GET['ticket_id']; and $_SESSION['user_name']; in
I then put the code below in and it did not give any errors and displayed the original message

$sql = "select
t.ticket_id, t.user_id, u.user_name
from
support_tickets as t
left join users as u
on t.user_id = u.user_id
where t.ticket_id='$ticket_id' and u.user_name='$user_name'
limit 1";

So I then put the following code in and only displayed the original message still and no filenames

I have noticed I have the following code above the form and wondering if that is causing the issue

<?php
$username = $_SESSION['user_name'];
if($result = $link->query("SELECT ticket_id, ticket_subject, ticket_message, ticket_status, DATE_FORMAT(created_at,'%d/%m/%Y \at\ %H:%i:%s') AS created_at, DATE_FORMAT(ticket_timestamp,'%d/%m/%Y %H:%i:%s') AS ticket_timestamp FROM support_tickets WHERE ticket_id = ".$_GET['ticket_id']." and user_name = '".$_SESSION["user_name"]."'")){
if ($result->num_rows > 0) {
$ticket=$result->fetch_object();
?>

PHP:

I wonder if it's that code that needs amending as thinking that is where the ticket_message is being pulled in from

Is it best if I upload the whole php file here so can see the whole code altogether?

 

I could not upload the php file so have added the whole php code of view-support-ticket.php to here http://sandbox.onlinephpfunctions.com/code/99c261aca317089c4d2dd309ae3c1fc979f29cef

 

@JEET @sarahk I think I have just solved it, I have amended the query that was above the form to the following

if($result = $link->query("SELECT
support_tickets.ticket_id, support_tickets.ticket_subject, support_tickets.ticket_message, support_tickets.ticket_status, DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i:%s') AS created_at, DATE_FORMAT(support_tickets.ticket_timestamp,'%d/%m/%Y %H:%i:%s') AS ticket_timestamp, support_ticket_files.file_name, support_tickets.user_name
FROM
support_tickets INNER JOIN support_ticket_files
ON support_tickets.ticket_id=support_ticket_files.ticket_id
WHERE
support_tickets.ticket_id = ".$_GET['ticket_id']." AND
support_tickets.user_name = '".$_SESSION["user_name"]."'")){

PHP:

I then used <?php echo $ticket->file_name ?> to display the file name and it has worked

Last little question, if the user does not upload a file, the design/layout is not right, the sidebar shows under the the original message box and the ticket message does not show. I have attached the screenshot of the issue

The code to display the ticket message is <?php echo $ticket->ticket_message ?>

 

Think I just sorted it with the following query amendment

SELECT support_tickets.ticket_id, support_tickets.ticket_subject, support_tickets.ticket_message, support_tickets.ticket_status, DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i:%s') AS created_at, DATE_FORMAT(support_tickets.ticket_timestamp,'%d/%m/%Y %H:%i:%s') AS ticket_timestamp, support_ticket_files.file_name, support_tickets.user_name
FROM
support_tickets LEFT JOIN support_ticket_files
ON support_tickets.ticket_id=support_ticket_files.ticket_id
WHERE
support_tickets.ticket_id = ".$_GET['ticket_id']." AND
support_tickets.user_name = '".$_SESSION["user_name"]."'

PHP:

 

I am so glad that you finally got this working!

 

What a nightmare, I am so sorry for all the confusion about it but thank you so much to you and @sarahk for the replies and help with it. I have def learned more about php and it's coding