Link to home
Start Free TrialLog in
Avatar of Jaber Ahmad
Jaber AhmadFlag 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:

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

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

SELECT 			R.NIC_Handle, 

FROM 			tb_user_notifications N

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


"); 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";} ?>

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


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 !
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

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

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
User generated image
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Seriously, even in dreams I would not have found!
Thank you very much for your help!

The problem was resolved !