troubleshooting Question

Problem with join in sql

Avatar of Ahmad Jaber
Ahmad JaberFlag for Côte d'Ivoire asked on
PHPSQL
4 Comments1 Solution108 ViewsLast Modified:
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

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>

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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros