We help IT Professionals succeed at work.

Problem with join in sql

Ahmad Jaber
Ahmad Jaber asked
on
98 Views
Last Modified: 2019-01-31
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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

Ahmad JaberResponsable

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ahmad JaberResponsable

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

The problem was resolved !

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.