PDO，从两个表中获取内容foreach语句 [英] PDO, fetching content from two tables foreach statement

问题描述

这似乎是重复的，但是我单击了所有按钮并进行了尝试，但它无法解决，因此我想让id发布我的代码并寻求帮助！

this may seem like a duplicate but I have clicked on all the ones and tried it but it won't fix it so I thought id post my code and ask for help!

我正在尝试将数据回显到表中，到目前为止，唯一有效的方法是答复和主题，但我无法弄清楚为什么其他方法不起作用，并且如上所述，我已经尝试了所有PDO帖子但他们都没有解决此问题，因此请您帮忙，因为我对PDO还是比较陌生，所以希望能够看到我出了什么问题.

I'm trying to echo data into a table and so far the only ones that work are the replies and subject and I can't figure out why the others won't work and as stated above I have tried all PDO posts but none of them have fixed it so please can you help because I'm relatively new to PDO so want to be able to see where I went wrong.

我希望它做的是票证的主题，日期，发布者以及它是否处于活动状态，但仅显示答复和主题，因此我想知道为什么，因为它使我感到困惑.

What i'm expecting it to do is how the subject of the ticket, the date, the publisher and whether its active but only the replies and subject show so i want to know why because its confusing me.

<table class="ui single line table">
<thead>
<tr>
    <th>Subject</th>
    <th>Created</th>
    <th>By</th>
    <th>Replies</th>
    <th>Status</th>
</tr>
</thead>
<tbody>
<?php

/* Ticket Info */
$stmt = $dbh->prepare("SELECT * FROM support_tickets WHERE username = :username ORDER BY id DESC");
$stmt->bindParam(':username', $userName);
$stmt->execute();
$tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($tickets as $myticket)
{
    $ticket_id = $myticket['id'];
    $stmt = $dbh->prepare("SELECT * FROM support_messages WHERE on_ticket = :on_ticket");
    $stmt->bindParam(':on_ticket', $ticket_id);
    $stmt->execute();
    $getReplies = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $replyNo = count($getReplies);
    $simpleHash = sha1($ticket_id . $companyName);

    echo '<tr>';
    echo '<td><a href="index.php?t=' . base64_encode($myticket['id']) . '&h=' . $simpleHash . '">' . $myticket['subject'] . '</a></td><td>' . $myTicket[0]['date'] . '</td><td>' . $tickets[0]['from_name'] . '</td> <td>' . $replyNo . '</td> <td>' . $myTicket['status'] . '</td>';
    echo '</tr>';
}

?>
</tbody>
</table>

推荐答案

这是我更新的解决方案.对于错误报告和异常处理，我建议您阅读此和

Here is my updated solution. For error reporting and exception handling I recommend you to read this and this.

<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'yourDb');
define('USERNAME', 'yourDbUsername');
define('PASSWORD', 'yourDbPassword');
define('CHARSET', 'utf8');

/*
 * Error reporting. Also, define an error handler, an exception handler and, eventually, 
 * a shutdown handler function to handle the raised errors and exceptions correspondingly.
 * 
 * @link http://php.net/manual/en/function.error-reporting.php
 * @link http://php.net/manual/en/function.set-error-handler.php
 * @link http://php.net/manual/en/function.set-exception-handler.php
 * @link http://php.net/manual/en/function.register-shutdown-function.php
 */
error_reporting(E_ALL);
ini_set('display_errors', 1); // SET IT TO 0 ON A LIVE SERVER!

/*
 * Create a PDO instance as db connection to db.
 * 
 * @link http://php.net/manual/en/class.pdo.php
 * @link http://php.net/manual/en/pdo.constants.php
 * @link http://php.net/manual/en/pdo.error-handling.php
 * @link http://php.net/manual/en/pdo.connections.php
 */
$connection = new PDO(
        sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s', HOST, PORT, DATABASE, CHARSET)
        , USERNAME
        , PASSWORD
        , [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => FALSE,
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
);

/*
 * Read needed variables (from HTTP POST or GET, for example).
 */
$userName = 'Example User';
$companyName = 'Example Co.';


/*
 * The SQL statement to be prepared. Notice the so-called named markers.
 * They will be replaced later with the corresponding values from the
 * bindings array when using PDOStatement::bindValue.
 * 
 * When using named markers, the bindings array will be an associative
 * array, with the key names corresponding to the named markers from
 * the sql statement.
 * 
 * You can also use question mark markers. In this case, the bindings 
 * array will be an indexed array, with keys beginning from 1 (not 0).
 * Each array key corresponds to the position of the marker in the sql 
 * statement.
 * 
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$sql = 'SELECT 
            tic.*,
            (
                SELECT COUNT(*) 
                FROM support_messages 
                WHERE on_ticket = tic.id 
            ) AS numberOfReplies 
        FROM support_tickets AS tic 
        WHERE tic.username = :username 
        ORDER BY tic.id DESC';

/*
 * The bindings array, mapping the named markers from the sql
 * statement to the corresponding values. It will be directly 
 * passed as argument to the PDOStatement::execute method.
 * 
 * @link http://php.net/manual/en/pdostatement.execute.php
 */
$bindings = [
    ':username' => $userName,
];

/*
 * Prepare the sql statement for execution and return a statement object.
 * 
 * @link http://php.net/manual/en/pdo.prepare.php
 */
$statement = $connection->prepare($sql);

/*
 * Execute the prepared statement. Because the bindings array
 * is directly passed as argument, there is no need to use any
 * binding method for each sql statement's marker (like
 * PDOStatement::bindParam or PDOStatement::bindValue).
 * 
 * @link http://php.net/manual/en/pdostatement.execute.php
 */
$statement->execute($bindings);

/*
 * Fetch tickets (all at once) in an array.
 * 
 * @link http://php.net/manual/en/pdostatement.fetchall.php
 */
$tickets = $statement->fetchAll(PDO::FETCH_ASSOC);

/*
 * Close the prepared statement.
 * 
 * @link http://php.net/manual/en/pdo.connections.php Example #3 Closing a connection.
 */
$statement = NULL;

/*
 * Close the previously opened database connection.
 * 
 * @link http://php.net/manual/en/pdo.connections.php Example #3 Closing a connection.
 */
$connection = NULL;
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>STO answer</title>
    </head>
    <body>
        <table class="ui single line table">
            <thead>
                <tr>
                    <th>Subject</th>
                    <th>Created</th>
                    <th>By</th>
                    <th>Replies</th>
                    <th>Status</th>
                </tr>
            </thead>
            <tbody>
                <?php
                foreach ($tickets as $ticket) {
                    $ticketId = $ticket['id'];
                    $ticketSubject = $ticket['subject'];
                    $ticketDate = $ticket['date'];
                    $ticketPublisher = $ticket['from_name'];
                    $ticketStatus = $ticket['status'];
                    $ticketNumberOfReplies = $ticket['numberOfReplies'];

                    $hash = sha1($ticketId . $companyName);
                    $encodedTicketId = base64_encode($ticketId);

                    $url = 'index.php?t=' . $encodedTicketId . '&h=' . $hash;
                    ?>
                    <tr>
                        <td>
                            <a href="<?php echo $url; ?>">
                                <?php echo $ticketSubject; ?>
                            </a>
                        </td>
                        <td>
                            <?php echo $ticketDate; ?>
                        </td>
                        <td>
                            <?php echo $ticketPublisher; ?>
                        </td>
                        <td>
                            <?php echo $ticketNumberOfReplies; ?>
                        </td>
                        <td>
                            <?php echo $ticketStatus; ?>
                        </td>
                    </tr>
                    <?php
                }
                ?>
            </tbody>
        </table>
    </body>
</html>

这篇关于PDO，从两个表中获取内容foreach语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！