2. PHPRunner General questions
  3. LIMIT records returned
This topic is locked

LIMIT records returned

 		11/9/2013 5:51:19 AM
PHPRunner General questions
S
 slotracer author

Hi again,

Having rebuilt my Project I now want to 'LIMIT' the number of rows returned for a query.

I would normally add 'LIMIT 20' to the end of my SQL string to do this but PHPR doesn't want to let me do it.

In the 'Edit SQL query' page and 'SQL' tab I can add this and when viewing the results I see only 20 rows. However, the SQL query does not retain this and returns me all the rows.

Is there a way to do this please.

Thanks

Kev
Sergey Kornilov admin 11/9/2013

PHPRunner limits number of retrieved records on its own. I.e. if you have 20 records on the page PHPRunner will add LIMIT 20 to SQL query automatically. To see how it works turn on SQL debug mode: http://xlinesoft.com/phprunner/docs/debugging_tips.htm
S
 		Stucco 11/10/2013

Hi,
I came to post about this same item but found this post on the first page. Mine is slightly different though. I need to use LIMIT in a subquery to only return the first result, however PHPRunner strips this LIMIT out, making the query invalid. My query is like this.

SELECT

field1,

field2,

(SELECT field3 FROM table2 WHERE (criteria) ORDER BY (complex ordering) LIMIT 1) as field3

FROM table1;


The complex ordering necessitates a subquery instead of a standard join (I actually need to pick the first and only first matching record from table2).
I can run this query in the PHPRunner interface by clicking on Results, and directly on the server, but upon build the LIMIT 1 is removed from my subquery, and I receive "Error type 256 - Error description Subquery returns more than 1 row".
How can I stop the builder from removing LIMIT 1 in my subquery?
S
 		Stucco 11/17/2013

Hi,
I have worked around this by removing the LIMIT 1 and adding a group_concat function, and subsequent processing of values pre-export.

SELECT

field1,

field2,

(SELECT group_concat(field3) FROM table2 WHERE (criteria) ORDER BY (complex ordering)) as field3

FROM table1;


In Events -> Export -> Before Export Record I added the following

// Use only the first field3 value from the CSV resulting from the group_concat of field3 matches

$values["field3"] = substr($values["field3"], 0, strpos($values["field3"], ","));


For me this worked, as it is an export only field.
Also, I understand this is fixed in a future 7.x version.
S
 		Stucco 11/17/2013



In Events -> Export -> Before Export Record I added the following

// Use only the first field3 value from the CSV resulting from the group_concat of field3 matches

$values["field3"] = substr($values["field3"], 0, strpos($values["field3"], ","));



I updated the code to account for only a single response

$values["field3"] = (strpos($values["field3"], ',') !== FALSE ? substr($values["field3"], 0, strpos($values["field3"], ",")):$values["field3"]);