1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

get data from two tables for specific ticket id and username

Discussion in 'PHP' started by Ian Haney, Mar 18, 2020.

  1. #1
    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 have


    <?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 = 'ticket_id' AND support_tickets.user_name = '".$_SESSION["user_name"]."'";
    $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);
    ?>
    SEMrush
     
    Ian Haney, Mar 18, 2020 IP
    SEMrush
  2. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #2
    I have just managed to solve it by amending 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"]."'
     
    Ian Haney, Mar 18, 2020 IP
  3. wmtips

    wmtips Well-Known Member

    Messages:
    575
    Likes Received:
    56
    Best Answers:
    0
    Trophy Points:
    140
    #3
    Just a remark to your code. I would highly recommend not inserting raw $_GET or any other user input values directly into SQL queries, as they could be used for SQL injection attacks. You need to follow the best practices: the best way is to use parameterized PDO queries instead of mysqli_query, or at least sanitize input (with filter_var, etc.). Even simple
    $id = intval($_GET['ticket_id']);
    PHP:
    is better than inserting raw $_GET value.
     
    wmtips, Mar 20, 2020 IP
    JEET likes this.
  4. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #4
    I have updated the code since to the following but it seems to be retrieving the file_names of each file uploaded instead of just the file_names for each specific ticket_id


    <?php
    $mysqli = new mysqli("localhost", "user", "pass", "dbname");
    if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }
    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
    }
    ?>

    Sorry also how do I put the code in code tags so it's with the black background like yours above and easier to see
     
    Ian Haney, Mar 20, 2020 IP
  5. malky66

    malky66 Acclaimed Member

    Messages:
    3,779
    Likes Received:
    2,156
    Best Answers:
    84
    Trophy Points:
    515
    #5
    Click the insert button on the editor and select code from the pop-up:

    screenshot-forums.digitalpoint.com-2020.03.20-20_31_28.png
     
    malky66, Mar 20, 2020 IP
  6. JEET

    JEET Notable Member

    Messages:
    3,153
    Likes Received:
    318
    Best Answers:
    10
    Trophy Points:
    235
    #6
    Ian Haney

    I think your problem is that you are trying to join tables on wrong column.
    The join should be on ticket_id and not on username

    If you are already storing ticket_id in support_ticket_files table, then try this query below:
    If not, then you will have to make a new column ticket_id in support_ticket_files, fill it with proper data, and then run this query 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.ticket_id=support_ticket_files.ticket_id
    WHERE
    support_tickets.ticket_id ='$ticket_id' AND
    support_tickets.user_name ='$username'");
     
    JEET, Mar 20, 2020 IP
  7. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #7
    I have updated the code to the above and seems ok for when there are files for the specific ticket_id but when there are no files, it says no rows which is ok but the rest of the site such as the footer does not show. The updated 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;
                                                            }
                                                            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.ticket_id=support_ticket_files.ticket_id
                                                            WHERE
                                                            support_tickets.ticket_id ='$ticket_id' AND
                                                            support_tickets.user_name ='$username'");
                                                            //$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>
    PHP:
     
    Ian Haney, Mar 21, 2020 IP
  8. wmtips

    wmtips Well-Known Member

    Messages:
    575
    Likes Received:
    56
    Best Answers:
    0
    Trophy Points:
    140
    #8
    1. Using $mysqli->prepare is the move to the right direction, but it is not complete. Passing SQL as ready-to-use string like you do has no difference to mysqli_query and doesn't prevent you from the possible SQL injection. For example, it seems that with this simple input your SQL will return all tickets, just insert this test line at the beginning of your script and check the results:
    $_GET['ticket_id'] = "0' or '1";
    PHP:
    For the real parameterized query check the examples in the documentation for mysqli_prepare, your SQL should contain placeholders (?) instead of values and param values should be passed via bind_param.
    2. You are using exit('No rows'); which stops script execution and footer output. You need to change your logic:
    
    if($result->num_rows > 0)
    {
    //display results
    }
    else
    echo 'No rows';
    
    PHP:
     
    wmtips, Mar 21, 2020 IP
  9. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #9
    Oh right ok, I have updated the code to hopefully be better and use placeholders and bind_param and got the footer output now if there are no file attachments but got one issue, I have put the error message below

    Notice: Undefined index: file_name in /home/itdonerightco/public_html/account/view-support-ticket.php on line 245

    The updated 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;
    
    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.ticket_id=support_ticket_files.ticket_id WHERE support_tickets.ticket_id = ? AND support_tickets.user_name = ?");
    
    $stmt->bind_param("is", $ticket_id, $username);
    $stmt->execute();
    $result = $stmt->get_result();
    if($result->num_rows > 0) {
    ?>
    
    <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 } else
    echo 'No Files';
    ?>
    PHP:
    I was bit unsure where to put the line
    $_GET['ticket_id'] = "0' or '1";
    PHP:
     
    Ian Haney, Mar 21, 2020 IP
  10. JEET

    JEET Notable Member

    Messages:
    3,153
    Likes Received:
    318
    Best Answers:
    10
    Trophy Points:
    235
    #10
    Try this to remove the error you are getting 'file_name' is undefined.

    if($result->num_rows > 0){
    while($row=$result->fetch_array(MYSQLI_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
    }//while ends here
    }else{
    echo 'No Files';
    }
    ?>
     
    JEET, Mar 21, 2020 IP
  11. Ian Haney

    Ian Haney Member

    Messages:
    124
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    33
    #11
    Thank you so much, that has solved the issue

    Thank you to everyone who has replied and helped with the coding, really appreciate it
     
    Ian Haney, Mar 21, 2020 IP
    JEET likes this.
  12. JEET

    JEET Notable Member

    Messages:
    3,153
    Likes Received:
    318
    Best Answers:
    10
    Trophy Points:
    235
    #12
    No problem, glad to help :)
     
    JEET, Mar 21, 2020 IP