Jump to content

get data from two tables for specific ticket id and username


ianhaney

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

 

Link to comment
Share on other sites

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

 

Link to comment
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

 

Link to comment
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.

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

 

Link to comment
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.

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

 

Link to comment
Share on other sites

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
Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.