• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Mysql user defined variable

#1
[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

#2
[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."

#3
[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

#4
[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";

#5
[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.

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

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

#8
[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.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.