Avatar of Jaber Ahmad
Jaber Ahmad
Flag for Côte d'Ivoire 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
PHPSQL

Avatar of undefined
Last Comment
Member_2_8007715

8/22/2022 - Mon
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
result.png
ASKER CERTIFIED SOLUTION
Chris Stanyon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Member_2_8007715

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

The problem was resolved !
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23