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. JEET

    JEET Notable Member

    Messages:
    3,173
    Likes Received:
    320
    Best Answers:
    10
    Trophy Points:
    235
    #81
    If both are in same table, staff and customer, then this should work:
    SEMrush
    $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"]."'order by tr.reply_at desc";
     
    JEET, Mar 25, 2020 IP
    SEMrush
  2. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #82
    @JEET Yeah the staff is in the same users table as customers. I have just amended the sql query to what you put and it's worked all ok and showing the replies from both the customer and staff

    Thank you so much, really all the replies and help @JEET and @sarahk

    Now to just amend the new support ticket page so the file upload works the same way so the file upload is optional like what just done, I should be able to just copy the file upload code section to the new support php file and fingers crossed will work

    Any probs, I'll do my best to work it out first now knowing and learning bit more and post if get really stuck if ok
     
    Ian Haney, Mar 26, 2020 IP
  3. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #83
    Just amended the create support ticket php file and seems ok so gone to check it on view-support-ticket.php for the new support ticket and the reply accordion box is not working for ticket_id 67 and also the sidebar is not showing, it just says no rows but if I check ticket_id 66, it's working fine and everything showing. The block of code on view-support-ticket.php is below with the reply accordion box and the sidebar


    <div class="col-md-9">
    <?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();
    ?>
    
    <div class="panel-group" id="accordion" role="tablist" aria-multiselectable="true">
    <div class="panel panel-info panel-default-reply">
    <div class="panel-heading-replybox" role="tab" id="headingReply">
    <h4 class="panel-title-reply">
    <a class="collapsed" data-toggle="collapse" data-parent="#accordion" href="#collapseReply" aria-expanded="true" aria-controls="collapseReply">
    <i class="fa fa-pencil" aria-hidden="true"></i>&nbsp;Reply
    </a>
    </h4>
    </div>
    <div id="collapseReply" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingReply">
    <div class="panel-body-reply-margin">
    <form action="" method="post" class="form-block ticketform" enctype="multipart/form-data">
    <input type="hidden" name="ticket_id" value="<?php echo $_GET['ticket_id']; ?>"/>
    <div class="col-md-6 col-sm-12">
    <div class="form-group">
    <label>Name</label>
    <input type="text" class="form-control input-lg" name="customer_name" value="<?php echo htmlspecialchars($_SESSION["customer_name"]); ?>" disabled>
    </div>
    </div>
    <div class="col-md-6 col-sm-12">
    <div class="form-group">
    <label>Email</label>
    <input type="text" class="form-control input-lg" name="customer_email" value="<?php echo htmlspecialchars($_SESSION["customer_email"]); ?>" disabled>
    </div>
    </div>
    <?php if($ticket->ticket_status!='CLOSED'){ ?>
    <div class="col-md-12 col-sm-12">
    <div class="form-group">
    <label>Message</label>
    <textarea class="form-control" name="reply_text" id="reply_text" rows="10"></textarea>
    </div>
    </div>
    <div class="col-md-12 col-sm-12">
    <div class="form-group">
    <label>Select Files To Upload</label>
    <input type="file" name="files[]" multiple class="form-control"/>
    </div>
    </div>
    <div class="col-md-12 col-sm-12">
    <div class="form-group">
    <button type="submit" name="submit" class="btn btn-primary btn-sm">Submit</button>
    </div>
    </div>
    <?php } else{ ?>
    <div class="form-group text-center">
    <label><h2><?php echo $ticket->ticket_status ?></h2></label>
    <p>This support ticket is now closed, Please create new ticket for any issue</p>
    </div>
    <?php } ?>
    </form>
    </div>
    </div>
    <?php
    }
    }
    ?>
    </div>
    </div>
    <div class="divider-lg"></div>
    <?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"]."'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) ?>
    <?php if (!empty($row->file_name))
    echo "<br><br>";
    echo html_entity_decode($row->file_name)
    ?>
    </div>
    </div>
    <?php
    }
    }
    }
    ?>
    <div class="panel panel-info panel-default-reply">
    <div class="panel-heading-replies" role="tab">
    ORIGINAL MESSAGE
    </div>
    <div class="panel-body">
    <?php echo $ticket->ticket_message ?>
    </div>
    </div>
    </div><!-- /col -->
    <div class="col-md-3 sidebar">
    
    <?php
    $mysqli = new mysqli("localhost", "username", "password", "dbname");
    if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    
    if(isset($_GET['user_name'])){
    $username =$_GET['user_name'];
    }
    if(isset($_GET['ticket_id'])){
    $ticket_id =$_GET['ticket_id'];
    }
    
    $stmt = $mysqli->prepare("SELECT support_tickets.ticket_id, support_tickets.ticket_status, DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i') AS created_at, DATE_FORMAT(ticket_replies.reply_at,'%d/%m/%Y \at\ %H:%i') AS reply_at FROM support_tickets INNER JOIN ticket_replies ON support_tickets.ticket_id=ticket_replies.ticket_id WHERE support_tickets.ticket_id = ? AND support_tickets.user_name = ? ORDER BY ticket_replies.reply_at DESC LIMIT 1");
    
    $stmt->bind_param("is", $ticket_id, $username);
    $stmt->execute();
    $result = $stmt->get_result();
    if($result->num_rows === 0) exit('No rows');
    $row = $result->fetch_assoc();
    
    ?>
    
    <div class="panel-group box" id="accordionMenuFirst" role="tablist" aria-multiselectable="true">
    <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingFirst">
    <h4 class="panel-title">
    <a role="button" data-toggle="collapse" href="#collapseFirst" aria-expanded="true" aria-controls="collapseFirst">
    <i class="fa fa-ticket" aria-hidden="true"></i>&nbsp;Ticket Info
    </a>
    </h4>
    </div>
    <div id="collapseFirst" class="panel-collapse collapse in" role="tabpanel" aria-labelledby="headingFirst">
    <div class="panel-body-margin">
    <ul class="nav">
    <li><a href="#" class="noline"><strong>Ticked ID</strong><br>#<?php echo $row['ticket_id']; ?></a></li>
    <?php
    if ( $row['ticket_status'] == 'OPEN' )
    echo '<li><a href="#" class="noline"><strong>Ticket Status</strong><p class="nogap" style="color: purple;">' .$row['ticket_status']. '</p></a></li>';
    else if ( $row['ticket_status'] == 'PENDING SUPPORT' )
    echo '<li><a href="#" class="noline"><strong>Ticket Status</strong><p class="nogap" style="color: blue;">' .$row['ticket_status'].'</p></a></li>';
    else if ( $row['ticket_status'] == 'CLOSED' )
    echo '<li><a href="#" class="noline"><strong>Ticket Status</strong>,p class="nogap" style="color: green;">' .$row['ticket_status']. '</p></a></li>';
    ?>
    <li><a href="#" class="noline"><strong>Submitted</strong>:<br><?php echo $row['created_at']; ?></a></li>
    <li><a href="#" class="noline"><strong>Last Updated</strong>:<br><?php echo $row['reply_at']; ?></a></li>
    </ul>
    </div>
    </div>
    </div>
    </div>
    <br>
    <div class="panel-group box" id="accordionMenuSecond" role="tablist" aria-multiselectable="true">
    <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingSecond">
    <h4 class="panel-title">
    <a role="button" data-toggle="collapse" href="#collapseSecond" aria-expanded="true" aria-controls="collapseSecond">
    <i class="fa fa-file-o" aria-hidden="true"></i>&nbsp;Attachments
    </a>
    </h4>
    </div>
    <div id="collapseSecond" class="panel-collapse collapse in" role="tabpanel" aria-labelledby="headingSecond">
    <div class="panel-body-margin">
    <?php
    $mysqli = new mysqli("localhost", "username", "password", "dbname");
    if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    if(isset($_GET['ticket_id'])){
    $ticket_id =$_GET['ticket_id'];
    }
    if(isset($_GET['file_name'])){
    $filename =$_GET['file_name'];
    }
    ?>
    <?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)){
    ?>
    <ul class="nav">
    <li><a href="support-ticket-images/<?php echo $row['file_name']; ?>" class="noline" download="download"><?php echo $row['file_name']; ?></a></li>
    </ul>
    <?php
    }//while ends here
    }else{
    echo '<ul class="nav"><li><a href="#" class="noline">No Files</a></li></ul>';
    }
    ?>
    </div>
    </div>
    </div>
    </div>
    <br>
    <div class="panel-group box" id="accordionMenuThird" role="tablist" aria-multiselectable="true">
    <div class="panel panel-default">
    <div class="panel-heading" role="tab" id="headingThird">
    <h4 class="panel-title">
    <a role="button" data-toggle="collapse" href="#collapseThird" aria-expanded="true" aria-controls="collapseThird">
    <i class="fa fa-bookmark" aria-hidden="true"></i>&nbsp;Shortcuts
    </a>
    </h4>
    </div>
    <div id="collapseThird" class="panel-collapse collapse in" role="tabpanel" aria-labelledby="headingThird">
    <div class="panel-body-margin">
    <ul class="nav">
    <li><a href="create-support-ticket?user=<?php echo $_SESSION['user_name'] ?>" class="noline">Create Support Ticket<i class="fa fa-comments iconslist pull-right"></i></a></li>
    <li><a href="support-tickets?user=<?php echo $_SESSION['user_name'] ?>" class="noline">My Support Tickets<i class="fa fa-ticket iconslist pull-right"></i></a></li>
    <?php if($_SESSION["user_name"]) {
    print "<li><a href='user-account?user=$username' class='noline'>Back to User Account<i class='fa fa-user iconslist pull-right'></i></a></li>";
    } else {
    echo "";
    }
    ?>
    <li><a href="change-password?user=<?php echo $username ?>" class="noline">Change Password<i class="fa fa-pencil iconslist pull-right"></i></a></li>
    <li><a href="logout.php" class="noline">Logout<i class="fa fa-sign-out iconslist pull-right"></i></a></li>
    </ul>
    </div>
    </div>
    </div>
    </div>
    </div><!-- /col -->
    PHP:
    I think the issue is with the section of code below


    
    $stmt = $mysqli->prepare("SELECT support_tickets.ticket_id, support_tickets.ticket_status, DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i') AS created_at, DATE_FORMAT(ticket_replies.reply_at,'%d/%m/%Y \at\ %H:%i') AS reply_at FROM support_tickets INNER JOIN ticket_replies ON support_tickets.ticket_id=ticket_replies.ticket_id WHERE support_tickets.ticket_id = ? AND support_tickets.user_name = ? ORDER BY ticket_replies.reply_at DESC LIMIT 1");
    
    $stmt->bind_param("is", $ticket_id, $username);
    $stmt->execute();
    $result = $stmt->get_result();
    if($result->num_rows === 0) exit('No rows');
    $row = $result->fetch_assoc();
    
    PHP:
     
    Ian Haney, Mar 26, 2020 IP
  4. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #84
    Just had a thought in regards to the No rows issue as mentioned just above, could it be the coding for the create-support-ticket.php causing the issue rather than view-support-ticket.php file? It has added the record to the support_tickets db table all ok but wondering if some of the code could be wrong or if it some coding/sql query on the view-support-ticket.php file needs amending

    In my create-support-ticket.php, I have the following code


    <?php
    
    // Initialize the session
    session_start();
    
    ini_set('display_errors', 'On');
    
    $_SESSION["loggedin"] !== true){
        header("location: login");
        exit;
    }
    
    $username = $_SESSION['user_name'];
    $customername = $_SESSION['customer_name'];
    $customeremail = $_SESSION['customer_email'];
    
    require_once "registerconfig.php";
       
            if (isset($_POST['submit'])){
    
    // 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
    $ticket_subject = filter_input(INPUT_POST, 'ticket_subject', FILTER_SANITIZE_STRING);
    $ticket_message = filter_input(INPUT_POST, 'ticket_message', FILTER_SANITIZE_STRING);
    $ticket_status ='PENDING SUPPORT';
    $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."',LAST_INSERT_ID(),'".$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
    
                // Insert image file name into database            
                $insert = "INSERT INTO support_tickets (ticket_subject, ticket_message, ticket_status, user_name, user_id) VALUES ('$ticket_subject', '$ticket_message', '$ticket_status', '$username', '$user_id');";
                $insert .= "INSERT INTO support_ticket_files (file_name, ticket_id, user_name, user_id) VALUES $insertValuesSQL";   
       
    if(mysqli_multi_query($link, $insert)){
                   
                    $to = "emailaddress";
    
        $subject = "A new support ticket has been submitted";
        $message = " <strong>$username</strong> has just created a support ticket, below is the support ticket
        <br /><br />
        <u>Support Ticket Details</u>
        <br /><br>
        <strong>Support Ticket Subject</strong>: $ticket_subject
        <br/><br><strong>Support Ticket Message</strong>: $ticket_message
        <p><strong><u>Support Ticket Files</u></strong>
        <br>
        <img src='https://www.it-doneright.co.uk/account/support-ticket-images/$fileNames'>
        ";
        // Always set content-type when sending HTML email
        $headers = "MIME-Version: 1.0" . "\r\n";
        $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
        // More headers
        $headers .= 'From: <noreply@it-doneright.co.uk>' . "\r\n";
        $mail=mail($to,$subject,$message,$headers);
                   
                    header("location: support-ticket-confirmation?user=$username");
                    }else{
    echo "Error: Unable to create support ticket";
    }
    //display any errors here
    
    }//submit
    
    ?>
    PHP:
     
    Ian Haney, Mar 26, 2020 IP
  5. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #85
    I am so stupid, I figured it's because there are no replies and where I have got INNER JOIN ticket_replies on support_tickets, the query is correct in terms of if there are replies it will show the info in the sidebar and because there are no replies, it's saying No rows which is correct in a way as there are no replies so just wondering how can I get the sidebar to show if there are no replies but show the info for the ticket such as the ticket_id, ticket_status, the created_at date, only show last updated date if there is a reply otherwise that heading can be hidden. I then think the rest of the info will show and the footer show so def has to be this query causing the issue if are no replies and not showing the sidebar and footer as a result

    SELECT support_tickets.ticket_id, support_tickets.ticket_status, DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i') AS created_at, DATE_FORMAT(ticket_replies.reply_at,'%d/%m/%Y \at\ %H:%i') AS reply_at FROM support_tickets INNER JOIN ticket_replies ON support_tickets.ticket_id=ticket_replies.ticket_id WHERE support_tickets.ticket_id = ? AND support_tickets.user_name = ? ORDER BY ticket_replies.reply_at DESC LIMIT 1
    PHP:
    or does the following code need amending or moving else where? I did try playing around with the following code and moved it to after the sidebar closing div and showed the sidebar but not the ticket info and didn't show the footer still, again think is due to not having a a ticket_reply so do think it's more the sql query possibly or if not that then is the code below


    $stmt->bind_param("is", $ticket_id, $username);
    $stmt->execute();
    $result = $stmt->get_result();
    if($result->num_rows === 0) exit('No rows');
    $row = $result->fetch_assoc();
    PHP:
     
    Last edited: Mar 26, 2020
    Ian Haney, Mar 26, 2020 IP
  6. Spoiltdiva

    Spoiltdiva Illustrious Member

    Messages:
    6,940
    Likes Received:
    2,352
    Best Answers:
    51
    Trophy Points:
    470
    #86
    You are not stupid, you are someone who has just learnt something. You will no doubt go on to learn more and someday will be here at DP teaching the rest of us all what you have learnt.
     
    Spoiltdiva, Mar 26, 2020 IP
  7. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #87
    Thank you, makes me feel better and would love to give back someday to DP and hopefully help others in the same way others are helping me
     
    Ian Haney, Mar 26, 2020 IP
    Spoiltdiva likes this.
  8. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #88
    Just thinking of ways to get the sidebar ticket info and columns displayed still and the footer showing if are no replies as of yet being a new support ticket, would I need a second sql query and sidebar divs or does the current sql query and code after that be amended so the sidebar and footer shows still if no replies?
     
    Ian Haney, Mar 26, 2020 IP
  9. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #89
    Quick update, think I have just solved it as been reading up about joins online and was reading up on LEFT JOINS and sounded like it could be what I need the SQL query to be so amended it and it's now displaying the sidebar and footer and just the info I need, if someone can just confirm for me please it looks ok or if I need to amend it any further?

    The old code is below

    SELECT support_tickets.ticket_id,
    support_tickets.ticket_status, 
    DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i') AS created_at,
    DATE_FORMAT(ticket_replies.reply_at,'%d/%m/%Y \at\ %H:%i') AS reply_at 
    FROM support_tickets INNER JOIN ticket_replies
    ON support_tickets.ticket_id=ticket_replies.ticket_id
    WHERE support_tickets.ticket_id = ?
    AND support_tickets.user_name = ?
    ORDER BY ticket_replies.reply_at
    DESC LIMIT 1
    PHP:
    The new amended code using LEFT JOIN is below

    SELECT support_tickets.ticket_id,
    support_tickets.ticket_status,
    DATE_FORMAT(support_tickets.created_at,'%d/%m/%Y \at\ %H:%i') AS created_at,
    DATE_FORMAT(ticket_replies.reply_at,'%d/%m/%Y \at\ %H:%i') AS reply_at
    FROM support_tickets
    LEFT JOIN ticket_replies
    ON support_tickets.ticket_id=ticket_replies.ticket_id
    WHERE support_tickets.ticket_id = ?
    AND support_tickets.user_name = ?
    ORDER BY ticket_replies.reply_at
    DESC LIMIT 1
    PHP:
     
    Ian Haney, Mar 26, 2020 IP
  10. sarahk

    sarahk iTamer Staff

    Messages:
    26,008
    Likes Received:
    3,770
    Best Answers:
    106
    Trophy Points:
    665
    #90
    Looks ok but if you specify the username you won't see everyone's replies
     
    sarahk, Mar 26, 2020 IP
  11. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #91
    Ahh ok what in the url you mean or in the query itself?

    Also bit of further help as just noticed something, I would like the file_name displayed in the original message box if they upload a file or files when creating a new support ticket. I thought it would be the same way as showing it in the reply box but it's not showing the file_name in the original box for some reason, the updated code is below


    <div class="panel panel-info panel-default-reply">
                                        <div class="panel-heading-replies" role="tab">
                                        ORIGINAL MESSAGE
                                        </div>
                                        <div class="panel-body">
                                        <?php echo $ticket->ticket_message ?>
                                        <?php if (!empty($row->file_name))
                                        echo "<br><br>";
                                        echo html_entity_decode($row->file_name)
                                        ?>
                                        </div>
                                    </div>
    PHP:
    The whole 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, 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"]."'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) ?>
                                        <?php if (!empty($row->file_name))
                                        echo "<br><br>";
                                        echo html_entity_decode($row->file_name)
                                        ?>
                                        </div>
                                    </div>
                                   
                                    <?php
                                    }
                                    }
                                    }
                                    ?>
                                   
                                    <div class="panel panel-info panel-default-reply">
                                        <div class="panel-heading-replies" role="tab">
                                        ORIGINAL MESSAGE
                                        </div>
                                        <div class="panel-body">
                                        <?php echo $ticket->ticket_message ?>
                                        <?php if (!empty($row->file_name))
                                        echo "<br><br>";
                                        echo html_entity_decode($row->file_name)
                                        ?>
                                        </div>
                                    </div>
    PHP:
    I did try moving one of the curly brackets to after the closing div of the second panel box but that did not work either
     
    Ian Haney, Mar 26, 2020 IP
  12. sarahk

    sarahk iTamer Staff

    Messages:
    26,008
    Likes Received:
    3,770
    Best Answers:
    106
    Trophy Points:
    665
    #92
    The file name is saved in the database, right? you've checked that it's there?

    What is this meant to be doing?
    <?php echo ($row->user_id!=$_SESSION['user_id'])?'':''; ?>
    Code (markup):
    I'm not sure that this is doing what you think it's doing
    <?php if (!empty($row->file_name))
    echo "<br><br>";
    echo html_entity_decode($row->file_name)
    ?>
    Code (php):
    an if without {} will only impact the next line. Should it be

    <?php if (!empty($row->file_name)){
       echo "<br><be>";
       echo html_entity_decode($row->file_name)
    }
    ?>
    Code (php):
    Does the editor you're using have a Format, Pretty, Reindent option?
    It can help you to see where the indenting and brackets are wrong.
    upload_2020-3-27_11-47-27.png
    upload_2020-3-27_11-49-7.png
     
    sarahk, Mar 26, 2020 IP
  13. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #93
    @sarahk Yeah the file_name is stored in the db table support_ticket_files. I have checked the db table and it's there for the new support ticket I created and is linked by ticket_id

    To be honest I am not sure what <?php echo ($row->user_id!=$_SESSION['user_id'])?'':''; ?> that is doing there as a php coder put that in ages ago

    I thought the following code is to check if a file_name is not empty and if not then add the code below the empty line, it does seem to work. I'll amend that bit of code to what you have

    I am using dreamweaver cc, I'll have to look about the formatting and identing of the code
     
    Ian Haney, Mar 26, 2020 IP
  14. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #94
    I think the file_name is not displaying as need to select it from the db table but does mean amending the query to JOIN the support_ticket_files db table onto support_tickets as the new support ticket has no replies as of yet but would be good to add the file_name uploaded into the original message box if the user uploads a file or files when they create the support ticket
     
    Ian Haney, Mar 26, 2020 IP
  15. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #95
    I have been trying this morning to alter the sql query so can get the file_name displayed in the original message box that has no replies as of yet but the user uploads a file with the new ticket message but I can't seem to work it out in what order it should go, I am not sure where or how to add the support_tickets db table in the sql query so can link the ticket_id to the support_ticket_files ticket_id without causing upset to the ticket_replies db table that displays the file_name in the reply box

    Hope that all makes sense
     
    Ian Haney, Mar 27, 2020 IP
  16. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #96
    Been playing around with the sql on sqlfiddle site http://sqlfiddle.com/#!9/701847/66, I have added the updated sql query and on there it looks right as seems to be getting the right data but when integrate it into the php file, it's not displaying the file_name in the original box

    The updated sql query is below and below that is the php section of code

    SELECT tr.ticket_id,
    tr.reply_id,
    stf.file_name,
    stf.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 support_ticket_files stf
    on tr.reply_id = stf.reply_id
    left join users u
    on u.user_id=tr.user_id
    JOIN support_tickets st
    ON stf.ticket_id = st.ticket_id
    WHERE tr.ticket_id = '".$_GET["ticket_id"]."'
    order by tr.reply_at desc
    PHP:

    <?php
    
    $sql = "SELECT tr.ticket_id,
    tr.reply_id,
    stf.file_name,
    stf.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 support_ticket_files stf
    on tr.reply_id = stf.reply_id
    left join users u
    on u.user_id=tr.user_id
    JOIN support_tickets st
    ON stf.ticket_id = st.ticket_id
    WHERE tr.ticket_id = '".$_GET["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) ?>
                  <?php if (!empty($row->file_name))
    echo "<br><br>";
    echo html_entity_decode($row->file_name)
    ?>
                </div>
              </div>
              <?php
    }
    }
    }
    ?>
              <div class="panel panel-info panel-default-reply">
                <div class="panel-heading-replies" role="tab"> ORIGINAL MESSAGE </div>
                <div class="panel-body"> <?php echo $ticket->ticket_message ?>
                  <?php echo html_entity_decode($row->file_name) ?>
                </div>
              </div>
    PHP:
    I did try moving one or two of the brackets to below the closing div of the second panel box for the original message but that don't work either
     
    Ian Haney, Mar 27, 2020 IP
  17. JEET

    JEET Notable Member

    Messages:
    3,173
    Likes Received:
    320
    Best Answers:
    10
    Trophy Points:
    235
    #97
    Try this:

    select
    tr.reply_id, tr.ticket_id, tr.reply_text, tr.user_id,
    DATE_FORMAT(tr.reply_at,'%d/%m/%Y \at\ %H:%i:%s') AS reply_at,
    stf.file_name,
    u.customer_name
    from ticket_replies as tr
    left join support_tickets_files as stf
    on tr.reply_id = stf.reply_id
    left join users as u
    on u.user_id=tr.user_id
    where tr.ticket_id='$_GET[ticket_id]' order by tr.reply_at desc
     
    JEET, Mar 27, 2020 IP
  18. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #98
    @JEET I tried that code but didn't work unfortunately. Does any of the other code need amending below that query or just amend the query itself?
     
    Ian Haney, Mar 27, 2020 IP
  19. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #99
    @JEET sorry just trying to work out the latest sql code and try and work it out myself, does the support_ticket_files db table need to be linked to the support_tickets db table so if a user uploads a file with the ticket, it shows in the original message box as just wondering if the latest sql code will only show the file_name if the support ticket has replies? Hope that makes sense
     
    Ian Haney, Mar 27, 2020 IP
  20. sarahk

    sarahk iTamer Staff

    Messages:
    26,008
    Likes Received:
    3,770
    Best Answers:
    106
    Trophy Points:
    665
    #100
    Making assumptions here
    if files are only ever attached to replies then they only need the reply_id because reply will link it to the ticket
    however, I'd be surprised if you didn't have files on the ticket so ticket needs both reply_id and ticket_id


    But... I've lost track of quite where you're at in here...
     
    sarahk, Mar 27, 2020 IP