Php mysqli prepared statement data for specific user session and ticket id

PHP
21

I got the correct data showing now by amending the code to the following

<?php
						
$ticket_id = '';
$username = '';
						
$stmt = $mysqli->prepare("SELECT ticket_id, 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, user_name FROM support_tickets WHERE ticket_id = '58' AND user_name = 'ianhaney35new'");
						
//$stmt->bind_param();

$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
$row = $result->fetch_assoc();

?>

So now just need to amend it back with a placeholder and leave the single quotes around the ? and put a s in the bind_param line.

In phpmyadmin it does work still without the single quotes around the 58.

Regarding the username and $_SESSION[ā€˜user_nameā€™]. Where should I put the var_dump() or echo ().

If I use user_id instead of _user_name, does that mean I need to change all pages to be for user_id instead of user_name and the support_ticket related db tables or just the support_ticket pages?

Hopefully that makes sense, I just want to say thank you as am beginning to get to grips with the placeholders and prepared statements very slowly

22

Just before you use the variables, so in this case youā€™d want them just before your call to bind_param(). You need to see what the value is when it is being used. (Technically thatā€™s when you call execute(), but as thatā€™s right after in this case itā€™s the same).

No, donā€™t put quotes around the placeholder. One of the good things about prepared statements is you donā€™t need to worry about quotes, embedded quotes, itā€™s all handled for you.

Anywhere that you use the name should really use the id. It doesnā€™t matter for now, itā€™s just a more usual way of linking things. ā€œNormalisationā€ is the term.

23

I put var_dump(); before bind_param but got a error saying Warning : var_dump() expects at least 1 parameter, 0 given in /home/itdonerightco/public_html/account/view-support-ticket.php on line 167

Is it because I have commented out bind_param line?

Iā€™m back to not really understanding the query line again and getting the data for the specific ticket_id and user_name

I keep getting no rows, I amended the query and bind_param to the following

$ticket_id = '';
$username = '';
						
$stmt = $mysqli->prepare("SELECT ticket_id, 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, user_name FROM support_tickets WHERE ticket_id = ? AND user_name = 'ianhaney35new'");
						
$stmt->bind_param("s", $ticket_id);
25

Think I am getting somewhere as sorted the errors on the page with the first query. The updated code I got is below

<?php	
if(isset($_GET['user_name'])){
	$username =$_GET['user_name'];
}
if(isset($_GET['ticket_id'])){
	$ticket_id =$_GET['ticket_id'];
}
				
$stmt = $mysqli->prepare("SELECT ticket_id, 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, user_name FROM support_tickets WHERE ticket_id = '$ticket_id' AND user_name = '$username'");

$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
$row = $result->fetch_assoc();

?>

I am just working the second query now that will retrieve the file_name of each file uploaded to the db table and display the file_name. I am able to get the data but I think itā€™s not getting the file_names for the specific ticket_id

Itā€™s getting the file_name for all tickets and not just the file_names for the specific ticket_id, the code for that query is below

<?php
														
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
}
?>
26

No, itā€™s because you gave var_dump() zero parameters instead of one, exactly as the error message tells you. :slight_smile: You need to put:

var_dump($username);
var_dump($ticket_id);

and I suspect youā€™ve just put

var_dump();

for some reason. You may not be familiar with the function, but you must be aware of echo, and you will be aware you can go on php.net and find all the documentation for these functions.

That may work, but youā€™ve reverted to not using prepared statements - I know you call prepare(), but while you concatenate the strings into the query, especially without doing any kind of checking, it has no real effect. So you need to get your bind_param() working again.

Your query does an inner join based on the username, not the ticket id, so I would presume it would get all files for that user.

27

Ahh ok Iā€™ll concentrate on the first query again and get that right first. I have just amended the coding to the following

$stmt = $mysqli->prepare("SELECT ticket_id, 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, user_name FROM support_tickets WHERE ticket_id = ? AND user_name = ?");

$stmt->bind_param("is", $ticket_id, $username);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
$row = $result->fetch_assoc();

It does seem to be working still and outputting the correct data for that specific ticket_id

Does that query and code look ok or have I still missed something?

28

Just working on the second query again and have amended it but itā€™s just outputting no rows. I put the following query in phpmyadmin and itā€™s outputting the right data

SELECT support_tickets.ticket_id, support_ticket_files.file_name FROM support_ticket_files INNER JOIN support_tickets ON support_ticket_files.ticket_id=support_tickets.ticket_id WHERE support_tickets.ticket_id = '58' AND support_ticket_files.file_name = 'customer-agreement-may-2018.docx'

So in php, I amended the code to be the following

<?php
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 FROM support_ticket_files INNER JOIN support_tickets ON support_ticket_files.ticket_id=support_tickets.ticket_id
WHERE support_tickets.ticket_id = ? AND support_ticket_files.file_name = ?");
$stmt->bind_param("is", $ticket_id, $filename);
$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>

Do I need to alter the code slightly as will be outputting multiple rows as could be more than one file uploaded for the specific ticket_ids

29

It looks OK, and if it recovers the data that it should, then it must be good. I am not a mysqli expert, though, so I canā€™t say whether there are other things. But if itā€™s working, thatā€™s good.

On the second query, isnā€™t it better to retrieve the filename based on the ticket_id rather than the name? Retrieve all files for that ticket, then loop through the results to display them?

30

ok not 100% on how to do that to be honest. I have amended the query to be the following

SELECT support_ticket_files.file_name FROM support_ticket_files INNER JOIN support_tickets ON support_ticket_files.ticket_id=support_tickets.ticket_id
WHERE support_tickets.ticket_id = ?
31

Think I got it sorted now kind of, if there are files for the specific ticket_id, it shows the correct file_name but if there are not any files for the specific ticket_id, it shows no rows and no footer etc.

I think this is where I need to do the loop through the results as mentioned but not 100% on how to do it, the amended code is below

<?php
if(isset($_GET['ticket_id'])){
$ticket_id =$_GET['ticket_id'];
}
if(isset($_GET['file_name'])){
$filename =$_GET['file_name'];
}
?>
<?php
$stmt = $mysqli->prepare("SELECT file_name FROM support_ticket_files WHERE ticket_id = ?");
$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>

Also do I still need to do a JOIN still for retrieval of the data of the file_name for the specific ticket_id or when the front end user opens a new support ticket, do I need to amend the code so the ticket_id is added to the support_ticket_files db table as well?

32

A simple while() loop would suffice:

while $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
}

Not sure what youā€™re driving at in the last paragraph. When the user opens a new support ticket, you (or MySQL, presumably) assign it a new unique id. When they upload a file to it, thatā€™s when you stick it in the support_ticket_files table.

33

Thank you, I managed to add the while loop in earlier today and will be testing it tonight and hopefully the support ticket will add to the support_tickets db table and the files will be added to the support_tickets_files and are added by a unique id

35

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.