Active record and date in where statement

Active record and date in where statement - 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: Active record and date in where statement (/showthread.php?tid=32370)

Pages: 1 2 3

Active record and date in where statement - El Forum - 07-22-2010

[eluser]mddd[/eluser]
It's hard for us to help, because you give different pieces of information each time. At first it was about dates, now we are talking about a different query. And you gave the SQL but not the php code that generated it.

There are definitely ways to lose the 'backticks' like you have around 'active_id'. But please give us the php code so we can see what you are putting there.

Active record and date in where statement - El Forum - 07-22-2010

[eluser]smilie[/eluser]
mddd,

I am sorry if I have caused any confusion. Indeed, there are two different 'problems' in this topic.
Let's forget date problem completely for now and focus only on the "id = action_id" in where.

Ok, here goes the code.

In the controller:

Code:
# Load the reports model, which will do all DB queries

$this->load->model('reports_model');

# Get all objects

$do['start']['objects_warning'] = $this->reports_model->objects_warning();

# Get open and or delivered objects

Then in the reports_model, function:

Code:
function objects_warning()

{

    $this->db1 = $this->load->database('db1',TRUE);

    $this->db1->where('invalid !=','1');

    $this->db1->where('status','0');

    $this->db1->where('id','action_id');

    $this->db1->where('atempt >','0');

    $this->db1->where('atempt <','4');

    $this->db1->from('table1','table2');

    $res = $this->db1->count_all_results();

    return $res;

}

With this code I receive $res = 0 (zero).

If I copy & paste the query in PHPMyAdmin (and strip all quotes) then I do get a valid result (!=0).

Once again, apologies for confusion.

Regards,
Smilie

Active record and date in where statement - El Forum - 07-22-2010

[eluser]mddd[/eluser]
No problem Smilie. It often happens that one discussion leads into the next.

Allright. The question is: which do you mean for the 'id=action_id' part. Because that is the ambigious thing here.

Remember, CI doesn't know if a value is a column or a string. By default it will think that the first variable is the column name and the second is a value you want to test against. So if you write

Code:
$this->db->where('id', 'action_id');

you get:

Code:
WHERE `id` = 'action_id'

That's logical. The column name is marked with backticks by CI and the value is simple a string: "action_id".

If you would write

Code:
$this->db->where('id','action_id',false);

you get:

Code:
WHERE id = 'action_id'

That's because you tell CI not to put in the backticks for the column name.

But still, CI will think of the second parameter as a value, not a column name. So you must write

Code:
$this->db->where('id = action_id','',false)

Then you get:

Code:
WHERE id = action_id

There are no backticks because you told CI not to use them. Your argument is copied just as you wrote it.
And you leave the second parameter empty so CI doesn't write '=....' somewhere.

Active record and date in where statement - El Forum - 07-22-2010

[eluser]smilie[/eluser]
Hi mddd,

Third solution works:
$this->db1->where('id=action_id','',FALSE);

My mistake was, that I have placed TRUE instead of FALSE Sad

'Mistery' solved :-)

Regarding that date problem, I am going to open a new thread to keep things clear.

Thank you very much!

Regards,
Smilie

Active record and date in where statement - El Forum - 09-25-2010

[eluser]alexaaaaaaaaaa[/eluser]
Hi, i have the same issue how can i interogate the db in the where statement to " search " if the user has posted more then 2 articles in one month. Can you guys please help me?