如何在php中连接两个表并返回结果

Question

I have two tables namely: users and messages

I want to JOIN messages table with users table and select users information from users table where the id in users table has NO row in messages table checking with the hash column.

Please Note: user_two column in messages table is the ids from users table

I tried but it return no result. Please help:

<?php 

        //Get the friend a user wants to send message
    if(isset($_POST['recipname']) && !empty($_POST['recipname'])){
        $recipname  =   mysqli_real_escape_string($dbc_conn,htmlentities(trim($_POST['recipname'])));

        $message_group_tatable = "messages";

        $sql    =   "

        SELECT      users.id, users.username,users.FirstName ,
                    users.LastName , users.avatar ,
                    users.cell_group

        FROM        users 
        INNER JOIN  $message_group_tatable 
        ON          $table_name.id=$message_group_tatable.user_two

        WHERE       $message_group_tatable.hash = NULL
        AND         users.id    != $message_group_tatable.user_two
        AND         users.username 
        LIKE        '%$recipname%'   
        LIMIT       6


        ";

        $query  =   mysqli_query($dbc_conn,$sql);
        //die(mysqli_error($dbc_conn));
        if(mysqli_num_rows($query) > 0){
        while($row  =   mysqli_fetch_array($query)){
        $name   =   ucfirst($row['FirstName'])." ".ucfirst($row['LastName']);
        $user_id    =   $row['id'];
        $user_name  =   $row['username'];
        $school     =   $row['cell_group'];
        $avatar     =   $row['avatar'];


        ?>
        <div class="selectmeWrapper this">
        <table class="selectme">
          <tr>
            <td><span class="selectmeavtspan"><img class="selectmeavatar" src="uploaded/<?php echo $avatar; ?>" /></span></td>
            <td><span class="univ"><?php echo $name; ?></span></td>
          </tr>
        </table>

    <span class="uiremovable selected" title="pro/<?php echo $user_name;?>">
        <span> <img class="recipavt" src="uploaded/<?php echo $avatar; ?>" /></span>
        <span class="selectedName">
            <?php echo $name; ?>
        <input type="hidden" autocomplete="off" 
            value="<?php echo $user_name ?>" />

    </span>
    <a href="#" id="<?php echo $user_name?>" class="ulCloseSmall <?php echo "Remove ".$name; ?>"><i class="fa fa-times"></i></a>
    </span>
        </div>
        <?php


        }

        }else{
        echo "<p class='noresult'>No Result Found.</p>";    

        }


        }


?>

Table structure for table messages

        CREATE TABLE IF NOT EXISTS `messages` (
      `user_one` int(11) NOT NULL,
      `user_two` int(11) NOT NULL,
      `hash` int(11) DEFAULT NULL,
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

and Table structure for table users

    CREATE TABLE IF NOT EXISTS `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(64) DEFAULT NULL,
      `FirstName` varchar(32) DEFAULT NULL,
      `LastName` varchar(32) DEFAULT NULL,
      `Email` varchar(64) DEFAULT NULL,
      `Password` varchar(32) DEFAULT NULL,
      `Month` varchar(6) DEFAULT NULL,
      `Day` varchar(6) DEFAULT NULL,
      `Year` varchar(11) DEFAULT NULL,
      `Gender` varchar(6) DEFAULT NULL,
      `cell_group` varchar(100) DEFAULT NULL,
      `active` varchar(11) DEFAULT NULL,
      `avatar` text,

      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

Answer 1

You could use a not in clause

   $sql    =   "
    SELECT      users.id, users.username,users.FirstName ,
                users.LastName , users.avatar ,
                users.cell_group

    FROM        users 
    WHERE       users.id  not in (select distinct user_two from " . $message_group_tatable . " )
    AND         users.username 
    LIKE        concat('%', ". $recipname .", '%')   
    LIMIT       6 
  ";