Database troubles

[eluser]Maglok[/eluser]
There is a current bug that makes the profiler not display queries if you are running PHP 4 (which I am). I use active records to build my query, but I am getting errors.

I have 3 tables basically:
- personen
- inschrijvingen
- evenementen

inschrijvingen has both a persoon_id and a evenement_id, creating a unique pair. I want to select all the 'personen' that are in 'inschrijvingen' with a evenement_id that I give. So I wrote this:

Code:

$this->db->select('personen.voornaam, personen.tussenvoegsel, personen.achternaam, inschrijvingen.type');
$this->db->from('personen, inschrijvingen');
$this->db->where('inschrijvingen.evenement_id', $_GET['id']);
$this->db->where('inschrijvingen.persoon_id', 'personen.persoon_id');
$this->db->order_by('personen.achternaam','asc');
$query = $this->db->get();*/

If I try to call any of the data I get issues like:

Code:

A PHP Error was encountered
Severity: Notice
Message: Undefined property: achternaam
Filename: views/inschrijven_lijst.php
Line Number: 37

I tried putting the query directly in phpMyAdmin using this:

Code:

SELECT personen.voornaam, personen.achternaam, personen.tussenvoegsel, inschrijvingen.type
FROM personen, inschrijvingen
WHERE inschrijvingen.persoon_id = personen.persoon_id
AND inschrijvingen.evenement_id = 1

It selected no problem. I'd normally run the profiler but it is suffering from the bug, I am trying to find out just what query I am building with the active record.

[eluser]xwero[/eluser]
have you run the db->last_query method yet?

[eluser]Maglok[/eluser]
Thank god. I didn't know that method. Finally! I had been looking for that for a while.

[eluser]srisa[/eluser]
Can you use two tables in the "from" clause like you did? I use raw queries most of the time, so i do not know how this is acceptable with active records.
If you are curious about the query, .system/database/DB_active_rec.php line 1002 (CI version 1.7.0), there you can see the query generated by CI.

[eluser]Maglok[/eluser]
Now that I can see the generated SQL I see the problem.

Code:

SELECT `personen`.`voornaam`, `personen`.`tussenvoegsel`, `personen`.`achternaam`, `inschrijvingen`.`type` FROM (`personen`, `inschrijvingen`) WHERE `inschrijvingen`.`evenement_id` = '1' AND `inschrijvingen`.`persoon_id` = 'personen.persoon_id' ORDER BY `personen`.`achternaam` asc

It is in the AND it generates from this line of code:

Code:

$this->db->where("inschrijvingen.persoon_id", "personen.persoon_id");

It generates 'personen.persoon_id' instead of 'personen'.'persoon_id', and thus fails to recognize it. If I can't use where() to do this what do I use?

[eluser]xwero[/eluser]
use false as the third parameter of the where method that stops the backticks from being added to the sql statement.

[eluser]Maglok[/eluser]
Yes! Thanks. It's small things like that, that really help out. I did notice the option in the user_guide but didn't connect it to this issue. Stupid me.

[eluser]Maglok[/eluser]
Interesting development. I have decided to put a forum and the application in the same database, for easier management. The forum is prefixed 'smf_' while the application is prefixed '_phpci'. I have done that through the config.php.

All my queries convert nicely, I don't need to change any code, it will translate it. BUT:

Code:

$this->db->where('inschrijvingen.persoon_id', 'personen.persoon_id', FALSE);

It doesn't put the prefix in front. If I put it to TRUE it does work, but once again the query is not valid and will select 0 rows.

So I either put at FALSE and it errors or on TRUE and it can't select anything. A way around this one?