php - 匹配两个数据库表中的 ID 以从一个数据库表中获取用户名

Question

我想将我的 announcements 表中的 user_id 列与我的用户表中的 id 列相匹配。然后我想从 ID 匹配的用户表中获取用户名。

我最初有以下查询

if ($announcements_stmt = $con->prepare("SELECT * FROM announcements"))

我的当前代码出现以下错误..

Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement in  

我知道这意味着什么，但我是否需要添加我的用户表中的每个列表才能使其工作，或者是否有其他方法可以做到这一点？如果我确实需要将所有列作为变量添加到我的 bind_result 中，我将它们放入的顺序是否重要？公告优先还是用户优先，反之亦然？

if ($announcements_stmt = $con->prepare("SELECT * FROM announcements
                        INNER JOIN users
                        ON announcements.user_id = users.id")) {


    $announcements_stmt->execute();
    $announcements_stmt->bind_result($announcements_id, 

$announcements_user_id, $announcements_messages, $announcements_date); 

        if (!$announcements_stmt) {
            throw new Exception($con->error);
        }
        $announcements_stmt->store_result();
         $announcements_result = array();

?>

            Current Announcements
            <table>
                <tr>
                    <th>ID</th>
                    <th>Username</th>
                    <th>Message</th>
                    <th>Date</th>
                </tr>   
<?php
        while ($row = $announcements_stmt->fetch()) {
?>
                <tr>
                    <td><?php echo $announcements_id; ?></td>
                    <td><?php echo $announcements_username; ?></td>
                    <td><?php echo $announcements_messages; ?></td>
                    <td><?php echo $announcements_date; ?></td>
                </tr>   

<?php
        } 
?>

    }

更新..

 if ($announcements_stmt = $con->prepare("SELECT announcements.id, announcements.user_id, announcements.messages, announcements.date, users.username FROM announcements
                        INNER JOIN users
                        ON announcements.user_id = users.id")) {


    $announcements_stmt->execute();
    $announcements_stmt->bind_result($announcements_id, 

$announcements_user_id, $announcements_messages, $announcements_date, $announcements_username); 

        if (!$announcements_stmt) {
            throw new Exception($con->error);
        }
        $announcements_stmt->store_result();
         $announcements_result = array();

?>

            Current Announcements
            <table>
                <tr>
                    <th>ID</th>
                    <th>Username</th>
                    <th>Message</th>
                    <th>Date</th>
                </tr>   
    <?php
            while ($row = $announcements_stmt->fetch()) {
    ?>
                    <tr>
                        <td><?php echo $announcements_id; ?></td>
                        <td><?php echo $announcements_username; ?></td>
                        <td><?php echo $announcements_messages; ?></td>
                        <td><?php echo $announcements_date; ?></td>
                    </tr>   

    <?php
            } 
    ?>

        }
                </table>
    <?php 
            }           
        }

Answer 1

警告表明当您将结果字段绑定(bind)到变量时，变量的数量与结果集中的字段数量不匹配:

$announcements_stmt->bind_result($announcements_id, $announcements_user_id, $announcements_messages, $announcements_date, $announcements_username); 

解决此问题的简单方法是始终在 SELECT 语句中指定字段(仅作为示例):

SELECT t1.id, t1.user_id, t1.messages, t1.date, t2.username

代替:

SELECT *