Contents

 
Home
PHPRunner 7.1 manual
Prev Page Next Page
 
 

SQL tab

 

On the SQL tab you can modify SQL query manually. All changes on this tab automatically transfer to the Query Designer.

yellowbulbNote: if you modify default SQL query, make sure that key column(s) are included. This is required to provide edit/delete functionality. If table participates in Master-Details relationship make sure link fields (primary/foreign key) stay on the list of fields.

qd_sql

To switch between tables use Tables list panel on the left. Find and Replace buttons let you quickly search within your SQL code and modify it.

What is supported

Inner joins, outer joins

SQL query:

SELECT

carsmodels.id,

carsmodels.model,

carsmodels.make

FROM carsmodels

INNER JOIN carsmake ON carsmodels.make = carsmake.make

yellowbulbNote: it's recommended to use aliases for fields from joined tables to avoid confusion when two fields from different tables have the same name.

Calculated fields

SQL query:

SELECT

category,

color,

Date Listed,

descr,

EPACity,

EPAHighway,

features,

UserID,

YearOfMake,

zipcode,

Price*0.1 AS Discount

FROM carscars

In the example above the alias Discount is assigned to the calculated field Price*0.1. Note that if the field was assigned an alias in the SQL query, then the $values array will get the alias instead of field name from the database. So you should use $values["Discount"] instead of $values["Price*0.1"] in your events. For more information about events, see Events.

We do not recommend using aliases to give a field another name. If you have very long or complex field names, you can assign a label to the field on Choose fields page or in the Label editor instead of using aliases.

WHERE clause

SQL query:

SELECT *

FROM carscars

WHERE YearOfMake =2004

For more complicated queries wrap the condition by parentheses:

SELECT *

FROM carscars

WHERE ( YearOfMake =2004 OR YearOfMake =2005 )

ORDER BY and GROUP BY clauses

SQL query:

SELECT

Make,

Model,

AVG (YearOfMake)

FROM carscars

GROUP BY Make, Model

ORDER BY Make

What is not supported

· Stored procedure calls
· Update/Delete/Insert/Create queries
· Unions
· DISTINCT keyword
Converted from CHM to HTML with chm2web Standard 2.85 (unicode)