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

I am trying to retrieve filenames that are stored in the database and the actual files stored on my ftp server in a folder and what I am trying to do is display a list of the filenames related to the ticket id and the specific user session but can’t work out how to do it, I am bit new to prepared statements (late to the party on this one I know). My current coding is below. I have been looking on google for examples and looking at php.net etc. but not getting any closed I think

<?php
														$mysqli = new mysqli("localhost", "username", "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 = ? and support_tickets.user_name = '".$_SESSION['user_name']."'");
														$stmt->bind_param("iss", $_POST['ticket_id']);
														$stmt->execute();
														$stmt->store_result();
														if($stmt->num_rows === 0) exit('No rows');
														$stmt->bind_result($ticket_idRow, $filenameRow, $usernameRow); 
														while($stmt->fetch()) {
														  $ticket_ids[] = $ticket_idRow;
														  $filename[] = $filenameRow;
														  $username[] = $usernameRow;
														}
														var_export($ticket_ids);
														$stmt->close();
														?>
                                                        <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>

That code just outputs no rows

You have a few mistakes! This will help you I think : https://stackoverflow.com/a/26826585/12232340

And this one : https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

I originally looked at https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection yesterday and did try the following code going by the example on that site but it just says No rows

<?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 = ? and support_tickets.user_name = '".$_SESSION['user_name']."'");
														$stmt->bind_param("i", $_POST['ticket_id']);
														$stmt->execute();
														$result = $stmt->get_result();
														if($result->num_rows === 0) exit('No rows');
														while($row = $result->fetch_assoc()) {
														$ticket_id[] = $row['ticket_id'];
														$filename[] = $row['file_name'];
														$username[] = $row['user_name'];
														}
														var_export($ticket_id);
														$stmt->close();
														?>

I have just checked the error log on the server and it says the following

PHP Warning: mysqli_stmt::bind_param(): Number of variables doesn’t match number of parameters in prepared statement in /home/itdonerightco/public_html/account/view-support-ticket.php on line 222

Line 222 is 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.user_name=support_ticket_files.user_name
    													WHERE support_tickets.ticket_id = ? and support_tickets.user_name = '".$_SESSION['user_name']."'");

It’s also go the following error

PHP Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in /home/itdonerightco/public_html/account/view-support-ticket.php:226
Stack trace:
#0 {main}
thrown in /home/itdonerightco/public_html/account/view-support-ticket.php on line 226

Line 226 is below

while($row = $result->fetch_assoc()) {

Your where clause is not equal to bind_param.

Change this :

$stmt->bind_param("i", $_POST['ticket_id']);

To :

$stmt->bind_param("is", $_POST['ticket_id'], $_SESSION['user_name']);

And this :

$ticket_id[] = $row['ticket_id'];
														$filename[] = $row['file_name'];

To this:

echo $row['ticket_id'];
														 echo $row['file_name'];

If you want to use values out of while loop then change while :

while($row = $result->fetch_assoc()) {

To this:

$row = $result->fetch_assoc();

And then use like this in html
<?php echo $row['file_name']; ?>

I am on mobile sorry can’t write full codes :frowning:

No worries thank you I done the changes you mentioned but also think my other select script is causing issues as well as the whole side column has disappeared now.

The whole 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;
						}
					?>
<?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, user_name FROM support_tickets WHERE ticket_id = ? AND user_name = '".$_SESSION['user_name']."'");
						
if ($stmt === FALSE) {
    die($mysqli->error);
}
						
						$stmt->bind_param("sss", $_POST['created_at'], $_POST['ticket_timestamp'], $_SESSION['user_name']);
						$stmt->execute();
						$result = $stmt->get_result();
						if($result->num_rows === 0) exit('No rows');
						$row = $result->fetch_assoc();
?>
<?php echo $row['created_at']; ?>
<?php echo $row['ticket_timestamp']; ?>

<?php
														$mysqli = new mysqli("localhost", "user", "dbname", "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 = ? AND support_tickets.user_name = '".$_SESSION['user_name']."'");
														$stmt->bind_param("is", $_POST['ticket_id'], $_SESSION['user_name']);
														$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>

In your first query, you only have one parameter defined, but in your bind_param() you try to pass in three parameters to it. In the second, you only have one parameter defined, but you try to pass two parameters to it.

I notice, though, that you use a parameter for your ticket_id, but then concatenate the username - why not do it consistently and use a parameter for both?

(Incidentally it would be easier to read your code if you didn’t indent it quite so much. I generally use two spaces for each “level” of indent.)

1 Like

Sorry but totally lost me as in the first query, I see three columns I want data from and in the bind_param I see three

In the second query I see two but have updated it to three. Below is the updated code with hopefully correct indentation

<?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, user_name FROM support_tickets WHERE ticket_id = ? AND user_name = '".$_SESSION['user_name']."'");
						
if ($stmt === FALSE) {
    die($mysqli->error);
}
						
$stmt->bind_param("sss", $_POST['created_at'], $_POST['ticket_timestamp'], $_SESSION['user_name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
$row = $result->fetch_assoc();
?>

<?php echo $row['created_at']; ?>
<?php echo $row['ticket_timestamp']; ?>

<?php
  $mysqli = new mysqli("localhost", "itdoneri_user2", "eUw}+8]Pft9[", "itdoneri_itdoneright2");
  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 = ? AND support_tickets.user_name = '".$_SESSION['user_name']."'");
$stmt->bind_param("iss", $_POST['ticket_id'], $_POST['file_name'], ['user_name']);
$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>

I think I am slowly winning as think sorted two issues and just got one last issue which I think is related to the first query by looks of it. The error message is below

Fatal error : Uncaught Error: Call to a member function bind_param() on bool in /home/itdonerightco/public_html/account/view-support-ticket.php:163 Stack trace: #0 {main} thrown in /home/itdonerightco/public_html/account/view-support-ticket.php on line 163

On line 163 is below

$stmt->bind_param("isss", $_POST['ticket_id'], $_POST['created_at'], $_POST['ticket_timestamp'], ['user_name']);

The query line is below

$stmt = $mysqli->prepare("SELECT DATE_FORMAT(ticket_id, 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 = '".$_SESSION['user_name']."'");

“Call … on bool” usually means that your previous statement has failed and returned “false” instead of returning a pointer to a query. So in this case, your prepare has failed, and $stmt contains the Boolean false value.

In your query, the first call to DATE_FORMAT has three parameters, and the second one has two - is that correct? I’ve never used DATE_FORMAT as far as I recall. A quick look at the doc suggests it only has two, so the third would probably be enough to stop the query working, leading to $stmt = false. You can test it easily in phpMyAdmin or similar.

On your line 163, the final value you pass in is in square-brackets as if it’s an array element, but you don’t specify an array name. Again, I don’t know if that matters.

My point about the parameters was based on me not using mysqli - I use PDO, and I use named placeholders. As far as I know (and I may be wrong, it’s happened before…) for each parameter that you supply in bind_param() you need a corresponding ? in your query. For example:

$q = $mysqli->prepare("select name, status from users where userid = ? and status = ? ";)
$q->bind_param("ss", $id, $stat);

and as far as I see it, you only have the single ? parameter. Maybe bind_param() ignores any excess ones.

bind_result is the function that is linked to how many columns you are retrieving, isn’t it?

1 Like

The current error you are getting is a follow-on error. It is not where the actual problem is at. It is an error that is occurring because you don’t have error handling for all the database statements that can fail - connection, query, prepare, and execute, and your code continues to run, producing more errors.

To add error handling for all the mysqli statements that can fail, without having to add program logic for each statement, just use exceptions for errors and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) To enable exceptions for the mysqli extension, add the following line of code before the point where you make your database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

BTW - you should only have one database connection in your code.

I did see a small mistake in the first query and so amended that but still got errors which are below as I also added in the line mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Warning : mysqli_stmt::bind_param(): Number of variables doesn’t match number of parameters in prepared statement in /home/itdonerightco/public_html/account/view-support-ticket.php on line 165

Fatal error : Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement in /home/itdonerightco/public_html/account/view-support-ticket.php:166 Stack trace: #0 /home/itdonerightco/public_html/account/view-support-ticket.php(166): mysqli_stmt->execute() #1 {main} thrown in /home/itdonerightco/public_html/account/view-support-ticket.php on line 166

My updated code for the first query etc. is below

<?php
$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("isss", $ticket_id, $created_at, $ticket_timestamp, $username);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
$row = $result->fetch_assoc();
?>

I’m struggling with this and might have to accept defeat soon as is getting bit too much for me to do I think, just seem to be ending up with more and more errors

You better see a cargo cult prepared statements

This is the error I was referring to. For a prepared statement, you need to pass in a variable (or a reference to a variable, really) for each placeholder in your query. A placeholder in mysqli is a ? symbol. In your query here:

$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 = ?");

you can see that you have two placeholders, for ticket_id and user_name selection, in your WHERE clause. In your bind_param() call, it needs to supply values for those two values, but you’re sending four values, so bind_param() throws a warning and does not execute. Thus, your bind_param() needs to read:

$stmt->bind_param("is", $ticket_id, $username);

and when you execute the query, those values will be passed in, whatever they are at the time of execution.

You don’t need to pass in created_at or ticket_timestamp because those are values that are coming out of your query, along with ticket_id and username - as you passed those last two values in, there’s no real need to retrieve them at all, since you already know what they are. You can test that by dropping the WHERE clause completely, not calling bind_param() at all, and executing the query. You will (if the query is valid) still get results.

I believe this one is coming in because the first bind_param() didn’t execute, because you have the wrong number of parameters in it. This is one of the “follow-on” errors that @mabismad referred to - because you don’t check to see if the prepare() or the bind_param() worked, and just carry on to the next statement, you get cascading error messages. I think that if you fix the bind_param() mismatch issue, this one will go away too.

I think it will drop into place if you persevere with it. The error message quantity isn’t necessarily indicative of having lots of errors, because perhaps one (in this case your bind_param() mistake) is causing the rest of them. Mainly what you’ve done is turn on error reporting, so you can now see them.

There are few things nicer than changing one line of code and seeing it remove a load of error messages.

I understand it bit more but still getting issues

I amended the bind_param line so it’s just got the two variables in it and it just returns no rows so tried amending the where clause line for the user_name = ? to be for the user session but I then end up with the number of variables not matching error again

The original code is below

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

So tried amending it 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 = '".$_SESSION['user_name']."'");

$stmt->bind_param("is", $ticket_id, $username);

but with the amended lines, I get the following errors

Warning : mysqli_stmt::bind_param(): Number of variables doesn’t match number of parameters in prepared statement in /home/itdonerightco/public_html/account/view-support-ticket.php on line 168

Fatal error : Uncaught mysqli_sql_exception: No data supplied for parameters in prepared statement in /home/itdonerightco/public_html/account/view-support-ticket.php:170 Stack trace: #0 /home/itdonerightco/public_html/account/view-support-ticket.php(170): mysqli_stmt->execute() #1 {main} thrown in /home/itdonerightco/public_html/account/view-support-ticket.php on line 170

Well, you will do. You’ve gone back to a situation where you’ve got one placeholder ‘?’, but send two parameters to it. That is never going to work.

Your first version is correct. If it is returning no rows, display the values for the two variables that you are passing in to the bind_param() call and check that they are correct. Then run the query, using those two values, in something like phpMyAdmin and see if it does the same. If it does, you have an issue with the values. If it does not, and returns some rows, then perhaps you could paste some values and some sample data and we can go from there.

What’s the difference between the $username variable, and the $_SESSION['user_name'] variable? Do they contain the same thing? Why use the former when doing a prepared statement, but the latter when concatenating it into the query?

Ahh ok, I understand it bit more now, sorry I thought placeholders were not just the ? but also the next one after the AND but understand it now that the placeholders are just the placeholders so if got just ticket_id = ?. That’s one placeholder and the bind_param should then just have the one variable in it.

I have amended it now to the following and it says no rows

$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 = '".$_SESSION['user_name']."'");

$stmt->bind_param("i", $ticket_id);

Just need to work out now why it’s saying no rows as I would like it to return the created_at value and ticket_timestamp like the following format

Submitted at: created_at value from database table for the specific ticket_id and user_name
Last updated: ticket_timestamp value from database table for the specific ticket_id and user_name

Don’t think there is any difference between username and _SESSION[‘user_name’]. It should be the same thing

I just done the following to try and output the values but still says no rows

$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();
						
echo $ticket_id;
echo $username;

In phpmyadmin, I just ran the query below and it’s returned the data I need to

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'

I just need to get it outputting in php now for the data specific to the ticket_id and user_name

So, you know the query is OK. That means that either $ticket_id, $username or $_SESSION['user_name'] doesn’t have the correct value in it.

The fact that you have put quotes around the value for ticket_id in the working query suggests that you are storing it as a string, so you could perhaps try your bind_param() call with ‘s’ instead of ‘i’ for that parameter. Or try the query in phpMyAdmin without the quotes around ‘58’ and see if it still works.

But you should check whether they are, not presume they “should be”. If they’re the same, why have two variables? var_dump() or echo() will tell you for sure.

From a database point of view, you’d be better off using the user-id instead of the name to link the various tables.