1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

reply to staff on support ticket

Discussion in 'PHP' started by Ian Haney, Mar 22, 2020.

  1. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #41
    Ahh ok so should be a SELECT query where the question marks areSEMrush
     
    Ian Haney, Mar 24, 2020 IP
    SEMrush
  2. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #42
    Is this right?

    $reply_id = "SELECT ticket_id FROM ticket_replies WHERE ticket_id = '$ticket_id'";
    PHP:
     
    Ian Haney, Mar 24, 2020 IP
  3. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #43
    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
     
    Ian Haney, Mar 24, 2020 IP
  4. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #44
    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
     
    Ian Haney, Mar 24, 2020 IP
  5. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #45
    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:
     
    Ian Haney, Mar 24, 2020 IP
  6. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #46
    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
     
    Ian Haney, Mar 24, 2020 IP
  7. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #47
    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
     
    Ian Haney, Mar 24, 2020 IP
  8. qwikad.com

    qwikad.com Illustrious Member Affiliate Manager

    Messages:
    6,324
    Likes Received:
    1,313
    Best Answers:
    23
    Trophy Points:
    400
    #48
    Did you try to post your question and code on stackoverflow? I got my questions answered and problems solved dozens of times there.
     
    qwikad.com, Mar 24, 2020 IP
  9. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #49
    Stackoverflow won't let me post for some reason
     
    Ian Haney, Mar 24, 2020 IP
  10. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #50
    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:
     
    Ian Haney, Mar 24, 2020 IP
    sarahk likes this.
  11. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #51
    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:
     
    Ian Haney, Mar 24, 2020 IP
  12. sarahk

    sarahk iTamer Staff

    Messages:
    25,994
    Likes Received:
    3,764
    Best Answers:
    106
    Trophy Points:
    665
    #52
    I've created a mockup of your database at http://sqlfiddle.com/#!9/55286f/1 - feel free to have a play around with it, it can be a useful tool for us to visualise your data.

    My thoughts
    • Move the column `user_type` to the user table. Unless the user_type changes from ticket to ticket it should be there. If it does change then the column may be misnamed.
    • I like join conditions to be static so I'd move the user_id check down to the WHERE section rather than being a join condition.
      
      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 u.user_type='customer') 
       WHERE tr.ticket_id = '2' 
       AND tr.reply_id = '101' 
       and tr.user_id='12'
       order by reply_at desc
      Code (markup):
    • Why are we checking that the ticket_id, reply_id and user_id all match?
      What are we looking for?
      The reply? then just use the reply_id
      The ticket? then just use the ticket_id
     
    sarahk, Mar 24, 2020 IP
  13. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #53
    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
     
    Ian Haney, Mar 24, 2020 IP
  14. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #54
    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:
     
    Ian Haney, Mar 24, 2020 IP
  15. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #55
    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?
     
    Ian Haney, Mar 24, 2020 IP
  16. sarahk

    sarahk iTamer Staff

    Messages:
    25,994
    Likes Received:
    3,764
    Best Answers:
    106
    Trophy Points:
    665
    #56
    I just saw this
    (tr.reply_id AND stf.reply_id)
    Code (markup):
    should be
    (tr.reply_id = stf.reply_id)
    Code (markup):
    can you change that line to this

    
    $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 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";
    echo $sql;
    if($replyresult = $link->query($sql)){
    
    Code (php):
    and show what the SQL that is generated looks like
     
    sarahk, Mar 24, 2020 IP
  17. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #57
    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:
     
    Last edited: Mar 24, 2020
    Ian Haney, Mar 24, 2020 IP
  18. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #58
    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
     
    Ian Haney, Mar 24, 2020 IP
  19. sarahk

    sarahk iTamer Staff

    Messages:
    25,994
    Likes Received:
    3,764
    Best Answers:
    106
    Trophy Points:
    665
    #59
    so echo'ing out the sql was good because you can see that the info is missing. Why are you looking for a reply if you don't know which reply you are looking for? Has the user clicked something? Is it the last one you added?

    you still don't need to have ticket_id and user_id in the query if the reply is what you are looking for. Keep it simple.
     
    sarahk, Mar 24, 2020 IP
  20. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #60
    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:
     
    Ian Haney, Mar 24, 2020 IP