CodeIgniter Forums
Mysql user defined variable - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Mysql user defined variable (/thread-41009.html)



Mysql user defined variable - El Forum - 04-25-2011

[eluser]dannythebestguy[/eluser]
I want to use a user defined Mysql variable in a Select statement and run it through query function.

$sql = 'Select @var := col1 - col2 FROM table1 WHERE col3 = @var';
$query = $this->db->query($sql);
$data = $query->result_array();

But CI is not returning the records.

Please let me know how to go about and to set the variable too.

Thnx

Dinesh


Mysql user defined variable - El Forum - 04-25-2011

[eluser]Aken[/eluser]
http://dev.mysql.com/doc/refman/5.0/en/variables.html warns against this usage of user variables:
"The general rule is to never assign a value to a user variable in one part of a statement and use the same variable in some other part the same statement. You might get the results you expect, but this is not guaranteed."


Mysql user defined variable - El Forum - 04-25-2011

[eluser]dannythebestguy[/eluser]
Thnx for the suggestion, I have tested and retested user variables for this case in MySQL and it has not give any problem. Just that I do not have a clue how to go about in CI. Help will be appreciated.

Thnx


Mysql user defined variable - El Forum - 04-26-2011

[eluser]Nick_MyShuitings[/eluser]
using the query function is literally just going to execute the SQL... so what does your system return when you execute this sql dircetly using phpmyadmin or somthing similar: "Select @var := col1 - col2 FROM table1 WHERE col3 = @var;" or perhaps "Select @var";


Mysql user defined variable - El Forum - 04-26-2011

[eluser]dannythebestguy[/eluser]
Nick,

Select @var gives a null value.
Select @var := col1 - col2 FROM table1 WHERE col3 = @var gives result as expected from any client, but with CI it does returns a empty array. It does not complain of wrong SQL or throws any error.


Mysql user defined variable - El Forum - 04-26-2011

[eluser]Nick_MyShuitings[/eluser]
Perhaps CI is escaping those values? what does $this->db->last_query(); show?


Mysql user defined variable - El Forum - 04-26-2011

[eluser]dannythebestguy[/eluser]
The db last query gives the right SQL statement. Just the reuturn array is empty.


Mysql user defined variable - El Forum - 04-26-2011

[eluser]Nick_MyShuitings[/eluser]
Do you have access to the MySQL logs to see what actually ran on the server? Aside from that I'm not sure how mysql handles user defined variables, and whether your CI installation is connecting into MySql as the same user, or whether that would even matter.