reply to staff on support ticket

Ahh ok so should be a SELECT query where the question marks are

 

Is this right?

$reply_id = "SELECT ticket_id FROM ticket_replies WHERE ticket_id = '$ticket_id'";

PHP:

 

Think I have managed to get the query ok as it looks ok in the phpmyadmin

I got the following query

$reply_id = "SELECT support_ticket_files.ticket_id, support_ticket_files.reply_id, support_ticket_files.file_name FROM support_ticket_files INNER JOIN ticket_replies ON support_ticket_files.ticket_id=ticket_replies.ticket_id WHERE support_ticket_files.ticket_id = '$ticket_id' AND ticket_replies.reply_id = '$reply_id'";

PHP:

I then tried to display the file_name in the reply box but got the following error

Fatal error: Uncaught Error: Cannot use object of type stdClass as array in /home/itdonerightco/public_html/account/view-support-ticket.php:227 Stack trace: #0 {main} thrown in /home/itdonerightco/public_html/account/view-support-ticket.php on line 227

I am using <?php echo $row['file_name'];?> to try and display the filename in the reply box

 

Sorry I will need help on this bit so can get the file_name displayed within the reply box if the user uploads a file or files with their reply

It's bit too advanced for me I think

 

I think the support_ticket_files db table needs to be joined on the following query so can get the file_name for the related reply id, that right?

if($replyresult = $link->query("SELECT tr.ticket_id, 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 users u on (u.user_id=tr.user_id and tr.user_type='customer' and tr.user_id='".$_SESSION["user_id"]."') WHERE ticket_id = '".$_GET["ticket_id"]."' order by reply_at desc")){

PHP:

If so I am unsure how to join a third table on that query

The block of whole code is below

<?php
if($replyresult = $link->query("SELECT tr.ticket_id, 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 users u on (u.user_id=tr.user_id and tr.user_type='customer' and tr.user_id='".$_SESSION["user_id"]."') WHERE ticket_id = '".$_GET["ticket_id"]."' order by reply_at desc")){
                                  
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) ?>
       </div>
       </div>
                                  
       <?php
        }
        }
        }
        ?>

PHP:

 

I thought I would try again and have another go at the select query and put together the following and tested it in phpmyadmin and works ok there

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 AND stf.reply_id left join users u on (u.user_id=tr.user_id and tr.user_type='customer' and tr.user_id=180) WHERE tr.ticket_id = 66 AND tr.reply_id = 34 order by reply_at desc

PHP:

So I then tried to put php into it and came up with the following

if($replyresult = $link->query("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 AND stf.reply_id left join users u on (u.user_id=tr.user_id and tr.user_type='customer' and tr.user_id=180) WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.reply_id = '".$_GET["reply_id"]."' order by reply_at desc")) {

PHP:

I used <?php echo html_entity_decode($row->file_name) ?> to try and display the file_name within the reply box but it's not displaying any replies and is only showing the original ticket message that the user wrote to open the support ticket

Sorry I am really trying to work it out myself but getting quite stuck on it

 

Sorry me again

I have had another go as don't think the latest code I posted would work so gone back to the original code and got the following

/* GET THE ID FROM HERE */
        $reply_id = 'LAST_INSERT_ID()';

        if (strlen($insertValuesSQL) > 3) {
            $insertValuesSQL = str_replace('###', $reply_id, $insertValuesSQL);
            $sql = "INSERT INTO support_ticket_files (file_name, ticket_id, reply_id, user_name, user_id) VALUES {$insertValuesSQL} ";
            $link->query($sql);
        }

PHP:

Hopefully $reply_id = 'LAST_INSERT_ID()'; should get the reply_id of the new record added to the ticket_replies db table

Bit unsure what to do next to display the file_name in the reply box after the user submits the reply like it was in my screen shot that I circled where I would like the file_name displayed

 

Did you try to post your question and code on stackoverflow? I got my questions answered and problems solved dozens of times there.

 

Stackoverflow won't let me post for some reason

 

small update, think I can use any of the following to get the last id added to the ticket_replies db table, which one is best or they as good as each other

$mysqli->insert_id;
$last_id = $conn->insert_id;
$id = last_insert_id();
$last_inserted_id=$mysqli->insert_id; // returns last ID

PHP:

 

I went with $last_inserted_id=$mysqli->insert_id; // returns last ID in the end

Bit I am unsure on now is how to now get the filename displayed for the specific reply_id?

Was I close when I mentioned about the following code should be changed from

SELECT tr.ticket_id, 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 users u on (u.user_id=tr.user_id and tr.user_type='customer' and tr.user_id='".$_SESSION["user_id"]."') WHERE ticket_id = '".$_GET["ticket_id"]."' order by reply_at desc

PHP:

to this

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 AND stf.reply_id left join users u on (u.user_id=tr.user_id and tr.user_type='customer' and tr.user_id='".$_SESSION["user_id"]."') WHERE tr.ticket_id = '".$_GET["ticket_id"]."' AND tr.reply_id = '".$_GET["reply_id"]."' order by reply_at desc

PHP:

 

Thank you, that will help loads, I can use that to play around with. Regarding the user_type, it does get updated so if the support ticket is replied to by admin, a new row is added and the user type is updated to support team and then if the user replies, a new row is added and the user_type then says customer, if ok I would like to stay in the ticket_replies db table only because to save changing any more coding as really not 100% on PHP as prob know by now and knowing my luck, I'll end up with loads more errors if try moving user_type to the users db table

 

I have updated the query on http://sqlfiddle.com/#!9/e9a3d8/2 and it's fine there so add that into the php file and that's where the issue starts to happen, it's the php part in the WHERE clauses being the issue

On the page it don't show the replies, it just shows the original support ticket message the user opened the support ticket with

The code is below

if($replyresult = $link->query("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 AND 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:

 

I'm trying to get my head around the sql on http://sqlfiddle.com/#!9/701847/1

I have added a new row in each db table and changes the WHERE clause id's so my thinking is it should show just one row. Can someone show where I am going wrong with it please as not understanding the query and why it's showing 3 rows. Is it the sql query itself wrong?

 

Ahh ok, that's better in the sqlfiddle now as it's showing just the one row so put it into the php file itself and it's generated the sql 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 = '66' AND tr.reply_id = '' AND tr.user_id = '180' order by reply_at desc

Looks like just the reply_id needs to be generated. If I am hopefully right, reply_id would need to be added amongst the lines below, is that right?

$ticket_id = htmlentities($_POST["ticket_id"], ENT_QUOTES);
$reply_text = strip_tags( htmlentities( $_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']);

PHP:

 

I tried adding in $reply_id in the coding on my previous pst but still the same and showing the same sql generated on the php page itself

 

To be honest I'm losing track of what I need to do or what coding I need as I am trying to understand and follow it as I go. I thought I would need ticket_id and user_id in the query so it's all linked together so it knows that the ticket is for the user?

How can I get the reply_id into the query so it's got the missing info added in? I don't get it, I am guessing it's nothing to do with the ### left in from the earlier code that is below

$insertValuesSQL .= "('{$fileName}', '{$ticket_id}','###', '{$username}','{$user_id}'),";

PHP:

if (strlen($insertValuesSQL) > 3) {
            $insertValuesSQL = str_replace('###', $reply_id, $insertValuesSQL);
            $sql = "INSERT INTO support_ticket_files (file_name, ticket_id, reply_id, user_name, user_id) VALUES {$insertValuesSQL} ";
            $link->query($sql);
        }

PHP: