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 have <?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 = 'ticket_id' AND support_tickets.user_name = '".$_SESSION["user_name"]."'"; $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); ?>
I have just managed to solve it by amending 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"]."'
Just a remark to your code. I would highly recommend not inserting raw $_GET or any other user input values directly into SQL queries, as they could be used for SQL injection attacks. You need to follow the best practices: the best way is to use parameterized PDO queries instead of mysqli_query, or at least sanitize input (with filter_var, etc.). Even simple $id = intval($_GET['ticket_id']); PHP: is better than inserting raw $_GET value.
I have updated the code since to the following but it seems to be retrieving the file_names of each file uploaded instead of just the file_names for each specific ticket_id <?php $mysqli = new mysqli("localhost", "user", "pass", "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']; } 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.user_name=support_ticket_files.user_name WHERE support_tickets.ticket_id = '$ticket_id' AND support_tickets.user_name = '$username'"); $stmt->execute(); $stmt -> store_result(); $stmt -> bind_result($ticket_id, $filename, $username); while ($stmt->fetch()) { ?> <ul class="nav"> <li><a href="support-ticket-images/<?php echo $filename; ?>" class="noline" download="download"><?php echo $filename; ?></a></li> </ul> <?php } ?> Sorry also how do I put the code in code tags so it's with the black background like yours above and easier to see
Ian Haney I think your problem is that you are trying to join tables on wrong column. The join should be on ticket_id and not on username If you are already storing ticket_id in support_ticket_files table, then try this query below: If not, then you will have to make a new column ticket_id in support_ticket_files, fill it with proper data, and then run this query below. $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 ='$ticket_id' AND support_tickets.user_name ='$username'");
I have updated the code to the above and seems ok for when there are files for the specific ticket_id but when there are no files, it says no rows which is ok but the rest of the site such as the footer does not show. The updated code is below <?php $mysqli = new mysqli("localhost", "user", "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 ='$ticket_id' AND support_tickets.user_name ='$username'"); //$stmt->bind_param("i", $ticket_id); $stmt->execute(); $result = $stmt->get_result(); if($result->num_rows === 0) exit('No rows'); $row = $result->fetch_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:
1. Using $mysqli->prepare is the move to the right direction, but it is not complete. Passing SQL as ready-to-use string like you do has no difference to mysqli_query and doesn't prevent you from the possible SQL injection. For example, it seems that with this simple input your SQL will return all tickets, just insert this test line at the beginning of your script and check the results: $_GET['ticket_id'] = "0' or '1"; PHP: For the real parameterized query check the examples in the documentation for mysqli_prepare, your SQL should contain placeholders (?) instead of values and param values should be passed via bind_param. 2. You are using exit('No rows'); which stops script execution and footer output. You need to change your logic: if($result->num_rows > 0) { //display results } else echo 'No rows'; PHP:
Oh right ok, I have updated the code to hopefully be better and use placeholders and bind_param and got the footer output now if there are no file attachments but got one issue, I have put the error message below Notice: Undefined index: file_name in /home/itdonerightco/public_html/account/view-support-ticket.php on line 245 The updated code is below <?php $mysqli = new mysqli("localhost", "user", "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) { ?> <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 } else echo 'No Files'; ?> PHP: I was bit unsure where to put the line $_GET['ticket_id'] = "0' or '1"; PHP:
Try this to remove the error you are getting 'file_name' is undefined. 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 'No Files'; } ?>
Thank you so much, that has solved the issue Thank you to everyone who has replied and helped with the coding, really appreciate it