SQLresult
[SQLresult] is used to display the result of a [SQLexecute]
Parameters
| Parameter | Description |
|---|---|
| result_ref (or just 'ref') | (Required) - The name of the SQLResult variable you created with a prior execution of [SQLexecute] (the value you set for the "result_var" or "var" parameter) |
| Tag | Description |
|---|---|
| [commandtext] | The SQL command string that was executed via the [SQLexecute] context. |
| [numfound] | A number indicating how many records were returned as the result of the SQL statement (a 'select' statement in most cases). Some SQL statements will not result in a record set, i.e. DELETE, INSERT, DROP, etc... In these cases [numfound] will be zero. |
| [numfields] | A number indicating the number of fields in the returned record set. |
| [numrowsaffected] | The number of rows changed by an INSERT/UPDATE/DELETE command. |
| [insertID] | The insert ID of a successfull INSERT command. |
| [founditems]...[/founditems] | Normally you put a [founditems] loop inside a [SQLresult] context to retrieve the records resulting from a SQL SELECT statement, so you can display all the matching records. You can put any record set field name inside the [founditems] loop to display them in HTML.There are other SQL commands that will return a records set. For example, in MySQL, the following statements will return a record set: "show tables;" "show processlist;" "describe <table>;" |
Example WebDNA code:
It may sometimes be the case when any or all of the db fields are to be returned:
Executing 'Select * from mytable' will pull all field values into the record set. If the db fields are unknown, [listfields] will return the field names from the data set.
Example WebDNA code:
You can also use [field], inside [founditems], to retrieve field data by the fields position in the records set.
Example WebDNA code:
[SQLconnect dbType=MySQL&host=192.168.1.1&database=base&uid=sa&pwd=pass&conn_var=conn1]
Connected successfully
[/SQLconnect]
[SQLexecute conn_ref=conn1&result_var=rs1]
select firstName,lastName from employees;
[/SQLexecute]
[SQLresult result_ref=rs1]
[numfound] records found<br>
<table border=1><tr><th>First Name</th><th>Last Name</th></tr>
[founditems]
<tr><td>[firstName]</td><td>[lastName]</td></tr>
[/founditems]
</table>
[/SQLresult]It may sometimes be the case when any or all of the db fields are to be returned:
Executing 'Select * from mytable' will pull all field values into the record set. If the db fields are unknown, [listfields] will return the field names from the data set.
Example WebDNA code:
[SQLconnect dbType=MySQL&host=192.168.1.1&database=base&uid=sa&pwd=pass&conn_var=conn1]
[/SQLconnect]
[SQLexecute conn_ref=conn1&result_var=rs1]
select * from employees;
[/SQLexecute]
[SQLresult result_ref=rs1]
[numfound] records found<br>
<table border=1>
<tr>
[listfields]<th>[fieldname]</th>[/listfields]
</tr>
[founditems]
<tr>
[listfields]<td>[interpret][[fieldname]][/interpret]</td>[/listfields]
</tr>
[/founditems]
</table>
[/SQLresult]You can also use [field], inside [founditems], to retrieve field data by the fields position in the records set.
Example WebDNA code:
[SQLconnect dbType=MySQL&host=192.168.1.1&database=base&uid=sa&pwd=pass&conn_var=conn1]
[/SQLconnect]
[SQLexecute conn_ref=conn1&result_var=rs1]
select * from employees;
[/SQLexecute]
[SQLresult result_ref=rs1]
[founditems]
[loop start=1&end=[numfields]]
[field seek=ordinal:[index]&get=NAME]: <b>[field seek=ordinal:[index]&get=VALUE]</b> [hideif [index]=[numfields]]- [/hideif]
[/loop]<br>
[/founditems]
[/SQLresult]