+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11)
+--- Thread: Query Builder Class (/showthread.php?tid=77362)
Query Builder Class - patricio - 08-21-2020
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
RE: Query Builder Class - jreklund - 08-21-2020
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;
RE: Query Builder Class - InsiteFX - 08-22-2020
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.
RE: Query Builder Class - patricio - 08-22-2020
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 !!!
RE: Query Builder Class - InsiteFX - 08-22-2020
There should be a setting in your editor for the file save as type if so select linux