james Oduro james Oduro - 5 months ago 9
SQL Question

How to join two tables and return result in php

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

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 
  ";