CodeIgniter Forums
Extremely Wierd DATE based database error. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Extremely Wierd DATE based database error. (/thread-36317.html)



Extremely Wierd DATE based database error. - El Forum - 11-28-2010

[eluser]Shawty[/eluser]
Hi All, I'm posting here after a really frustrating few days trying to resolve the issue a describe below, anyone who might have any insight or ideas are greatly recived beacuse I've been writing code for best part of 30 years and this one's got me at a dead end.

I have a database table (A log of all phone calls recieved by an astresk pbx and inserted into MySql)

Each row in the DB has the following schema

Column Type
---------------------------------
recID int PK
name varchar(50)
msisdn varchar(15)
date date
time time

I have a code igniter install on the system (A Sun Netra Blade) that interrogates the DB and returns XML formatted data for consumation by down stream services and web apps.

The ci install is a standard controller/model/view layout, with the view being formatted as XML output from a PHP script that loops over data provided by a very basic controller calling a model to interrogate the MySql DB that lives on the same machine.

This all works very well except for one really strange thing.

Any dates AFTER 18th July 2010 are not returned by the Active record system in CI when i interrogete the DB. Previous to that date I have no problems, data is returned as expected.

Active record is putting the SELECT stament together correctly, beacuse if I use 'print_r' to dump the object and copy/paste the generated SQL directly into the MySql console it works perfectly and returns the expected data, but active record just cut's off after that date.

the php code in the model to get the data is:

$this->db->where('date =', $day);
$query = $this->db->get('callerlog');
if($query->numrows() > 0)
{
return $query->result_array();
}

Where $day is passed into the method in the following format YYYY-MM-DD which is exactly the same way as the date is stored in the database.

2010-07-18 or previous, data comes back perfectly and as expected.
2010-08-19 or after consistantly comes back with no records

SELECT * FROM callerlog WHERE date >= '2010-08-19' works perfectly fine from the MySql console or from PHP-MyAdmin, so I know it's not a DB thing, Iv'e definately pinned it down to Code Igniter itself, beacuse if I replace my Model code with normal standard MySql API calls I get back the data I expect every time.

Cheers

Shawty


Extremely Wierd DATE based database error. - El Forum - 11-28-2010

[eluser]Shawty[/eluser]
Anyone???

I see many other posts after mine, surely someone who's read this has a small Idea where to start looking?


Extremely Wierd DATE based database error. - El Forum - 11-28-2010

[eluser]jedd[/eluser]
Hi Shawty and welcome to the CI forums.

I suggest you haven't seen a plethora of responses .. because it's such a weird problem. It seems unlikely that it is, as you say, a problem with CodeIgniter itself - or else we'd have seen a few more people reporting this problem (given it's a bit after July now Smile

Can you post some example code - ideally the smallest lump of code that proves this problem out on your system - and an extract of mysql dump - such that someone could take your code, create an identical DB with some sample data, and replicate the problem you're seeing?

Without those files, we're a bit stuck. As I say, lots of people are using dates from the second half of 2010 and (presumably) aren't seeing this kind of problem crop up.


Extremely Wierd DATE based database error. - El Forum - 11-29-2010

[eluser]Bart Mebane[/eluser]
Any chance CI is configured to use an old version of the database?


Extremely Wierd DATE based database error. - El Forum - 11-30-2010

[eluser]Shawty[/eluser]
You know what Guys, Sometimes when you sit staring at a problem for ages and ages and you just can't see the wood for the trees. :-)

This has had me going round in circles for best part of the last 5 days and Bart hits the nail right on the head.

I'd set up a seperate database until I was sure that everything in the service was working, this was 5 months back, and it looks like at the time a did a simple table to table data copy just so I had test data.

Fast forward 5 months, and I come to write a consumer of the service in .NET, and so I load up PHP My admin, browse to the "Call Log Table" (Obviously Completley forgetting that I'd set up a test db) and set the .NET service to enquire on that days date for data, and hey presto the whole spiral of why is my current data not been seen started...

It does however just go to show that even an old dog like me can sometimes get outsmarted by the stupidest of things...

Don't ya just love development & IT :-D

Thanks for the replys guys...

Now please excuse me while I go and repeatedly punch myself in the side of my head...

Shawty