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> <?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.
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:
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
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?
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
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
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.
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: