2. PHPRunner General questions
  3. correct List Page before SQL Query syntax
This topic is locked

correct List Page before SQL Query syntax

 		9/24/2012 8:41:08 AM
PHPRunner General questions
S
 scoobysteve author

I have tried to migrate a 6.1 phpr project into the new 6.2 and it seems that if I have any filters or sort types set in the SQL Query designer I get a logon fail, so I have reset the queries and mow I can logon but I still need my filters in place. I have managed to get the desending order one sorted by using the
$strOrderBy = "ORDER BY Sapre16 DESC"
But I am trying to add a WHERE statement and I cant get the syntax right, I want the following to be true
where (TotalScore > "199") AND (MainImage <>"")
this is what was in the SQL tables in 6.1 and it works but if I put this in the tables in 6.2 it fails to log me on, so how can I add this in the
List page in the Before SQL query event please
Thanks
Steve
C
 		cgphp 9/24/2012
$strWhereClause = whereAdd($strWhereClause, "TotalScore > 199");

$strWhereClause = whereAdd($strWhereClause, "MainImage <> ''");
S
 		scoobysteve author 9/24/2012


$strWhereClause = whereAdd($strWhereClause, "TotalScore > 199");

$strWhereClause = whereAdd($strWhereClause, "MainImage <> ''");



wow that was quick thanks Cristian I will try that thanks again, is there any reason why adding these to the SQL designer does not work on 6.2
Steve
S
 		scoobysteve author 9/24/2012



wow that was quick thanks Cristian I will try that thanks again, is there any reason why adding these to the SQL designer does not work on 6.2
Steve
Sergey Kornilov admin 9/24/2012

You should not be using any double quotes in your SQL:

where (TotalScore > "199") AND (MainImage <>"")
SQL uses single quotes. Also, if TotalScore is a number you do not need any quotes around 199
Check this article to learn more about quoting in SQL:

http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql
S
 		scoobysteve author 9/24/2012



You should not be using any double quotes in your SQL:

where (TotalScore > "199") AND (MainImage <>"")
SQL uses single quotes. Also, if TotalScore is a number you do not need any quotes around 199
Check this article to learn more about quoting in SQL:

http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql


I hear what your saying but if I use the SQL query designer in your software and make 2 filters in the filter columns one with TotalScore >199 and another value in the MainImage filter box of <>""

then when I look at the SQL before the results it places that code in there
where (TotalScore > "199") AND (MainImage <>"") and it works fine on 6.1
Sergey Kornilov admin 9/24/2012

I'm afraid there is some sort of confusion here.
Anyway, if you need help with this post your application to Demo Account and open a ticket at http://support.xlinesoft.com sending your Demo Account URL. 'Demo Account' button can be found on the last screen in the program.
It would make more sense if you publish both PHPRunner 6.1 and 6.2 projects there.
S
 		scoobysteve author 9/25/2012



I'm afraid there is some sort of confusion here.
Anyway, if you need help with this post your application to Demo Account and open a ticket at http://support.xlinesoft.com sending your Demo Account URL. 'Demo Account' button can be found on the last screen in the program.
It would make more sense if you publish both PHPRunner 6.1 and 6.2 projects there.


I dont have a paid version of PHPR so I dont suppose there will be support there if I upload my project, I am not confused at all. My project has been working for ages on 6.1 its only because I want the new image upload feature that I am interested in using the 6.2 and then if this works I will buy the product. In my original 6.1 project I have used the Query designer to create the filters for my project, please see images below.




As you can see the 6.1 allows me to use the designer to add the filters and then the statements are added automatically to the SQL list of queries and this works. BUT 6.2 will not allow me to use the same designer to add the filters or it fails to let me logon.
I have used SQL statements in the List Page before SQL query event but it does not work as well as putting them in the designer. If I select a record that has been filtered and then click next record it goes to the next record that is 1 id number higher but the filters do not work so its useless that way.
This is what I am using now in the vent field
$strWhereClause = whereAdd($strWhereClause, "TotalScore > 199");

$strWhereClause = whereAdd($strWhereClause, "MainImage <> ''");

$strOrderBy = "ORDER BY Spare16 DESC"
I hope you can see where I am coming from on this.
Regards

Steve