Database troubles - Printable Version

Database troubles - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database troubles (/showthread.php?tid=19937)

Database troubles - El Forum - 06-23-2009

[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.

Database troubles - El Forum - 06-23-2009

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

Database troubles - El Forum - 06-23-2009

[eluser]Maglok[/eluser]
Thank god. Smile

I didn't know that method. Finally! I had been looking for that for a while.

Database troubles - El Forum - 06-23-2009

[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.

Database troubles - El Forum - 06-23-2009

[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?

Database troubles - El Forum - 06-23-2009

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

Database troubles - El Forum - 06-23-2009

[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.

Database troubles - El Forum - 06-24-2009

[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?