Compare 2 mysql column values from different tables

Question

I have 2 tables :

accounts

lanes

lanes contains 1 column "boss_id" which is an INT between 1 and 12

accounts contains various columns including username, password and also a boss_id column

Im trying to use PHP to display ALL items from the lanes table, which is working great! But, what i need to do is also show if a user from "accounts" also shares or has the same boss_id and displays a different output :

Regular output (no matching account)

foreach ($boss_id as $boss) {
    echo "Lane " . $boss . "<br>;
}

If there is a matching account show this instead

echo "Lane booked by " . $username. "<br>;

I've spent a lot of time on this, and really need some help as i cant seem to figure it out.

Any help would be awesome! If you need anymore information, please ask!

Thanks in advance E

This is what i have so far (variables may differ in name but you should get the idea!)

$db_host     = "localhost";
  $db_username = "root";
  $db_password = "";

  //connect to mysqli database (Host/Username/Password)
  $connection = mysqli_connect($db_host, $db_username, $db_password) or die("Error " . mysqli_error());

  //select MySQLi dabatase table
  $db = mysqli_select_db($connection, "archery-booking") or die("Error " . mysqli_error());

  // get ALL lanes from database table "lanes"
  $sql = mysqli_query($connection, "SELECT * FROM lanes");
  while($row = mysqli_fetch_array($sql)) {
   $bossid[] = $row['bossid'];
  }

  // save each result as a variable
  foreach ($bossid as $compare) {
    $compare = $compare;
  }

  // get ALL BOOKED lanes from database table "accounts"
  $sqluser = mysqli_query($connection, "SELECT * FROM accounts WHERE boss_id = '".$compare."' ");
  while($row = mysqli_fetch_array($sqluser)) {
   $bookedboss[] = $row['boss_id'];
  }

Updates

So i have no made a couple of changes, and getting the information for both lane.boss_id and also accounts.boss_id

How ever, i still need to do the following :

If lane.boss_id doesnt have an account attached to it, then do this, if it does have an account then do that.

What would be the best way of checking this condition? I was going to use an if/else on "username" but i got an error about illegal string offsets....here's my code so far!

$db_host     = "localhost";
  $db_username = "root";
  $db_password = "";

  //connect to mysqli database (Host/Username/Password)
  $connection = mysqli_connect($db_host, $db_username, $db_password) or die("Error " . mysqli_error());

  //select MySQLi dabatase table
  $db = mysqli_select_db($connection, "archery-booking") or die("Error " . mysqli_error());

  // get ALL lanes from database table "lanes"
  $sql = mysqli_query($connection, "SELECT l.*, a.username FROM lanes l LEFT JOIN accounts a ON a.boss_id = l.bossid");
    while($row = mysqli_fetch_array($sql)) {
     $bossid[] = $row['bossid']['username'];
     var_dump($bossid);
    }

UPDATE 3

I can't thankyou all enough for your help, we've really made progress!! :D

I have 1 final request though and i hope you guys can help as well as you have already! :)

So, in my script i have the following :

if (isset($uname)) {
       echo "<div class='col-xl-3 col-lg-3 col-md-6 col-sm-6 col-xs-6'>
                  <div class='card bg-normal'>
                  <div class='card-header day1'>
                  <p>Lane booked from 6.00pm</p>
                  </div>
                  <div class='card-body'>";
        echo "<h2 class='lane-title taken'>Lane " . $bossid . "</h2>";
        echo "<p class='lead'>Booked by " . $uname . "</p>";
        echo "</div>
                  </div>
                  </div>";
     } else {
       echo "<div class='col-xl-3 col-lg-3 col-md-6 col-sm-6 col-xs-6'>
                  <div class='card bg-normal'>
                  <div class='card-header day1'>
                  <p>Lane booked from 6.00pm</p>
                  </div>
                  <div class='card-body'>";
        echo "<h2 class='lane-title'>Lane " . $bossid . "</h2>";
        echo "Lane Available";
        echo "</div>
                  </div>
                  </div>";
    }

This works great and outputs everything required, BUT....what i need is the following :

If no username, show 1 entity with label "FREE"
If username + detail = 1, show 1 entity with label "TAKEN BY $username"
IF username + detail = 1 AND 2, show 1 entity with label "TAKEN BY $username1 and $username2

At the moment, from the code above, if detail 1 AND 2 are on boss_id 5, then there are 2 entities of boss_id 5, where i only need 1 entity showing both the details 1 AND 2.

Hope this makes some sense!! :)

TIA E

Something like this perhaps: `SELECT l.*, a.username FROM lanes l LEFT JOIN accounts a ON a.boss_id = l.bossid`? — Cyclonecode, Sep 06 '20 at 16:58
So would this replace all of my queries? Thankyou for taking the time to help :) — Eddy H, Sep 06 '20 at 17:11
Ok so i just updated my code with your suggestion, and its now doing something! Thankyou for that. How would i access both the boss_id and username, at least have them accesible in an array? I have tried ```$bossid[] = $row['bossid']['username'];``` but got an error for this as an undefined index....thanks again! — Eddy H, Sep 06 '20 at 17:24
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 06 '20 at 18:01
You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Sep 06 '20 at 18:01
@EddyH - Why not just dump the array to see what it contains e.g `var_dump($row);`. Since you are using a LEFT JOIN the result will be a combination of the fields from both tables so to access `username` you should just use `$row['username'];`. The `username` will be NULL if there is no corresponding row in the accounts table, so you could do something like this: `echo (!$row['username'] ? 'Lane ' . $row['boss_id'] : 'Lane booked by ' . $row['username']);` — Cyclonecode, Sep 06 '20 at 18:45
@Dharman I don't see that he is using any raw input from the user, so the above is not really wide open to sql injections? — Cyclonecode, Sep 06 '20 at 21:11
@Cyclonecode `'".$compare."'` should be parameterized. There should never be any kind of input directly as part of SQL. It doesn't matter where it comes from. — Dharman, Sep 06 '20 at 21:13

score 1 · Accepted Answer · answered Sep 06 '20 at 16:58

You seem to want a LEFT JOIN:

SELECT l.*, a.username
FROM lanes l
LEFT JOIN accounts a ON a.boss_id = l.boss_id

When there is a recored in accounts whose boss_id matches, this brings the corresponding username in the resultset. If there is no match, username comes as null.

Compare 2 mysql column values from different tables

1 Answers1