get data from two tables for specific ticket id and username

[ianhaney]

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);
														?>

 

[Barand]

1 hour ago, ianhaney said:

instead  of just for the specific ticket id

you don't specify a specific ticket id in the query

[ianhaney]

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"]."'

 

Edited March 18, 2020 by ianhaney
updated query code

[ianhaney]

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"]."'

 

[requinix]

Unfortunately for you, that's not the end of your problems.

Question for you: what query would run if I were to visit your page, go into my browser's address bar, and change the ticket_id to be anything I wanted? For example, what if I changed it to

123+OR+ticket_id+=+456

 

[ianhaney]

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

[requinix]

user_name? Not sure where you're going with that, but my point is that by putting a $_GET value directly into your query, anyone can change the query to do whatever they want. Even to make it do Bad Things.

What you need are prepared statements: you build a query with the structure that you want, using placeholders for where data needs to go, then you tell MySQL what data goes in those placeholders. Prepared statements actually have a few other benefits, but they don't apply much to this particular situation.

[ianhaney]

Ahh ok, that's where I would stuggle as I am not 100% on php and how to use prepared statements

[requinix]

No problem. The thing I linked has a few examples on how to set up a prepared statement, pass values into it, run the statement, and get the results back.

[ianhaney]

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?

[requinix]

It's why we're here.

[ianhaney]

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(); ?>

 

[requinix]

Look at the example code from that page I linked you to. Do you see the question marks in the queries? Notice how it does not put variables into the queries directly? That's what you need to do: use a question mark in every place that you want a piece of data, then use bind_param() to fill in the values.

In the first bit of code you're using fetch_assoc() to get rows of data. So the values you need will be in $row. PHP isn't creating variables for you because you didn't tell it to do that. In the second bit of code you're using bind_result() which will create variables.

[ianhaney]

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']; ?>

 

[ianhaney]

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

Edited March 19, 2020 by ianhaney
amended text

[requinix]

1. You're still putting a variable into the query. The user_name from the session. Make that be a parameter too.

2a. Your query is searching for a ticket_id that matches your $filename.
2b. You aren't checking if the query perhaps didn't return any results.
2c. Your query is returning the ticket_id, file_name, and user_name. In that order. You are only binding the one $filename, and in the first position. Which would be the ticket_id.

Please, try spending more time learning about this. If you can't see the page I linked you then find another site on the internet that talks about how to do prepared statements with mysqli.