Hi,
I'm trying to use a session variable in a query for a list page so I planned to modify $strSQL in a BeforeQueryList event.
However this produces a SQL error.
Before modification the query is:
select _news.NewsID, fred.unID, fred.userId, if (fred.ReadIt is null, 0, fred.ReadIt) as ReadIt, _news.Title, _news.Description, _news.Date_Modified from (select unID as unID, NewsID as NewsID, userId as userID, ReadIt as ReadIt from _usersnews where userID=22) as fred right join _news on fred.NewsID=_news.NewsID ORDER BY `ReadIt` ASC, `Date_Modified` DESC
This works fine and produces what is expected - as long as it is for user 22.
Then I modified $strSQL in the event by:
$strTEMP = str_replace("22", "".$_SESSION["UserIDnumber"]."", $strSQL);
$strSQL = $strTEMP;
(Note: the session variable is defined elsewhere after a successful login...)
When I run this for user 6 I get a SQL 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 ') as fred right join _news on fred.NewsID=_news.NewsID' at line 1"
the SQL Query in the error msg is:
select _news.NewsID, fred.unID, fred.userId, if (fred.ReadIt is null, 0, fred.ReadIt) as ReadIt, _news.Title, _news.Description, _news.Date_Modified from (select unID as unID, NewsID as NewsID, userId as userID, ReadIt as ReadIt from _usersnews where userID=6) as fred right join _news on fred.NewsID=_news.NewsID ORDER BY `ReadIt` ASC, `Date_Modified` DESC
If I copy this into mysql query browser it produces the right result for user 6.
Anybody shed any light on what is happening here?
Giles