MySQLi Multiple table query

Question

Struggling with figuring out which functions to use to get what I need - to list all the companies in ascending order and all their respective users.

So far I have created 2 tables: "companies" and "users" and connected them with a foreign key as below:

I've tried doing a JOIN queries to use both tables which worked to some extent, though I can't figure out how to correct it. The below is what i keep causing:

As you can see, the "Contoso" company is listed twice as it has 2 users associated with it in the users table. The code I'm doing is:

$joinquery = "SELECT companies.id, companies.name, users.fname, users.lname FROM users RIGHT JOIN companies ON users.cid = companies.id ORDER BY name ASC";
       $joinresult = $mysqli->query($joinquery);

       if($joinresult->num_rows > 0) {
           while($row = $joinresult->fetch_assoc()){
               $company = $row['name'];
               $fname = $row['fname'];
               $lname = $row['lname'];
               $name = $fname . " " . $lname;
               echo "<div class='customer'>";
               echo "<div class='name'>";
               echo $company;
               echo "</div>";
               echo "<div class='contacts'>";
               echo $name;
               echo "</div>";
               echo "</div>";
           }
       } else {
           echo "No results";
       }

The divs just make it a block element and change the colour.

How do I get all users within their relevant company rather than a row for each company and user? I've tried nesting but I was further away from the end goal, below is the code:

// Nested Query Test
$nestquery1 = "SELECT companies.name, users.fname, users.lname FROM companies INNER JOIN users ON companies.id = users.cid ORDER BY companies.name ASC";
$nestresult1 = $mysqli->query($nestquery1);

$nestquery2 = "SELECT companies.id, users.fname, users.lname FROM users INNER JOIN companies ON users.cid = companies.id ORDER BY fname ASC";
$nestresult2 = $mysqli->query($nestquery2);

if($nestresult1->num_rows > 0) {
    while($row = $nestresult1->fetch_assoc()){
        $company = $row['name'];
        echo "<div class='customer'>";
        echo "<div class='name'>";
        echo $company;
        echo "</div>";
        if($nestresult2->num_rows > 0) {
            while($row = $nestresult2->fetch_assoc()){
                $fname = $row['fname'];
                $lname = $row['lname'];
                $name = $fname . " " . $lname;
                echo "<div class='contacts'>";
                echo "<li>";
                echo $name;
                echo "</li>";
                echo "</div>";
            }
        }
        echo "</div>";
    }
} else {
    echo "No results";
}

Help will be very much appreciated!

Answer 1

If you only need to show few fields then try to grab the data in one query : sql fiddle

SELECT c.name company_name,
group_concat(u.name) employees_name
FROM companies c JOIN users u on c.id = u.cid
group by c.name;

Answer 2

You have two options, (A) use a join or (B) use two queries as you have attempted to do so. The join will require you to track the company for a change, however the two queries will not. There are pro's and con's to both options which are:

• A) Joins like this are fast, however you will be returning the company details for every single record incurring additional traffic load in communications with the database. This can be a concern if the database server is on a separate server, or you are dealing with an enormous data set. Remember, even if the extra data seems like a small amount, multiply it by the number of expected concurrent visitors to the website and you might be saturating your database link.

  The sample provided by @abney317 is accurate however instead of comparing the company name I would compare the cid as not only will it be more performant, it will be more reliable.

• B) This is the method I would opt for, while it is a little more involved to implement and requires two queries instead of one, it is the more scalable and maintainable option into the future. It will also make your code a little easier to follow and debug.

  Example:

  // Nested Query Test
  $rsCompanies = $mysqli->query("SELECT cid, name FROM companies ORDER BY name");
  while($company = $rsCompanies->fetch_assoc())
  {
    echo "<div class='customer'>";
    echo "<div class='name'>" . htmlspecialchars($company['name']) . "</div>";
    echo "<div class='contacts'>";
    echo "<ul>";
    $rsUsers = $mysqli->query("SELECT fname, lname FROM users WHERE cid = " . $company['id']);
    while($user = $rsUsers->fetch_assoc())
    {
      $name = htmlspecialchars($user['fname'] . " " . $user['lname']);
      echo "<li>$name</li>";
    }
    echo "</ul>";
    echo "</div>";
    echo "</div>";
  }
  

A few further notes:

• Always use htmlspecialchars when outputting text from a database that should not contain any HTML, failure to do so opens you up to XSS attack vectors.

• I did not SQL escape the company id, as according to your database schema it is an integer and since it is not provided by the end user it is safe to use it directly avoiding any additional processing overhead.

Answer 3

Since you're ordering by the company name, you should be able to check when the company name has changed in order to know when to create a new container.

$currentCompany = "";

while($row = $joinresult->fetch_assoc()){
    $company = $row['name'];
    $fname = $row['fname'];
    $lname = $row['lname'];
    $name = $fname . " " . $lname;

    if($currentCompany != $company) {
        if($currentCompany != "")
            echo "</div>";

        $currentCompany = $company;

        echo "<div class='customer'>";
        echo "<div class='name'>";
        echo $company;
        echo "</div>";
    }

    echo "<div class='contacts'>";
    echo $name;
    echo "</div>";
}