remove empty results with a 2nd query from array mysqli

Question

I have 2 tables, 1 with companies(Costcenters) and one with clients (employees of those companies)

i need to make a form to edit or delete malformed ( like john smit and j. Smit) from those companies employees grouped by company (Costcenter), when i make a list with all those companies i get a lot of companies that has no clients. So I made a array of the companies (Costcenters) and check first if they have employees, this with the goal to remove the Costcenters without employees from the array ($bedrijven).

The form is no problem, but i cant find a way to get those companies removed from the bedrijven array.

require_once('conn.php');
$query = "SELECT bedrijfID, Houder, Costcenter, Actief FROM bedrijven 
WHERE Actief = 'actief' ORDER BY Costcenter";
$results = mysqli_query($conn, $query);
if (!$results) printf("Query failed: %s\n", $conn->error);

$bedrijven = [];
while($row = mysqli_fetch_assoc($results)) {
$bedrijven[] = $row['Costcenter'];
}
foreach ($bedrijven as $key => $item) {

$query1 = "SELECT * from customer where Costcenter = '$item' ORDER by 
Client";
$customerresult = mysqli_query($conn, $query1) or 
die(mysqli_error($conn));
if (!$customerresult) printf("Query failed: %s\n", $conn->error);
    if($customerresult->num_rows === 0) {
    unset($bedrijven[$key]);
   }  
}

I am not familiar with PDO or funtions so tried it this way that does not work as i expected, the unset is not working.

the code is editted as it is working now, i hope it might help others as well. If any has a better solution please post.

`customerresult` is treated as a constant; it's missing the `$` sign for it. Enable error reporting. So, is that your real code? — Funk Forty Niner, Nov 12 '18 at 19:08
Its not a duplicate, I dont get any errors, just not getting the empty results removed from the bedrijven array.. — aton Graaff, Nov 12 '18 at 19:14
Can you add the result of `var_export($bedrijven);` to your question? — The fourth bird, Nov 12 '18 at 19:48

Nosajimiki · Answer 1 · 2018-11-20T18:04:28.510

2

If I understand what you are going for, this is better done as a single Query. A JOIN can be used first to bind your tables, and then additional WHERE operators can be used if needed to refine your search. I'm not 100% sure if I'm reading right that this is is exactly how you wanted to join the data, but if you play with different JOIN operators you'll get it.

$query = "SELECT Costcenter.bedrijfID, Costcenter.Houder, Costcenter.Costcenter, Costcenter.Actief, customer.* FROM Costcenter
LEFT JOIN customer ON customer.Costcenter = Costcenter.Costcenter 
WHERE Actief = 'Costcenter.actief' AND Costcenter.Costcenter != "" ORDER BY Costcenter.Costcenter";

The biggest reason for doing it this way this that a single SQL call processes WAY faster than trying to parse your data from multiple calls in PHP.

edited Nov 20 '18 at 18:04

answered Nov 12 '18 at 20:05

Nosajimiki

1,073
1
9
17

That would look for empty Costcenters in the bedrijven table. the goal was to look in if there were Costcenters that had no customers in the customers table and so remove those costcenters from the bedrijven array. – aton Graaff Nov 12 '18 at 20:43
Sorry, I got the array name and table name backwards, but the process is still the same. By selecting Costcenter and joining customer to it, you get a combined dataset. Then your WHERE statement filters out empty cost centers. Doing it this way can literally be thousands of times faster which will make a huge difference as your database grows. – Nosajimiki Nov 20 '18 at 18:13
the join way of the query is a perfect solution to make it less heavvy , thatnks for the advice. – aton Graaff Nov 21 '18 at 11:02

score 0 · Answer 2 · answered Nov 12 '18 at 20:38

thanks to the requestion of The fouth bird i discovered i have been wasting a lot of time on a simple solution. i should not have done : unset($bedrijven['Costcenter']); but

 unset($bedrijven[$key]);

you must unset the key in the array not the value....

remove empty results with a 2nd query from array mysqli

2 Answers2