Welcome Guest, Not a member yet? Register   Sign In
Query bindings keep unwanted quotes around IS NULL
#1

Hmm, CI throws this error:

Code:
A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''IS NULL' AND plantauth.contributor_id IS NULL ORDER BY 'plantitems.id ASC' at line 4

SELECT * FROM plantitems LEFT JOIN plantauth ON plantitems.id = plantauth.plantid WHERE plantauth.owner_id = '51' AND plantitems.death 'IS NULL' AND plantauth.contributor_id IS NULL ORDER BY 'plantitems.id ASC'

Filename: models/Logbook_model.php

Line Number: 29


Here's a screenshot of the model.

[Image: JukAGq.png]

WANT to pass a variable's text into the query binding but CI is passing the quotes around IS NULL and the SQL query doesnt like that. (Shown in the fourth paragraph of the error).

Am I missing a parameter like 'escape' (if one exists)?
Reply
#2

I was trying to avoid using the Query Builder Class, but had to.


Final code for now:

[Image: Zaau2K.png]
Reply
#3

Did you try using double quotes for the query body?
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#4

A stupid question: If it's you who defines the value of $archived (in the code...), why put it as parameter and not simply concatenate it inside the string of the query?
Reply
#5

(07-22-2016, 06:33 AM)Avenirer Wrote: A stupid question: If it's you who defines the value of $archived (in the code...), why put it as parameter and not simply concatenate it inside the string of the query?

Not a stupid question.
Reply
#6

(This post was last modified: 07-22-2016, 07:57 AM by meow. Edit Reason: clarity )

(07-21-2016, 03:22 PM)InsiteFX Wrote: Did you try using double quotes for the query body?

Man, I tried everything lol. Tried concatenating and changing up the quote types all around. You think its worth looking into? I know ci is doing it to produce safer queries.

(07-22-2016, 06:33 AM)Avenirer Wrote: A stupid question: If it's you who defines the value of $archived (in the code...), why put it as parameter and not simply concatenate it inside the string of the query?

That's a good question and its fair to ask a stupid question if you are wanting to learn something (or you asking in order to hack my website? kidding..) That parameter is handy when using it from the controller:

Setting it to TRUE in the controller calls the model method and
setting it to FALSE can change things up in a quickie (not a seasoned php writer/veteran here).

It should probably help you to see the SQL design to understand this further, let me know.

Controller preview
Code:
$data['my_active_logbooks'] = $this->logbook_model->get_my_logs($this->session->userdata('person_id'),FALSE,$this->session->userdata('sorting'));

$data['my_archived_logbooks'] = $this->logbook_model->get_my_logs($this->session->userdata('person_id'),TRUE,$this->session->userdata('sorting'));

I probably misunderstood your question though.
Reply
#7

(This post was last modified: 07-22-2016, 11:52 AM by InsiteFX. Edit Reason: added echo )

Did you try doing a echo var_dump($data); to see what you are getting back?
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#8

The issue here is that you're trying to bind both the value (parameter) and the operator to your prepared statement. Further, you're trying to use a string to represent null, which usually doesn't work even if you do everything else properly. You're only allowed to bind the value to the '?' token in your statement, so you are going to have to change your statement based on whether you want to check for 'is null' or 'is not null'. In many cases, this would mean you need two separate prepared statements, or a stored procedure which changes the query based on the input (but you probably still wouldn't pass 'is null' or 'is not null' as your values).
Reply




Theme © iAndrew 2016 - Forum software by © MyBB