0

I have two tables that looks something like this (made as example):

Table sales:

| ID |           date           | displayname | status |
| 1  |   2020/08/03 16:25:26    |     Angel   |   OK   |
| 2  |   2020/08/03 16:25:26    |     Angel   |   OK   |
| 3  |   2020/08/03 16:25:26    |     Cabil   |    X   |
| 4  |   2020/08/03 16:25:26    |     Syed    |   OK   |
...

Table users (all of the columns has value, but removed for GDPR reasons):

| ID | displayname | fullname | email |
|  1 |     Angel   |          |       | 
|  2 |     Nico    |          |       | 
|  3 |    Raquie   |          |       | 
|  4 |    Cabil    |          |       | 
|  5 |    Syed     |          |       |
...

I have a PHP script that looks like this:

<?php      
   $query = "SELECT * FROM sales WHERE status='OK' ORDER BY STR_TO_DATE(`date`, '%Y/%m/%d %H:%i:%s') DESC LIMIT 5";
       if ($result = $link->query($query)) {
              $num_rows = 0;
              while ($row = $result->fetch_assoc()) {
                  $num_rows++;

                  echo '<div class="my-box">';
                  echo "{$row['id']}";
                  echo "{$row['date']}";
                  echo "{$row['dbirth']}";
                  echo "{$row['email']}";
                  echo "{$row['displayname']}";
                  echo '</div>';
              }
              $result->free();
          }
      ?>

Now it currently displays each displayname for each sale in echo "{$row['displayname']}";, but insted of the displayname, I want to show the fullname for the user that has the current display name. How can I accomplish this?

Erik Auranaune
  • 1,384
  • 1
  • 12
  • 27

1 Answers1

0

You seem to be looking for a join:

select s.*, u.fullname
from sales s
inner join users u on u.displayname = u.displayname
GMB
  • 216,147
  • 25
  • 84
  • 135