CODEWITHSUNDEEP
phpmysqldatabase
Tomble
Tomble

Reputation: 51

Displaying a result from SQL inner join table on php page

So i have two tables in my database:

1

Users:

userID usertype username

2

Ticket:

ticketID userID ticketMessage

My goal is to echo with php the username from the USER table where the USERID from ticket = the same USERID as the one in the USER table.

Here is my code so far:

<?PHP
    if ($result['admin'] == 1) { 

        $sql = "SELECT * FROM ticket ORDER BY ticketDateTime DESC";
                $res = do_sql($sql);

                if ($res->rowCount() == 0) {
                echo "You have 0 Tickets, you haven't created any tickets!";

                } else {
                ?>
        <table class="table-container">
                    <thead>
                        <tr>
                            <th id="table-subject">Subject</th>
                            <th>Id</th>
                            <th>Created</th>
                            <th>User</th>
                            <th>Status</th>
                        </tr>
                    </thead>
                    <tbody>
                <?php
                while ($row = $res->fetch(PDO::FETCH_ASSOC)) {

                ?>
                        <tr>
                            <td>
                            <a id="ticket-subject" href="single-ticket-view.php?page=singletickview&ticketID=<?php echo $row['ticketID'];?>">
                            <?php
                            echo $row['ticketSubject'];?>
                            </a>
                            </td>
                            <td><?php 
                            echo sprintf('%04d', $row['ticketID']); ?>
                            </td>

                            <td><?php
                            $a_date = strtotime($row['ticketDateTime']);
                            $formatted_date = date( 'd/m/Y', $a_date );
                            echo $formatted_date; ?>
                            </td>

                            <td>
                            <?php
$sql2 = "SELECT users.username FROM users INNER JOIN ticket ON users.userID = '" . $row['userID'] . "'";
$res2 = do_sql($sql2);
    if ($res2->rowCount() == 1) {
        echo $res2['username'];
    }
                                ?>

                            </td>

                            <td>
                            <?php if ($row['is_active'] == 1)  {?>
                                <div class="status" id="open">Open</div>
                            <?php } else { ?>
                                <div class="status" id='closed'>Closed</div> <?php } ?>
                            </td>
                        </tr>
                    </tbody>
                <?php
                }}?>
            </table>

My problem here is that i cant figure out how to echo the username on line 60, i think it has somthing to do with my SQL query. Here is my SQL query and php code from line 60 in above section of code - The problem 100% lies within this section, Maybe to do with Query and improper way of using the INNER JOIN. here it is again:

                                <td>
<?php
$sql2 = "SELECT users.username FROM users INNER JOIN ticket ON users.userID = '" . $row['userID'] . "'";
$res2 = do_sql($sql2);
    if ($res2->rowCount() == 1) {
        echo $res2['username'];
    }
?>

                            </td>

$row['userID'] = the userID from table ticket which happens to be 11 in this instance. I went to the Run SQL query in PHP my admin and ran the query:

SELECT users.username FROM users INNER JOIN ticket ON users.userID = 11

which returned the correct result i am looking for, so i have no idea why it will not echo on my PHP webpage. Any help would be amazing, thanks!

EDIT:

I forgot to show my do_sql(); function, here it is: 

function do_sql($sql_string) {
$connection = new PDO("mysql:host=localhost;dbname=secret", 'root', '');
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
    $row_result = $connection->query($sql_string);
} catch (PDOException $Exception) {
    echo $sql_string . '</br>';
    echo $Exception;
    exit();
}

return $row_result;

}

Upvotes: 2

Views: 6051

Answers (4)

Musa
Musa

Reputation: 426

You only need one SQL query to get all the data that you need. 

SELECT Ticket.*, User.username FROM Ticket INNER JOIN User ON User.userid = Ticket.userID;

This will select all the ticket data, along with the username of the user that the ticket belongs to. Usage example:

echo $data['username'] . ' has a ticket with the following message: ' . $data['ticketMessage'];

In the future, I recommend you to prefix your column names to prevent confusion with queries like these. (e.g. user_name, user_id, ticket_message, ticket_id).

You can read up more about INNER JOIN here.

Upvotes: 0

JustMeNad
JustMeNad

Reputation: 55

that should be SELECT users.username FROM users INNER JOIN ticket ON users.userID = ticket.USERID hope it helps, and if you are selecting an specific record add WHERE clause in your query

Upvotes: 0

B. Desai
B. Desai

Reputation: 16436

Instead of call query multiple times you can Join query and get user name with single query execution. change this line

 $sql = "SELECT * FROM ticket ORDER BY ticketDateTime DESC";

to

$sql = "SELECT ticket.*,Users.username FROM ticket LEFT JOIN Users ON ticket.userID = Users.userID ORDER BY ticketDateTime DESC";

You can get user name by $row['username']; in loop

Upvotes: 3

neil
neil

Reputation: 429

If you simply perform the join on the initial query then you'll have access to all of the fields you require without running a second query.

SELECT * FROM ticket INNER JOIN users ON users.userID = ticket.userID ORDER BY ticketDateTime DESC

Upvotes: 1

Related Questions