phpmyadmin Question posted in
The official documentation can be found here.

Join two tables using php – Phpmyadmin

I have no idea with joins and I am really having a trouble getting the logic. Can anyone please help me?

Here is my table Announcements:

AnnouncementID      Subject        Header      Status
---------------------------------------------------
1                    Peter        Header 2     Publish
2                     2x2         Header 3     Draft
3                 Resignation     Header 4     Publish

And here is another table ReadAnnouncements:

AnnouncementID      Username      Status
---------------------------------------------
1                    User 1        Read
2                    User 2        Read
2                    User 3        Read

I want my result to be

AnnouncementID      Username      Status    Header       Subject
---------------------------------------------------------------
1                    User 1        Read     Peter        Header 2
2                    User 2        Read     2x2          Header 3
2                    User 3        Read     2x2          Header 3

Please teach me how I am really confused been trying this for two days already.

            <?php 
               $sql=" SELECT a.AnnouncementID,a.Created,r.Username,a.Status,a.Header,a.Body from Announcements a join ReadAnnouncements r using(AnnouncementID) WHERE a.Status = 'Publish'";
               $result = mysqli_query( $conn,$sql);

                 while($rows = mysqli_fetch_array($result)){
                  $time = date('h:i:s a',strtotime($rows['Created']));
                  $date = date('Y-m-d',strtotime($rows['Created']));
                    if($rows['ReadStatus'] == 'Unread'){
                    echo '
                    <tr class="'.$rows['Status'].'clickable-row" >
                      <strong><td class="view-message  dont-show"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none " class="text-dark"><div>'.$rows['Header'].'</div></a></td>
                      <td class="view-message "><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark" ><div>'.substr($rows['Body'],0,90).'</div></a></td>
                      <!--<td class="view-message  inbox-small-cells"><i class="fa fa-paperclip"></i></td>-->
                      <td class="view-message  text-right"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark"><div><h6>'.$time.''.'<br>'.''.$date.'</h6></div></a></td></strong></tr>                                        
                    ';                                
                    }else{
                       echo '<strong>
                    <tr class="'.$rows['Status'].'clickable-row" >
                      <strong><td class="view-message  dont-show"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none " class="text-dark"><div>'.$rows['Header'].'</div></a></td>
                      <td class="view-message "><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark" ><div>'.substr($rows['Body'],0,90).'</div></a></td>
                      <!--<td class="view-message  inbox-small-cells"><i class="fa fa-paperclip"></i></td>-->
                      <td class="view-message  text-right"><a href="ViewAnnouncement.php?view_id='.$rows['AnnouncementID'].'" style="text-decoration: none" class="text-dark"><div><h6>'.$time.''.'<br>'.''.$date.'</h6></div></a></td></strong></tr>                                        
                    </strong>';                             
                    }

                 } 
              ?>

I want to select all rows from table announcements that are only Published and classify them if they are read or unread based on username and announcement id.

Tags:

4

Answers


  1. 0

    You can use the below query to get the result.

    select a.AnnouncementID,r.Username,r.Status,a.Header,a.Subject
from Announcements a
join ReadAnnouncements r on r.AnnouncementID=a.AnnouncementID
    Login or Signup to reply.
  2. 0

    Joins are pretty easy, check this explanation.
    In your case, you can do something like this:

    SELECT A.AnnouncementID, A.Username, R.Status, A.Header, A.Subject FROM Announcements A join ReadAnnouncements R USING(AnnouncementID)
    Login or Signup to reply.
  3. 0

    You can use inner join. The INNER JOIN keyword selects records that have matching values in both tables.

     SELECT a.AnnouncementID,r.Username,r.Status,a.Header,a.Subject
    from Announcements a
    join ReadAnnouncements r using(AnnouncementID)
    Login or Signup to reply.
  4. 0

    Use JOIN clause to combine rows from two or more tables in a database, based on a related column between them ( in your case, AnnouncementID ).

    When combine data from 2 tables, you have a few combinations possible:

    (INNER) JOIN: Returns records that have matching values in both tables
    LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
    RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
    FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
    (source: https://www.w3schools.com/sql/sql_join.asp)

    Using your database schema, you should use:

    select * from Announcements as A INNER JOIN ReadAnnouncements as RA ON A.AnnouncementID RA.AnnouncementID

    You don’t mention which DBMS you are using, so SQL query above may differ.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search