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

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

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

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?

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

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

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

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

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

I have managed to get the files uploaded all ok now in the support_ticket_files db table and the support ticket info is added to the support_tickets db table. the updated code is below

I got the users table that stores the users info and then got the support tickets then the support_ticket_files db table Ahh yeah looks like I got extra brackets not needed, I'll look at that now as that could be the issue why it's not adding the data to the new db table

Ahh ok sorry, the echo of $insert is below INSERT INTO support_tickets (ticket_subject, ticket_message, ticket_status, user_name, user_id) VALUES ('test twelve', 'test twelve two tables files ', 'PENDING SUPPORT', 'ianhaney35new', '180');INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('('customer-agreement-may-2018.docx','ianhaney35new','180'),('IT DONE RIGHT NOTES.docx','ianhaney35new','180')') I guess the id is the best one but guessing I may need to rename it to ticket_id so it matches the column name of the other table. To be honest this is where I struggle as I just want the files uploaded in the new table to be linked to the ticket in the first table so each user can see their own support tickets and the files attached to them if they uploaded any

This is the query for the new table for the file names, it's called support_ticket_files <?php $insert .= "INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('$insertValuesSQL')"; ?> Is that what you meant by showing the whole query?

I can remove the user_name column then and link the new table to the other table using user_id or is ticket_id better. I currently have the id as the primary on the new table and on the other table, the primary key is ticket_id so should I make the id on the new table renamed to ticket_id?

Just done that and it's outputting the following ('customer-agreement-may-2018.docx','ianhaney35new','180'),('IT DONE RIGHT NOTES.docx','ianhaney35new','180') It looks ok to me but unsure why it's not adding the files to the second db table

I have updated the code to insert into two tables but is not working properly. It inserts the data to the first table but the files are not added to the second db table, below is my code <?php $fileNames = array_filter($_FILES['files']['name']); if(!empty($fileNames)){ foreach($_FILES['files']['name'] as $key=>$val){ // File upload path $fileName = basename($_FILES['files']['name'][$key]); $targetFilePath = $targetDir . $fileName; // Check whether file type is valid $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION); if(in_array($fileType, $allowTypes)){ // Upload file to server if(move_uploaded_file($_FILES["files"]["tmp_name"][$key], $targetFilePath)){ // Image db insert sql $insertValuesSQL .= "('".$fileName."','".$username."','".$user_id."'),"; }else{ $errorUpload .= $_FILES['files']['name'][$key].' | '; } }else{ $errorUploadType .= $_FILES['files']['name'][$key].' | '; } } if(!empty($insertValuesSQL)){ $insertValuesSQL = trim($insertValuesSQL, ','); // Insert image file name into database $insert = "INSERT INTO support_tickets (ticket_subject, ticket_message, ticket_status, user_name, user_id) VALUES ('$ticket_status', '$ticket_message', '$ticket_status', '$username', '$user_id');"; $insert .= "INSERT INTO support_ticket_files (file_name, user_name, user_id) VALUES('$insertValuesSQL')"; if(mysqli_multi_query($link, $insert)){ $to = "emailaddress"; $subject = "A new support ticket has been submitted"; $message = " <strong>$username</strong> has just created a support ticket, below is the support ticket <br /><br /> <u>Support Ticket Details</u> <br /><br> <strong>Support Ticket Subject</strong>: $ticket_subject <br/><br><strong>Support Ticket Message</strong>: $ticket_message <p><strong><u>Support Ticket Files</u></strong> <br> <img src='$fileName'> "; // Always set content-type when sending HTML email $headers = "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n"; // More headers $headers .= 'From: <noreply@emailaddress.co.uk>' . "\r\n"; $mail=mail($to,$subject,$message,$headers); $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType; //$statusMsg = "Files are uploaded successfully.".$errorMsg; header("location: support-ticket-confirmation?user=$username"); }else{ $statusMsg = "Sorry, there was an error uploading your file."; } } }else{ $statusMsg = 'Please select files to upload.'; } // Display status message echo $statusMsg; } ?>

Ahh ok got it, think I understand that, I'll give it a go

Store the files uploaded into a seperate table and link to the support tickets so the uploaded files are related to the user who has uploaded them

Have you got a example ginerjm please of the best way to do it as I am not 100% on PHP to be honest and unsure of the way to do it

Can it be done with the file names in just the one db table rather than putting them in a separate table?

I have a hopefully small issue on a form submitting data to the mysql database table and email. The email side works fine as does the adding the data to the database table but if I upload two files, it stores the support ticket twice in the database table where as I want to store just once and the files be stored as a array in the database table. I got the code from the link https://www.codexworld.com/upload-multiple-images-store-in-database-php-mysql/ The code I have is below <?php require_once "registerconfig.php"; if (isset($_POST['submit'])) { // File upload configuration $targetDir = "support-ticket-images/"; $allowTypes = array('pdf','doc','docx','jpg','png','jpeg','gif'); $statusMsg = $errorMsg = $insertValuesSQL = $errorUpload = $errorUploadType = ''; // Escape user inputs for security $ticket_subject = htmlentities($_POST['ticket_subject'], ENT_QUOTES); $ticket_message = strip_tags($_POST['ticket_message'], ENT_QUOTES); $ticket_status ='PENDING SUPPORT'; $username = htmlentities($_SESSION["user_name"], ENT_QUOTES); $user_id = htmlentities($_SESSION["user_id"], ENT_QUOTES); $fileNames = array_filter($_FILES['files']['name']); if(!empty($fileNames)){ foreach($_FILES['files']['name'] as $key=>$val){ // File upload path $fileName = basename($_FILES['files']['name'][$key]); $targetFilePath = $targetDir . $fileName; // Check whether file type is valid $fileType = pathinfo($targetFilePath, PATHINFO_EXTENSION); if(in_array($fileType, $allowTypes)){ // Upload file to server if(move_uploaded_file($_FILES["files"]["tmp_name"][$key], $targetFilePath)){ // Image db insert sql $insertValuesSQL .= "('".$ticket_subject."','".$ticket_message."','".$fileName."','".$ticket_status."','".$username."', '".$user_id."'),"; }else{ $errorUpload .= $_FILES['files']['name'][$key].' | '; } }else{ $errorUploadType .= $_FILES['files']['name'][$key].' | '; } } if(!empty($insertValuesSQL)){ $insertValuesSQL = trim($insertValuesSQL, ','); // Insert image file name into database $insert = $link->query("INSERT INTO DB TABLE NAME (ticket_subject, ticket_message, file_name, ticket_status, user_name, user_id) VALUES $insertValuesSQL"); if($insert){ $to = "emailaddress"; $subject = "A new support ticket has been submitted"; $message = " <strong>$username</strong> has just created a support ticket, below is the support ticket <br /><br /> <u>Support Ticket Details</u> <br /><br> <strong>Support Ticket Subject</strong>: $ticket_subject <br/><br><strong>Support Ticket Message</strong>: $ticket_message <p><strong><u>Support Ticket Files</u></strong> <br> <img src='$fileName'> "; // Always set content-type when sending HTML email $headers = "MIME-Version: 1.0" . "\r\n"; $headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n"; // More headers $headers .= 'From: <noreply@emailaddress>' . "\r\n"; $mail=mail($to,$subject,$message,$headers); $errorUpload = !empty($errorUpload)?'Upload Error: '.trim($errorUpload, ' | '):''; $errorUploadType = !empty($errorUploadType)?'File Type Error: '.trim($errorUploadType, ' | '):''; $errorMsg = !empty($errorUpload)?'<br/>'.$errorUpload.'<br/>'.$errorUploadType:'<br/>'.$errorUploadType; header("location: support-ticket-confirmation?user=$username"); }else{ $statusMsg = "Sorry, there was an error uploading your file."; } } }else{ $statusMsg = 'Please select files to upload.'; } // Display status message echo $statusMsg; } ?> The structure of the db table column is file_name, VARCHAR(255), latin1_swedish_ci, NOT NULL

Just found I can use dataTable for what I need and sort of got it working, can this thread be closed if possible please

Just found I can use dataTable for what I need and sort of got it working, can this thread be closed if possible please

I am in the middle of a project on my own website and want to add a filter on it, the results are displayed in a HTML table and are pulled in from a mysql database using php and want to add a text filter on the site. I want the filter to work by clicking on a status text link such as open, pending or closed and it filters the results that match that on the same page for that relevant text value. I was not sure if it's more a php question or if can be done with ajax/javascript

I am in the middle of a project on my own website and want to add a filter on it, the results are displayed in a HTML table and are pulled in from a mysql database using php and want to add a text filter on the site. I want the filter to work by clicking on a status text link such as open, pending or closed and it filters the results that match that on the same page for that relevant text value. Is that something that can be done using javascript/ajax? Is there any example of the code as been looking online but can't find anything on how to do it