reply to staff on support ticket

right ok so can I take out ticket_id and user_id then out of the SELECT query below?

SELECT tr.ticket_id, tr.reply_id, stf_file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, u.customer_name FROM ticket_replies tr left join support_ticket_files stf on ((tr.reply_id = stf.reply_id)) left join users u on (u.user_id=tr.user_id and tr.user_type='customer') WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.reply_id = '".$_GET["reply_id"]."' AND tr.user_id = '".$_SESSION["user_id"]."' order by reply_at desc

PHP:

after creating the record, I have got $last_inserted_id=$mysqli->insert_id; // returns last ID
Is that the right bit of code to grab the id or should I use one of the following?

$mysqli->insert_id;
$last_id = $conn->insert_id;
$id = last_insert_id();

PHP:

It would be good to get it completed sooner as I feel it's nearly complete, it's just these last couple of bits, think this is the last bit if I remember and then can just test the create support ticket page, may need bit of tweak on that page so it works similar way as the reply to a ticket so it works if the user does not add a file for upload but I can get most code for that from the reply section of the support ticket that am currently working on

 

I'll have a look at the code again in the morning to see how to get the reply_id in the query

 

This is the code you can use to connect files to individual comments, instead of connecting to whole ticket.
Instead of creating a new column in files table, you can use the ticket_id column itself to store new reply_id,
but to avoid confusion, I have used a new column.


<?php

if (isset($_POST['submit'])){
$ticket_id= $_REQUEST['ticket_id'];
if( !is_numeric($ticket_id) ){ echo "Error: Cannot continue"; exit; }

// File upload configuration
$targetDir = "support-ticket-images/";
$allowTypes = array('pdf','doc','docx','jpg','png','jpeg','gif');


$statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = '';

// Escape user inputs for security
$reply_text = strip_tags($_POST['reply_text'], ENT_QUOTES);
$username = htmlentities($_SESSION["user_name"], ENT_QUOTES);
$user_id = htmlentities($_SESSION["user_id"], ENT_QUOTES);
$fileNames = array_filter($_FILES['files']['name']);

if(!empty($fileNames)){
foreach($_FILES['files']['name'] as $key=>$val){
//File upload path
$fileName = basename($_FILES['files']['name'][$key]);
$targetFilePath = $targetDir . $fileName;
// Check whether file type is valid
$fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION);

if(in_array($fileType, $allowTypes)){
// Upload file to server
if(move_uploaded_file($_FILES["files"]["tmp_name"][$key], $targetFilePath)){
// Image db insert sql
$insertValuesSQL.="('".$fileName."', '$ticket_id', 'NewReplyID', '".$username."','".$user_id."'),";
}//move ends
}//file type check ends
}//foreach ends
}//empty file check ends



if(!empty($insertValuesSQL)){
$insertValuesSQL = trim($insertValuesSQL, ',');
}

//file checks done


//now add comment to table

if( strlen($reply_text)>3 ){

$sql = "INSERT INTO ticket_replies (ticket_id, reply_text,user_type, user_id) VALUES ('$ticket_id','$reply_text','customer', '$user_id');";
$link->query($sql);
$replyID= $link->insert_id;

if( strlen($insertValuesSQL)>3 ){
$insertValuesSQL = str_replace( "'NewReplyID'","'$replyID'", $insertValuesSQL );

$sql="INSERT INTO support_ticket_files (file_name, ticket_id, reply_id, user_name, user_id) VALUES $insertValuesSQL ";
$link->query($sql);
}


$sql="UPDATE support_tickets set ticket_status = 'PENDING SUPPORT' where ticket_id ='$ticket_id'";

}else{
echo "Error: Post a reply";
}

//display any errors here

}//submit
?>


But remember one thing, if you use this, then you need to create one more column in your "support_ticket_files" table, called "reply_id", should be INT type.

Additionally, when you display your ticket, then that query will also change.

Currently you have a join on "ticket_id".
That will remain, to pull out reply_text data,
and another join will get added on reply_id column, to pull out files

 

Thank you for the code @JEET I have already got the reply_id as INT type in the support_ticket_files db table

So to display the ticket/replies, as you say I'll need to change the current query, that right? if ok to paste what the current query is like and will see if I can amend it

 

I've added in the code above @JEET and below is the current code for the query to get the replies and the filenames for any uploaded files once working

$sql= "SELECT tr.ticket_id, tr.reply_id, stf_file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, u.customer_name FROM ticket_replies tr left join support_ticket_files stf on ((tr.reply_id = stf.reply_id)) left join users u on (u.user_id=tr.user_id and tr.user_type='customer') WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.reply_id = '".$_GET["reply_id"]."' AND tr.user_id = '".$_SESSION["user_id"]."' order by reply_at desc";
echo $sql;
if($replyresult = $link->query($sql)){

PHP:

That current code echos the following on the page itself

SELECT tr.ticket_id, tr.reply_id, stf_file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, u.customer_name FROM ticket_replies tr left join support_ticket_files stf on ((tr.reply_id = stf.reply_id)) left join users u on (u.user_id=tr.user_id and tr.user_type='customer') WHERE tr.ticket_id = '66' AND tr.reply_id = '' AND tr.user_id = '180' order by reply_at desc

 

Sorry @JEET bit confused by the JOIN on the ticket_id? I don't see that in the current query.

 

I noticed a small issue with my query where I had stf_file_name so corrected it to stf.file_name and also noticed I had ((tr.reply_id = stf.reply_id)) so corrected that to (tr.reply_id = stf.reply_id)

It's still not getting the reply_id in the sql query echo. it's just got AND tr.reply_id = ''

I did think to change it to stf.reply_id but that still gives the same result and not getting the reply_id so still end up with AND stf.reply_id = "

 

What is the primary key of ticket_replies table? Name of the auto_increment column?
In the below query replace "ticket_id_key" with that name and this should work.

$sql= "SELECT tr.ticket_id, tr.reply_id, stf.file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, u.customer_name
FROM ticket_replies tr
left join support_ticket_files stf on
tr.ticket_id_key = stf.reply_id
left join users u on
u.user_id=tr.user_id
WHERE tr.ticket_id = '".$_GET["ticket_id"]."'
AND tr.user_id = '".$_SESSION["user_id"]."'
group by tr.ticket_id
order by tr.reply_at desc ";

 

@JEET The primary key of ticket_replies is reply_id and it is auto increment column

I'll try the code now and post a update

 

@JEET I have updated the query to the following but still not seeing any replies and the echo shown on the page is below the query below

$sql= "SELECT tr.ticket_id, tr.reply_id, stf.file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, .customer_name FROM ticket_replies tr left join support_ticket_files stf on tr.reply_id = stf.reply_id left join users u on u.user_id=tr.user_id WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.user_id = '".$_SESSION["user_id"]."' group by tr.ticket_id order by tr.reply_at desc";

PHP:

SELECT tr.ticket_id, tr.reply_id, stf.file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, .customer_name FROM ticket_replies tr left join support_ticket_files stf on tr.reply_id = stf.reply_id left join users u on u.user_id=tr.user_id WHERE tr.ticket_id = '66' AND tr.user_id = '180' group by tr.ticket_id order by tr.reply_at desc

The block of code is below

<?php

$sql= "SELECT tr.ticket_id, tr.reply_id, stf.file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, .customer_name FROM ticket_replies tr left join support_ticket_files stf on tr.reply_id = stf.reply_id left join users u on u.user_id=tr.user_id WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.user_id = '".$_SESSION["user_id"]."' group by tr.ticket_id order by tr.reply_at desc";
                               
echo $sql;
if($replyresult = $link->query($sql)){

?>

if ($replyresult->num_rows > 0) {
      while ($row = $replyresult->fetch_object())  {
?>

<div class="panel panel-info panel-default-reply">
                               
<?php echo ($row->user_id!=$_SESSION['user_id'])?'':''; ?>
    <div class="panel-heading-replies" role="tab">
     <i class="fa fa-user" aria-hidden="true"></i>&nbsp; <?php echo ($row->user_id==$_SESSION['user_id'])?$row->customer_name:'Support Team'; ?>
      <span class="pull-right"><?php echo $row->reply_at?></span>
      </div>
       <div class="panel-body">
              <?php echo html_entity_decode($row->reply_text) ?>
               <br>
              <?php echo html_entity_decode($row->file_name) ?>
      </div>
       </div>
                                   
<?php
}
}
}
?>

PHP:

 

@JEET I just spotted another mistake in my query compared to yours, I noticed on my query I had .customer_name instead of u.customer_name and refreshed the page itself and it's showing the reply now with the file_name in the box so that's good but it's not showing any of the other replies.

There should be another 4 replies for the ticket id 66 looking at my phpmyadmin ticket_replies db table

I have taken another screenshot of how it now looks. I take it now I can comment out the echo $sql just in case I need it again in the future

My updated code is below

<?php

$sql= "SELECT tr.ticket_id, tr.reply_id, stf.file_name, tr.reply_text, tr.user_id, DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at, u.customer_name FROM ticket_replies tr left join support_ticket_files stf on tr.reply_id = stf.reply_id left join users u on u.user_id=tr.user_id WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.user_id = '".$_SESSION["user_id"]."' group by tr.ticket_id order by tr.reply_at desc";

echo $sql;
if($replyresult = $link->query($sql)){

if ($replyresult->num_rows > 0) {
while ($row = $replyresult->fetch_object())  {
?>

<div class="panel panel-info panel-default-reply">
                               
<?php echo ($row->user_id!=$_SESSION['user_id'])?'':''; ?>
<div class="panel-heading-replies" role="tab">
<i class="fa fa-user" aria-hidden="true"></i>&nbsp; <?php echo ($row->user_id==$_SESSION['user_id'])?$row->customer_name:'Support Team'; ?>
<span class="pull-right"><?php echo $row->reply_at?></span>
</div>

<div class="panel-body">
<?php echo html_entity_decode($row->reply_text) ?>
<br>
<?php echo html_entity_decode($row->file_name) ?>
</div>
</div>
                                   
<?php
}
}
}
?>

PHP:

 

Definitely and I am learning as well, I admit it takes a while to sink in, I only got my head around how prepared statements work the other day with placeholders etc.

Just need to find out if possible why the other replies are not showing for ticket id 66, looking at the phpmyadmin and the ticket_replies db table, there are 4 other replies for ticket id 66 but as my screenshot shows, there is only the one reply showing which is the one with the file_name in it

 

@sarahk ahh ok sorry forgot about group bit that groups them all together, I have removed it now and it's showing all the users reply, hopefully last issue now for this php page is that it's not showing the reply I sent from the admin side(support staff)

I wonder if I need to add in tr.user_type='customer' to the query, would that solve it?

 

Ahh ok, how can I get all the replies to show so it shows replies from the user and the support staff? Do I need to select user_type from the ticket_replies db table as well?

 

@Ian Haney
Is your support staff also registered in the same users table?
Something like this:
( 10, 'John', staff' ),
'( 88, 'David', 'customer' )
and so on.