asked on

Problem with join in sql

Hello everyone !

I am having trouble with my join request and are asking for your help.
Indeed, I have two tables:

tb_user_notifications
NumId   | Date   | Sujet         | Message 
----------------------------------------------
1       | NULL   | Sujet 1       | Message 1
2       | NULL   | Sujet 2       | Message 2
3       | NULL   | Sujet 3       | Message 3
4       | NULL   | Sujet 4       | Message 4
5       | NULL   | Sujet 5       | Message 5
6       | NULL   | Sujet 6       | Message 6


tb_user_notifications_recap
NumId   | NIC_Handle   | Notification
----------------------------------------------
1       | JS38579      | 4
2       | JS38579      | 2
3       | JA90717      | 5
4       | JA90717      | 3
5       | TR22377      | 3
6       | TR22377      | 4
7       | TR22377      | 5
8       | TR22377      | 6
9       | TR22377      | 2
10      | TR22377      | 1
11      | JS38579      | 1
12      | JS38579      | 6

Open in new window

With this query, I would like to display in my table, all the notifications of tb_user_notifications considering that:
If the NIC_Handle corresponding to the username session ($_SESSION_NICKHANDLE) exists in the tb_user_notifications_recap table, then the button displays "fa fa-check txt_vert", otherwise it displays "fa fa-eye txt_bleu"
My example below only shows only "fa fa-check txt_vert" and omits unread notifications.

<table class="table table-striped table-hover table-bordered table-sm" id="Tableau_1-">
  <thead class="thead-dark">
    <tr>
      <th align="center" width="120">Date</th>
      <th><?php echo $MES_NOTIFICATIONS; ?></th>
      <th align="center" width="50"><i class="fa fa-comments"></i></th>
    </tr>
  </thead>
  <tbody>

<?php
try{$MyPDO=$pdo->query("
SELECT 			R.NIC_Handle, 
				R.Notification, 
				
				N.NumId, 
				N.Date, 
				N.Sujet, 
				N.Message

FROM 			tb_user_notifications N

LEFT JOIN 		tb_user_notifications_recap R ON N.NumId = R.Notification 

WHERE 			R.NIC_Handle = '".$_SESSION_NICHANDLE."' OR R.NIC_Handle IS NULL

ORDER BY 		N.Date DESC
"); while($data=$MyPDO->fetch(PDO::FETCH_ASSOC)){
?>

<?php if($data["Notification"] == $data["NumId"] AND $data["NIC_Handle"] == $_SESSION_NICHANDLE ) {$ICO = "fa fa-check txt_vert";} else {$ICO = "fa fa-eye txt_bleu";} ?>

    <tr>
      <td><div style="padding:5px"><?php echo date("$DATE_SQL", strtotime($data["Date"])); ?></div></td>
      <td><div style="padding:5px"><?php echo $data["Sujet"]; ?></div></td>
      <td align="center"><button class="border btn btn-light" onClick="location.href='/my/notifications-view.php?page=<?php echo $data["NumId"]; ?>';" ><i class="<?php echo $ICO; ?>"></i></button></td></tr>

<?php }}catch(PDOException $e){echo "<div class='alert alert-danger'>".$e->getMessage()."</div>";} ?>

  </tbody>
</table>

Open in new window

In this exemple, my username is ($_SESSION_NICKHANDLE) is JA90717
This is what I see in my webpage :
Sujet 5
Sujet 3

Dans for the username JS38579, he see :
Sujet 6
Sujet 4
Sujet 2
Sujet 1

Can you please help me solve this problem?
Thank you very much !
Jaber

Chris Stanyon

Your query is filtering on the $_SESSION_NICHANDLE value, so your IF statement will always return true (you're only selecting records that match $_SESSION_NICHANDLE). You don't need to be filtering the query - just select all records, and then run your IF. There's also no need to check if NumId and Notification match - they will - the records are joined on those columns. It's probably also cleaner to do your IF check using the ternary operator:

<?php $ICO = ( $data["NIC_Handle"] == $_SESSION_NICHANDLE ) ? "fa fa-check txt_vert" : "fa fa-eye txt_bleu"; ?>

Open in new window

Member_2_8007715

Hello and thank you for taking care of my problem.
If I understand correctly, a standard query will solve the problem, but when I do it, I still do not have the desired result.

I change in the LEFT JOIN, I made a GROUP BY, I try everything I know and can do but to no avail, still no satisfactory result.

If I select everything from tb_user_notification_recap (NumId, NIC_Handle, Notification), I would not have the subject to display it in the table and would still have to make a WHERE NIC_Handle = $_SESSION_NICHANDLE in order to display only the notifications read.

I do not know where I put my head!

Here is what I must have as a result

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_8007715

Seriously, even in dreams I would not have found!
Thank you very much for your help!

The problem was resolved !