Jump to content
#StayAtHome ×

ianhaney

Members
  • Content Count

    305
  • Joined

  • Last visited

Community Reputation

0 Neutral

About ianhaney

  • Rank
    Advanced Member

Profile Information

  • Gender
    Not Telling
  1. Been looking at the coding again and have amended it slightly as what I said above was wrong so now got the following <?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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id = ? and support_tickets.user_name = '".$_SESSION['user_name']."'"); $stmt -> bind_param('s', $filename); $stmt -> execute(); $stmt -> store_result(); $stmt -> bind_result($filename); $stmt -> fetch(); ?> <ul class="nav"> <li><a href="support-ticket-images/<?php echo $filename; ?>" class="noline" download="download"><?php echo $filename; ?></a></li> <?php $stmt->close(); ?> </ul> I still not got the filenames outputting though
  2. Sorry I can't see question marks in the example code from that page but would the query look like the following <?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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id = ? and support_tickets.user_name = '".$_SESSION['user_name']."'"); ?> Then to echo the filenames, would it look like the following <?php echo $row['file_name']; ?>
  3. I have managed to get two bits of data using prepared statements but struggling with getting the filenames from the db table for the specific ticket id and user, it's currently not getting the filenames. I got the following code so far <?php $mysqli = new mysqli("localhost", "user", "password", "dbname"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } ?> <?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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id = ".$_GET['ticket_id']." AND support_tickets.user_name = '".$_SESSION["user_name"]."'"); $stmt->bind_param("ssi", $_SESSION['user_name'], $ticket_id); $stmt->execute(); $result = $stmt->get_result(); while ($row = $stmt->fetch_assoc()) { ?> <ul class="nav"> <li><a href="support-ticket-images/<?php echo $filename ?>" class="noline" download="download"><?php echo $filename ?></a></li> <?php } ?> </ul> Also if ok to check the following code for getting the two bits of data to confirm it's correct <?php $mysqli = new mysqli("localhost", "user", "password", "dbname"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } ?> <?php $stmt = $mysqli->prepare("SELECT DATE_FORMAT(created_at,'%d/%m/%Y \at\ %H:%i') AS created_at, DATE_FORMAT(ticket_timestamp,'%d/%m/%Y %H:%i') AS ticket_timestamp FROM support_tickets WHERE ticket_id = ".$_GET['ticket_id']." and user_name = '".$_SESSION["user_name"]."'"); if ($stmt === FALSE) { die($mysqli->error); } $stmt->bind_param("ss", $_POST['created_at'], $_POST['ticket_timestamp']); $stmt->execute(); $stmt->store_result(); if($stmt->num_rows === 0) exit('No rows'); $stmt->bind_result($submitted, $lastupdated); $stmt->fetch(); ?> <li><a href="#" class="noline"><strong>Submitted</strong>:<br><?php echo $submitted ?></a></li> <li><a href="#" class="noline"><strong>Last Updated</strong>:<br><?php echo $lastupdated ?></a></li> <?php $stmt->close(); ?>
  4. Thank you, I'll take a look and see what I can do on how to do it. If I get stuck can I post the code here and ask for help?
  5. Ahh ok, that's where I would stuggle as I am not 100% on php and how to use prepared statements
  6. Good question, what's the best way to solve it, can the user_name be displayed in the url address bar as well or something, the user needs to login to view the support tickets
  7. Update I have just solved it by amending the where clause for the ticket id. I have updated the query to the following 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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id = ".$_GET['ticket_id']." AND support_tickets.user_name = '".$_SESSION["user_name"]."'
  8. Yeah sorry I noticed that after, I'll update the query now. It won't let me update but the updated query is below 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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id = 'ticket_id' AND support_tickets.user_name = '".$_SESSION["user_name"]."'
  9. I am trying to work out how to get data from two different tables in the same database and retrieve the filenames uploaded into the database for a specific ticket id and username. I have been having a go at the php code myself and have managed to INNER JOIN the two tables but on the php page, it's showing all the uploaded files for the specific user instead of just for the specific ticket id and username together and looks like it's looping through and repeating. Below is the code I have currently <?php $con = mysqli_connect("localhost","dbuser","dbpass","dbname"); if (mysqli_connect_errno()) { echo "Unable to connect to MySQL! ". mysqli_connect_error(); } $sqli = "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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id"; $res = mysqli_query($con, $sqli); while ($row = mysqli_fetch_array($res)) { echo "<ul class='nav'>"; echo "<li><a href='".$row['file_name']."' class='noline'>Download</a></li>"; } mysqli_close($con); ?>
  10. I have managed to get the files uploaded all ok now in the support_ticket_files db table and the support ticket info is added to the support_tickets db table. the updated code is below
  11. I got the users table that stores the users info and then got the support tickets then the support_ticket_files db table Ahh yeah looks like I got extra brackets not needed, I'll look at that now as that could be the issue why it's not adding the data to the new db table
  12. Ahh ok sorry, the echo of $insert is below INSERT INTO support_tickets (ticket_subject, ticket_message, ticket_status, user_name, user_id) VALUES ('test twelve', 'test twelve two tables files ', 'PENDING SUPPORT', 'ianhaney35new', '180');INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('('customer-agreement-may-2018.docx','ianhaney35new','180'),('IT DONE RIGHT NOTES.docx','ianhaney35new','180')') I guess the id is the best one but guessing I may need to rename it to ticket_id so it matches the column name of the other table. To be honest this is where I struggle as I just want the files uploaded in the new table to be linked to the ticket in the first table so each user can see their own support tickets and the files attached to them if they uploaded any
  13. This is the query for the new table for the file names, it's called support_ticket_files <?php $insert .= "INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('$insertValuesSQL')"; ?> Is that what you meant by showing the whole query?
  14. I can remove the user_name column then and link the new table to the other table using user_id or is ticket_id better. I currently have the id as the primary on the new table and on the other table, the primary key is ticket_id so should I make the id on the new table renamed to ticket_id?
  15. Just done that and it's outputting the following ('customer-agreement-may-2018.docx','ianhaney35new','180'),('IT DONE RIGHT NOTES.docx','ianhaney35new','180') It looks ok to me but unsure why it's not adding the files to the second db table
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.