Welcome Guest, Not a member yet? Register   Sign In
Database troubles

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:

$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');
$query = $this->db->get();*/

If I try to call any of the data I get issues like:
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:

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.

have you run the db->last_query method yet?

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

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.

Now that I can see the generated SQL I see the problem.

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:

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

use false as the third parameter of the where method that stops the backticks from being added to the sql statement.

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.

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:
$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?

Theme © iAndrew 2016 - Forum software by © MyBB