Show/Hide Toolbars

PHPRunner 9.8 Manual

Navigation: Using PHPRunner > SQL query page

PHPrunner32x32     PHPRunner manual


SQL tab

Scroll Prev Next More

 

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.

sql_query_SQL1

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.

Do not use aliases as field labels in your app. 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

Aliases

When you connect to databases like DB2, Oracle or Postgre and your SQL query contains aliases, we recommend to enclose them in double quotes. Here is an example:

select FieldName as "FieldAlias"

from TableName

What is not supported

Stored procedure calls

Update/Delete/Insert/Create queries

Unions

DISTINCT keyword