+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: How to execute a prepared statement in CI (/showthread.php?tid=163)
How to execute a prepared statement in CI - dlm1897 - 11-06-2014
Hi all, I have been unable to find documentation on this. In MySQL, I have the following prepared statement:
Code:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT'MAX(IF(Name = ''', Name, ''', Name, NULL)) AS `', Name, '`') INTO @sql
from Contact_Types;
SET @sql = CONCAT('SELECT c.First_Name, ', @sql, ' from Contacts c
left join Contacts_Contact_Types cct
on c.Contact_Id = cct.Contact_Id
left join Contact_Types ct
on cct.Contact_Type_Id = ct.Contact_Type_Id
group by c.Contact_Id');
PREPARE statement FROM @sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;This allows me to dynamically retrieve "contact types" associated with a contact. How can I execute this statement in CI and retrieve the results?
I have tried the following and receive a SQL syntax error upon execution:
Code:
$this->db->query("PREPARE statement FROM @sql; EXECUTE statement; DEALLOCATE PREPARE statement;");Thanks for any help you can give!
RE: How to execute a prepared statement in CI - Rufnex - 11-06-2014
CI doesn't support prepared statements. You can use bindings vor replacement like described on the bottom of page "Query Bindings":
https://ellislab.com/codeigniter/user-guide/database/queries.html
For safe querys you should use the active record class:
https://ellislab.com/codeigniter/user-guide/database/active_record.html