If both are in same table, staff and customer, then 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.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 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
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> 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> <?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> 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> 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> 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:
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:
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:
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.
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
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?
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:
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> <?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
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.
@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
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
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
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> <?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
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 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?
@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
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...