ckroon
Posts: 869
|
| Posted: 08/07/2007, 8:27 PM |
|
Hey all.
I have a grid that is filtered using this SQL statement
select classlist.*, aplan_cst_curryear.*
from classlist
left join aplan_cst_curryear on aplan_cst_curryear.StudentID = classlist.student_id
where aplan_cst_curryear.StudentID is NULL and classlist.grade >= 2
No problems. I need to add a Where: "classlist.teacher_id = UserID" to this but it won't recognize the session variable UserID.
A) Does DQL not recognize session variables? and
B) IF not how do I translate this to Table View? I have tried several times and I can't get it to work properly
Brief explanation: Basically I have a classlist table and there is the aplan_cst_curryear table that has scores from testing. I want the grid to only show those students that do NOT have any scores in the aplan_cst_curryear table.
Thanks!
_________________
Walter Kempees...you are dearly missed. |
 |
 |
Benjamin Krajmalnik
|
| Posted: 08/07/2007, 9:06 PM |
|
Try using the expression CCGetUserID()
ANother thing you can do is echo Session("UserID") OR CCGetUserID() at the
top of your page so you can see if it has an expected value.
|
|
|
|
ckroon
Posts: 869
|
| Posted: 08/07/2007, 11:16 PM |
|
I got this in the SQL Now:
select classlist.*, aplan_cst_curryear.*
from classlist
left join aplan_cst_curryear on aplan_cst_curryear.StudentID = classlist.student_id
where aplan_cst_curryear.StudentID is NULL and classlist.grade >= 2 AND classlist.teacher_id = CCGetUserID()
and CCGetUserID comes back as an unknown function.
Is my syntax wrong?
_________________
Walter Kempees...you are dearly missed. |
|
|
|
datadoit.com
|
| Posted: 08/08/2007, 6:15 AM |
|
Probably the least painful way for you to get this is to drop the SQL
and use the Query Builder. Simply add your two tables, create your
join, your where parameters and you're set.
If you want to see what your problem is with the original SQL statement,
then AFTER you create the above using the Query Builder, switch it over
to SQL to see how it needed to be constructed.
|
|
|
|
DonB
|
| Posted: 08/08/2007, 6:24 AM |
|
You must set the parameter type to 'expression'. This means the
CCGetUserID() will be executed by PHP first, before the SQL statement is
built. Of course you may have done this, but you have not defined the
project settings for 'security', which means the function really does NOT
exist in Common.php. So you'd need to configure the security settings in
that case.
--
DonB
http://www.gotodon.com/ccbth
"ckroon" <ckroon@forum.codecharge> wrote in message
news:546b95fadbe8cd@news.codecharge.com...
> I got this in the SQL Now:
>
> select classlist.*, aplan_cst_curryear.*
> from classlist
> left join aplan_cst_curryear on aplan_cst_curryear.StudentID =
> classlist.student_id
> where aplan_cst_curryear.StudentID is NULL and classlist.grade >= 2 AND
> classlist.teacher_id = CCGetUserID()
>
> and CCGetUserID comes back as an unknown function.
>
> Is my syntax wrong?
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
wkempees
|
| Posted: 08/08/2007, 2:45 PM |
|
Ckroon,
1/What do you mean by DQL?
2/Where were you ding this SQL statement?
2a/in Visual Query Builder (in a Grid)?
2b/in some event coding?
Walter
"DonB" <~ccbth~@gotodon.com> schreef in bericht
news:f9cg5r$u1e$1@news.codecharge.com...
> You must set the parameter type to 'expression'. This means the
> CCGetUserID() will be executed by PHP first, before the SQL statement is
> built. Of course you may have done this, but you have not defined the
> project settings for 'security', which means the function really does NOT
> exist in Common.php. So you'd need to configure the security settings in
> that case.
>
> --
> DonB
>
> http://www.gotodon.com/ccbth
>
>
> "ckroon" <ckroon@forum.codecharge> wrote in message
>news:546b95fadbe8cd@news.codecharge.com...
>> I got this in the SQL Now:
>>
>> select classlist.*, aplan_cst_curryear.*
>> from classlist
>> left join aplan_cst_curryear on aplan_cst_curryear.StudentID =
>> classlist.student_id
>> where aplan_cst_curryear.StudentID is NULL and classlist.grade >= 2 AND
>> classlist.teacher_id = CCGetUserID()
>>
>> and CCGetUserID comes back as an unknown function.
>>
>> Is my syntax wrong?
>>
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.codecharge.com/
>>
>
>
|
|
|
|
datadoit.com
|
| Posted: 08/08/2007, 3:56 PM |
|
wkempees wrote:
> Ckroon,
> 1/What do you mean by DQL?
> 2/Where were you ding this SQL statement?
> 2a/in Visual Query Builder (in a Grid)?
> 2b/in some event coding?
>
> Walter
>>> ---------------------------------------
Walter, what do you mean by ding? ;)
|
|
|
|
wkempees
|
| Posted: 08/09/2007, 2:46 AM |
|
Ok, gotcha.
By ding I mean: doing
Following the same Ckroon must be meaning SQL by DQL
Thanks, sometimes a second view clears things up.
My questions still valid though.
Walter
|
|
|
|
ckroon
Posts: 869
|
| Posted: 08/09/2007, 9:31 AM |
|
It is on a grid and in the Query Builder. I think I may have it sorted out :) Things are crazy round here, haven't had much time to test it.
Switching from Table view to Sql was really helpfu in figuring it all out.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
