Use SQL and HTML with Lightweight Language

In this page, I'll show you how to use SQL and HTML simply. If you can write program almost in HTML and SQL, development process become very simple.

In this page, focusing on SELECT sentence of SQL, because this sentence returuns table data. The INSERT, UPDATE and DELETE do not return value, so just write and execute them.

Sample of SELECT sentence in the sample project
Adjust where section
Edit variables in the script

Sample of SELECT sentence in the sample project

In the sample project, program and HTML in "/ALINOUS_HOME/select/" folder is good to learn it.

The script "show.alns" is good example.

show.alns


// EXECUTE SELECT STATEMENT
if($IN.comment != null){
	$LIKE_STMT = '%' + $IN.comment + '%';
}


SELECT * INTO RECORDS FROM SAMPLE_TABLE 
		WHERE
			NAME = $IN.name AND
			EMAIL = $IN.mail AND
			COMMENT like $LIKE_STMT
		ORDER BY NAME DESC
LIMIT 10
ADJUST_WHERE = TRUE;

// Making colors for the back ground of TD tag for each record.
for($i = 0; $i < Array.size(@RECORDS); $i++){
	if($i % 2 == 0){
		$RECORDS[$i].BGCOL = "#EFEFEF";
	}
	else{
		$RECORDS[$i].BGCOL = "#FFFFFF";
	}
}

return 0;

This script executes SELECT. The SELECT sentence has "INTO" clause. This specify array variable to return the result records of SELECT sentence.

The form before execute "show.alns" is "index.html". The form in this HTML posts form parameter to "show.alns". You can specify parameter by "$IN" variable.

index.html


<form name="searchForm" action="show.html" method="POST">
	
	<table>
		<tr>
			<td>NAME : </dt>
			<td><input type="text" name="name" alns:ignoreBlank="true"></td>
		</tr>
		<tr>
			<td>E-MAIL : </td>
			<td><input type="text" name="mail" alns:ignoreBlank="true"></td>
		</tr>
		<tr>
			<td>COMMENT : </td>
			<td><input type="text" name="comment" 
					alns:ignoreBlank="true"></td>
		</tr>
	</table>
	
	<input  name="submit" type="submit" value="Search"/>
</form>

After adjusting returned variable. show them in the HTML.

Adjust where section

I guess you find "ADJUST_WHERE = TRUE" in the SELECT sentence.


SELECT * INTO RECORDS FROM SAMPLE_TABLE 
		WHERE
			NAME = $IN.name AND
			EMAIL = $IN.mail AND
			COMMENT like $LIKE_STMT
		ORDER BY NAME DESC
LIMIT 10
ADJUST_WHERE = TRUE;

This means if the expression contains NULL variable, automatically ignore the expression.

Auto adjusting where clause

Auto adjusting where clause and delete AND

When the form parameter's value is ""(blank string) and the attribute nemed "alns:ignoreBlank" set true, the parameter's value become NULL.


<input type="text" name="comment" alns:ignoreBlank="true">

Edit variables in the script

The array variable returned by SELECT sentence is normal variable. So you can edit it in the script.


// Making colors for the back ground of TD tag for each record.
for($i = 0; $i < Array.size(@RECORDS); $i++){
	if($i % 2 == 0){
		$RECORDS[$i].BGCOL = "#EFEFEF";
	}
	else{
		$RECORDS[$i].BGCOL = "#FFFFFF";
	}
}