This topic is locked
[SOLVED]

 Modifying $strSQL in BeforeQueryList event

6/7/2008 9:11:36 AM
PHPRunner General questions
G
giles author

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

J
Jane 6/9/2008

Giles,
It's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.

G
giles author 6/20/2008

Hi Jane,

Thanks for responding.
I solved the problem for the time being by editing the sql query in the include file. The drawback with that approach is I have to do this every time phpr decides to rebuild that file.
I am very busy with other deliverables of the project so will get back to a longer term solution later.
Thanks again.