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
    #101
    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.SEMrush
     

    Attached Files:

    Ian Haney, Mar 28, 2020 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    25,994
    Likes Received:
    3,764
    Best Answers:
    106
    Trophy Points:
    665
    #102
    Don't be afraid to query the database a few times.
    I'd do a query to get the ticket and it's replies and another to get the files. Then as you're looping through the ticket/ticket replies you're also checking for relevant files
     
    sarahk, Mar 28, 2020 IP
  3. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #103
    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, Mar 28, 2020 IP
  4. JEET

    JEET Notable Member

    Messages:
    3,153
    Likes Received:
    318
    Best Answers:
    10
    Trophy Points:
    235
    #104
    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...
     
    JEET, Mar 28, 2020 IP
  5. Ian Haney

    Ian Haney Member

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

    Ian Haney Member

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

    Ian Haney Member

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

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #108
    Ian Haney, Mar 28, 2020 IP
  9. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #109
    @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 ?>
     

    Attached Files:

    Ian Haney, Mar 28, 2020 IP
  10. Ian Haney

    Ian Haney Member

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

    JEET Notable Member

    Messages:
    3,153
    Likes Received:
    318
    Best Answers:
    10
    Trophy Points:
    235
    #111
    I am so glad that you finally got this working!
     
    JEET, Mar 28, 2020 IP
  12. Ian Haney

    Ian Haney Member

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