I could use some help with a CodeIgniter 4 problem, and I hope there is a CI4 guru here who can help.
First some information about the setup of the website:
I have two mysql tablets (eenheden and statussen). The setup for these tables are:
Eenheden:
id (int, PRIMARY, AI)
roepnummer (vchar(6)
locatie (varchar(255))
status (int)
leden (varchar(255)
telefoon (varchar(255))
Example data:
id, roepnummer, locatie, status, leden, telefoon
1, ‘52-201’, ‘Centrale post’, 1, ‘Jan en henk’, ‘0612345678’
2, ‘52-24’, ‘Start/Finish’, 3, ‘Jolanda’, ‘0698765432’
3, ‘1597’, ‘Ambulance parkeer’, 2, ‘0645678923’
Statussen:
id (int, PRIMARY, AI)
status (varchar(255))
bgcolor (varchar(7))
txtcolor (varchar(7))
Example data:
id, status, bgcolor, txtcolor
1, ‘Beschikbaar’, ‘#FFFFFF’, ‘#000000’
2, ‘Behandelen’, ‘#1d27ad’, ‘#FFFFFF’
3, ‘Pauze’, ‘#FFFFFF’, ‘#959c97’
I want to run this query:
Code:
SELECT e.id, e.roepnummer, e.locatie, e.telefoon, e.leden, statussen.status, statussen.bgcolor as bgcolor, statussen.txtcolor as txtcolor FROM eenheden as e JOIN statussen ON e.status=statussen.id
I have the following code for that:
PHP Code:
$db = \Config\Database::connect();
$builder = $db->table('eenheden');
$builder->select('e.id, e.roepnummer, e.locatie, e.telefoon, e.leden, statussen.status as status, statussen.bgcolor as bgcolor, statussen.txtcolor as txtcolor');
$builder->from('eenheden as e');
$builder->join('statussen', 'e.status = statussen.id');
return $builder->get()->getResultArray();
Now I’m coming to the problem. I get far more results than I should. When the table Eenheden has only 1 row, I get 1 row as a result. When Eenheden has 2 rows, I get 4 results (2 results per row, with identical data). With 3 rows I get 9 results in my array (3 for each row in Eenheden). And so on.
Now, the query functions as expected when I write it out manually (as in the example above).
Am I missing something? Does CI4 add something to my query that makes this happen? Or does my code snippet not function as I think it functions? I’m at a loss. I have been wrecking my brain on this for nearly a week.
EDIT:
Managed to solve the problem with some help elsewhere. I removed the from line and changed ->table to:
$builder = $db->table(‘eenheden as e’);
Now it works as expected.
Sometimes one works on a problem so long (specially when new to a framework, as I am to CI in general) that you read over the solution. It was right there in the documentation. I hope someone else can learn from my mistake 