Welcome Guest, Not a member yet? Register   Sign In
Query Builder Class
#1

(This post was last modified: 08-21-2020, 11:27 PM by jreklund.)

Please I need some help the Query Builder Class, this should be simple but i´ve been several days trying to solve it.

This is my Controller function, wich recieves data via POST from a IOT device:

PHP Code:
public function gettopics(){

    
$password strip_tags($this->input->post('gdp'));

    if (
$password == GET_DATA_PASSWORD){

           
$device_sn strip_tags($this->input->post('sn'));
           
$result $this->Getdata_model->gettopics($device_sn);
           echo 
"<br>#".ROOT_TOPIC."/".$result[0]['device_topic']."#".MQTT_USER."#".MQTT_PASSWORD."# "// Line 27
    
}else{
           echo 
"access denied";
    }



And this is my model:

PHP Code:
public function gettopics($device_sn){

    
$this->db->SELECT('device_topic');
    
$this->db->FROM('devices');
    
$this->db->WHERE('device_sn',$device_sn);
    
$result $this->db->get()->result_array();
    return 
$result;



Everything works fine when the field 'device_sn' in WHERE clause is a numeric value (INT, BIGINT, etc) but i need this field to be a VARCHAR

'device_sn' is the device serial number and it contains characters too.


I get this:

A PHP Error was encountered</h4><p>Severity: Notice</p><p>Message: Undefined offset: 0</p><p>Filename: controllers/Getdata.php</p><p>Line Number: 27

Line 27 in Getdata controller is the one with "// Line 27" above. This is the same error i get when there is not device matching the serial number, but in this case in sure the serial number exists ... i only change the data type in 'device_sn' field from INT to varchar and quits working


Many thanks in advance !!!


Patrick
Reply
#2

(This post was last modified: 08-21-2020, 11:33 PM by jreklund.)

Hi, I moved your thread into the CodeIgniter 3 section, as you aren't using CodeIgniter 4.

Please use get_compiled_select() to see the actual generated QUERY to see if it malformed.

PHP Code:
$this->db->SELECT('device_topic');
$this->db->FROM('devices');
$this->db->WHERE('device_sn',$device_sn);
echo 
$this->db->get_compiled_select();
exit; 
Reply
#3

Is the database column for device_sn a VARCHAR field? Or is it an IN?

If the databse field is a VARCHAR then your php

PHP Code:
$device_sn 

is set for an php int

You could get around this by doing this.

PHP Code:
$this->db->WHERE('device_sn',(string)$device_sn); 

Or update your database table to use a VARCHAR.
What did you Try? What did you Get? What did you Expect?

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

Well, that was helpfull !!!

This was the result of get_compiled_select();

SELECT `device_topic`FROM `devices`WHERE `device_sn` = '1234\r\n'

Strangely to me it was still working when `device_sn` as a numeric field with the exact same SELECT sentence.

Now is working, I removed those extra caracters.

Thanks !!!
Reply
#5

There should be a setting in your editor for the file save as type if so select linux
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB