CODEWITHSUNDEEP
phpmysqlfunctioncodeigniter
xurei
xurei

Reputation: 1086

Create a SQL FUNCTION with CodeIgniter

For a legacy project, I would like to execute this query using CodeIgniter : 

DELIMITER $$
CREATE FUNCTION `getCustomerFullName`(intCustomerID INT) 
  RETURNS varchar(100) CHARSET latin1
  return CONCAT(
    (SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),
    ' ',
    (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID))$$
DELIMITER ;

When I try to use $this->db->query(), I get this error :

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 
'DELIMITER $$ CREATE FUNCTION `getCustomerFullName`(intCustomerID INT) RETURNS '
at line 1

How can I execute such "multi-line" query using CodeIgniter ?

Upvotes: 2

Views: 1787

Answers (3)

grupowebex
grupowebex

Reputation: 185

Best solution i get was looking inside system libs and use it.

$this->db->simple_query("YOUR FULL SQL STRING");

I was looking on google and not solution provided.

Upvotes: 0

elddenmedio
elddenmedio

Reputation: 1030

if you want to execute a Function or SP, you need to add this to mysql (in the mtop menu of your phpmyadmin or Workbench) you have many otions

in More (routine / new / add new)
create a function or sp

Then in CI you only call like

$this->db->query('call Function()');

Upvotes: 1

wogsland
wogsland

Reputation: 9508

The issue is in the query, not CodeIgniter. Use the regular delimiter inside your function:

DELIMITER $$
CREATE FUNCTION `getCustomerFullName`(intCustomerID INT) 
  RETURNS varchar(100) CHARSET latin1
  return CONCAT(
    (SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),
    ' ',
    (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID));
$$
DELIMITER ;

Upvotes: 2

Related Questions