MYSQL User Variables

[eluser]Timothy_[/eluser]
Hello,

I am trying to set a variable within my sql query as I pack the results from the query into an array,

I have done a lot of reading, trial and error but still no luck

http://dev.mysql.com/doc/refman/5.0/en/u...ables.html

Code:

query = $this->db->query("    SET @a := 0;
                    SELECT @a, title.id, date as datedue
                    FROM lists
                    ");

I have also tried other variations, but all return Code Igniter MYSQL errors.

Any help is appreciated.

Tim

[eluser]bgreene[/eluser]
have never used a compound query in ci but i know you can create it as a stored procedure eg procname, and then query("call procname") but don't forget the closing semicolon after lists, in fact, maybe thats where your error is.

[eluser]Timothy_[/eluser]
I have tried a semicolon and that doesn't work either. So does codeigniter not support user defined variables in the SQL? If this is the case, then this will be the first time Code Igniter has actually acted as a hindrance in my 6 months of application development.

Please more advice would be greatly appreciated.

Tim

[eluser]BrainCatcher[/eluser]
Hi Timothy_,

What kind of error you have?

btw; did you posted all off you code? If you did i would from start replace 'query' for '$query'

Grtz BrainCatcher

[eluser]Timothy_[/eluser]
Hello,

No that was not the complete code.

Here is a version with all the code.

Code:

$hmkquery = $this->db->query("    SET @a := 'abc';
                SELECT homework.id, title as title, datedue as start, @a;
                FROM homework
                JOIN (homework_meta) ON (homework_meta.homework_id = homework.id)
                LEFT JOIN (classes) ON (homework.class_id = classes.id)
                WHERE user_id = $user_id AND completed = $due
                ORDER BY datedue ASC
                ");


echo json_encode($hmkquery->result_array());

This outputs

A Database Error Occurred

Error Number: 1064

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 '; SELECT homework.id, title as title, datedue as start, @a; FROM homew' at line 1

SET @a := 'abc'; SELECT homework.id, title as title, datedue as start, @a; FROM homework JOIN (homework_meta) ON (homework_meta.homework_id = homework.id) LEFT JOIN (classes) ON (homework.class_id = classes.id) WHERE user_id = 7 AND completed = 0 ORDER BY datedue ASC

I'm hoping this is just syntax. I've tried putting semi-colons everywhere and moving things around.

Suggestions are most appreciated!

Thanks,

Tim

[eluser]Timothy_[/eluser]
Ok I have done more investigations and found that I cannot do a query with

Code:

SET

within the SQL query.

Has anyone else noticed this. Is this a bug in Codeigniter?

Tim

[eluser]Sayian[/eluser]
I'm not sure if you've read this or not but ...

Why You Shouldn't Use SET

The MySQL SET datatype is not commonly used for a few reasons; First, using the MySQL SET datatype limits you to 64 elements. While you could get around this by using multiple SETs, this still represents a loss of versatility. Second, you cannot include commas in your set elements, as the comma is an element separator. Third, using a set means your data is not normalized. In our above example, we are tracking a person's interests for a hypothetical dating site. In a normalized schema, there should be three tables: one for the person, one for all possible interests, and one that links a person to their particular interests. Fourth, an INDEX on a set datatype is going to refer to the set as a whole and will not be used for searching individual elements (this may or may not be a problem for certain applications).

(Provided from the MySQL Dev Site)

http://dev.mysql.com/tech-resources/arti...atype.html

[eluser]Timothy_[/eluser]
Sayian

Thanks, I was not aware of this.

However... I don't believe I have a choice.

I am trying to prepare an array for a JSON encode. SQL SETS are the only way I can prepare variables that do not reside in the database for encoding to JSON. If you have another way please suggest it, telling me not to use it isn't very helpful

Thanks,

Tim