Jump to content
#StayAtHome ×
ianhaney

get data from two tables for specific ticket id and username

Recommended Posts

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

 

Share this post


Link to post
Share on other sites
1 hour ago, ianhaney said:

instead  of just for the specific ticket id

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

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ianhaney
updated query code

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

It's why we're here.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ianhaney
amended text

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.