mysqli_stmt从3个表中获取有限的数据

Question

I'm building a simple message board and need to get information from 3 different tables to display the main summary page. I'm using mysqli_stmt protocol and can't figure out how to get the first message and author name for each discussion, since all sql queries need to be closed before another is prepared.

Here is a simplified version of the code I run to create my database:

CREATE TABLE `users` (
`id` INT(25) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR(65) NOT NULL
);
CREATE TABLE `discussions` (
`id` INT(25) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
`subject` VARCHAR(120) NOT NULL ,
`last_author` INT(25) NOT NULL
);
CREATE TABLE `messages` (
`id` INT(25) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
`discussion` INT(25) NOT NULL ,
`author` INT(25) NOT NULL ,
`message` TEXT
);

Is it possible to form a query that will select all discussions, and for each discussion, select the subject, last author's name, and the most recent message text?

I can use this code to display all the subjects of each discussion:

<?php
$sqlcmd = "SELECT * FROM _discussions";
$get = mysqli_prepare($sqlConnection,$sqlcmd);
mysqli_stmt_execute($get);
mysqli_stmt_bind_result($get, $id, $subject, $last_author);

while(mysqli_stmt_fetch($get)) : ?>

    <li><?=$subject?></li>

<?php endwhile; 
mysqli_stmt_close($get);
?>

but how do I get the author name and message if I can't initiate another sql query within the loop?

Answer 1

Maybe it isn't possible.. I figured out another way to implement it though.

I can get the most recent discussion id with the MAX query, then use a standard for loop to get all the information I need based on that id. With this implementation, I can even drop the last_author field from the discussion table.

This code is not simplified and includes error checks. I'm sure it could be refactored a bit.

<?php 

//get LAST discussion
$sqlcmd = "SELECT MAX(_id) FROM _discussions";
$get = mysqli_prepare($app->sql,$sqlcmd);
if(!$get) $app->log_sqlError();
if(!mysqli_stmt_execute($get)) $app->log_sqlError();
if(!mysqli_stmt_bind_result($get,$last_id)) $app->log_sqlError();
if(mysqli_stmt_fetch($get)==FALSE) $app->log_sqlError();
if(!mysqli_stmt_close($get)) $app->log_sqlError();

$app->log('last discussion id: '.$last_id);
?>
<ul>
<?php for($i=$last_id;$i>0;$i--) :

    //get DISCUSSION
    $sqlcmd = "SELECT * FROM _discussions WHERE _id=".$i;
    $get = mysqli_prepare($app->sql,$sqlcmd);
    if(!$get) $app->log_sqlError();
    if(!mysqli_stmt_execute($get)) $app->log_sqlError();
    if(!mysqli_stmt_bind_result($get,$id,$subject,$original_author,$created,$updated))
        $app->log_sqlError();

    //IMPORTANT - move on if the discussion was not found
    if(mysqli_stmt_fetch($get)==FALSE) $id=0;
    if(!mysqli_stmt_close($get)) $app->log_sqlError();
    if($id!=0) :

    //get last MESSAGE id
    $get = mysqli_prepare($app->sql,"SELECT MAX(_id) FROM _messages WHERE _discussion=".$i);
    if(!$get) $app->log_sqlError();
    if(!mysqli_stmt_execute($get)) $app->log_sqlError();
    if(!mysqli_stmt_bind_result($get,$message_id))
        $app->log_sqlError();
    if(mysqli_stmt_fetch($get)==FALSE) $app->log_sqlError();
    if(!mysqli_stmt_close($get)) $app->log_sqlError();

    //get last MESSAGE
    $get = mysqli_prepare($app->sql,"SELECT * FROM _messages WHERE _id=".$message_id);
    if(!$get) $app->log_sqlError();
    if(!mysqli_stmt_execute($get)) $app->log_sqlError();
    if(!mysqli_stmt_bind_result($get,$m_id,$discussion_id,$author,$text,$created_time))
        $app->log_sqlError();
    if(mysqli_stmt_fetch($get)==FALSE) $app->log_sqlError();
    if(!mysqli_stmt_close($get)) $app->log_sqlError();

    //get AUTHOR
    $get = mysqli_prepare($app->sql,"SELECT _name FROM _users WHERE _id=".$author);
    if(!$get) $app->log_sqlError();
    if(!mysqli_stmt_execute($get)) $app->log_sqlError();
    if(!mysqli_stmt_bind_result($get,$name))
        $app->log_sqlError();
    if(mysqli_stmt_fetch($get)==FALSE) $app->log_sqlError();
    if(!mysqli_stmt_close($get)) $app->log_sqlError();
    ?>

    <li><p><?=$subject?> | <?=$name?> | <?=$text?> | <?=$created_time?></p></li>

<?php endif; endfor; ?>
</ul>

That log_sqlError function looks something like this:

function log_sqlError(){
    print_r('errno: '.$this->sql->errno.', error: '.$this->sql->error);
}