CodeIgniter Forums
CI3 WHERE and ORDER_BY errors - Printable Version

+- CodeIgniter Forums (
+-- Forum: Development (
+--- Forum: Issues (
+--- Thread: CI3 WHERE and ORDER_BY errors (/thread-1243.html)

CI3 WHERE and ORDER_BY errors - pkMyt1 - 02-21-2015

I have been looking into moving my one of my sites from CI2 to 3 and hit a snag that I have been unable to track down.  This code:

PHP Code:
$this->db->select'molecule.idmolecule,molecule,comment, molecule.projects_id');
 $this->db->where'projects_id'$this->project_id );
$this->db->from'molecule' );
$this->db->order_by'molecule''ASC' );
       $query $this->db->get_compiled_select();

Returns this query which appears to me to be correct:
SELECT `molecule`.`idmolecule`, `molecule`, `comment`, `molecule`.`projects_id` FROM `molecule` WHERE `projects_id` = 7

If but it also returns this error even when I place a die; immediately after print:

A Database Error Occurred

Error Number: 1054
Unknown column 'projects_id' in 'where clause'

UPDATE `ci_sessions` SET `timestamp` = 1424579236 WHERE `projects_id` = 7 AND `id` = '51ae29260b86c80abe0c60829071d14727c66bdb'

Filename: libraries/Session/drivers/Session_database_driver.php
Line Number: 239

This is coming from both the WHERE and the ORDER_BY lines.  Removing both returns the data I expect.  That 'id' in the database error block is the session id.  Is this a bug or did I miss something???

I did not have enough of the code pasted originally. I have traced the WHERE and ORDER_BY problem to having the start_cache and flush_cache lines.

RE: CI3 WHERE and ORDER_BY errors - Narf - 02-22-2015

You're looking for a column that doesn't exist, and naturally - it produces an error. How can that be a bug?

RE: CI3 WHERE and ORDER_BY errors - pkMyt1 - 02-22-2015

The columns all exist. CI3 was attempting to update the sessions table when it should not have. The core error was mine. If I understand it correctly, CI3 does not use start_cache or flush_cache anymore but instead requires cache_on and cache_off or cache_delete. When I made that change the error is gone. Still leaves me wondering why using the CI2 start_cache in combination with WHERE or ORDER_BY causes CI3 to attempt to update the sessions table with data that is not and does not belong there.

RE: CI3 WHERE and ORDER_BY errors - pkMyt1 - 02-22-2015

CodeIgniter Rocks. I have several sites built on CI2 and love it. Other than this issue migrating to CI3 was fairly straight forward. I would like to thank the community that answers questions for folks like me and the folks at the British Columbia Institute of Technology for picking it back up.

I have chased this in a circle and still do not understand the why but I think I understand the what.
I have always stored the session data in a table in my MySQL DB, not for speed as much as security (right or wrong). With CI2 this was never a problem. Commands like db->start_cache, db->stop_cache, db->flush_cache all worked as expected. With CI3 and a sessions table they do not when a WHERE or ORDER_BY is present. I guess CI2 was caching the query in a cookie?? Long story short, I gave up trying to get it to work with a sessions table and configured the sessions for file storage. Works like a charm. I have one question if someone happens to read this. the session data is destroyed upon logging out and closing the browser window but the file remains. I could do a CRON job one per day to clean any that are more than 24 hours old but would rather have CI delete this as it exits.

RE: CI3 WHERE and ORDER_BY errors - Narf - 02-23-2015

Oh, I see ...

Well, if you put a die() call after flush_cache(), there shouldn't be an issue. For portability reasons, the session database driver uses the query builder class ... you probably know that already.

The difference in that between CI2 and CI3 is that in version 3, there's only one UPDATE query sent to the database and that's at the end of script execution or whenever you call session_write_close(). So, you probably have some query cache active at the end of your program's logic.
CI2 on the other hand, immediately writes to the database each time you call sess_userdata() and presumably, your application doesn't have any query cache at those points.

Perhaps we could change the driver to call flush_cache() itself before constructing its UPDATE query, but I'm not really a fan of this ... it will break another use case.

And yes, CI will automatically delete inactive session files, you don't need to setup a cron job.

RE: CI3 WHERE and ORDER_BY errors - pkMyt1 - 02-23-2015

The fog lifts. I read about the session_write_close() but did not connect the dots to my problem. I will look into that. I think I also understand better why the manual suggests using file to store the session. If the app has to constantly do a write_close call each time that is, in my case, going to slow things down. Thanks for answering my questions. I will continue to explore CI3. So far I am very pleased with the new framework.

RE: CI3 WHERE and ORDER_BY errors - Narf - 02-23-2015

No, calling session_write_close() won't slow things down ... It's just the query caching feature that's messing up with the Session-related queries.