LUA Sqlite Working code with Create , Insert , select : Tested on Xoop Server


LUA Sqlite Working code with Create , Insert , select : Tested on Xoop Server

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
    <title>CGILua SQLITE Test PRAGS</title>
    <link rel="stylesheet" href="css/style.css" type="text/css"/>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
	<script src="js/jquery-1.7.min.js" type="text/javascript"></script>
	<script type="text/javascript">
	function check(){
	//alert('here');
	if($('#name').val() != '' && $('#address').val() != ''){
	return ture;
	}
	else{
	alert('Please enter name and address first');
	return false;
	}

	}
	</script>
</head>
<body>
<div id="container">
<div id="product">
	<div id="product_logo"><a href="http://pragneshkaria.com">
		<img src="images/pragneshkaria.png" alt="Pragnesh Karia" /></a>
    </div>
</div> 
<div id="main">
<div id="navigation">
<p>Today is: <br />
	<%= os.date() %>
	
	</p>
</div> <!-- id="navigation" -->

<div id="content">
<h2>SQLITE Form Handling</h2>
<form method="post" action="luasqlite.lp">
    <label>User name: </label><input name="name" id="name" type="text" >
    <label>User Address: </label><input name="address" id="address" type="text">
    <input type="submit" value="Post it" onClick="return check();">
    <input type="reset" value="Reset">
</form>
<p>
Values: User name = <%= cgilua.POST.name or "(not set)"%>, Address = <%= cgilua.POST.address or "(not set)"%>
</p>
<%
local put = cgilua.put;
require "luasql.sqlite3"
env = luasql.sqlite3()
con = env:connect("luasql_pk.sqlite")-- reset our table


if cgilua.POST.name ~= nil and cgilua.POST.address ~= nil  then

	function getrows(table)
	local sql_stmt = "SELECT max(id) as id FROM "..table
	-- print(sql_stmt)
	cur1 = assert(con:execute(sql_stmt))
	local row1 = cur1:fetch ({},"a")
	local pk1 = row1.id
	if pk1  then
	  return pk1+1
	end
	return 1
	end


	local n = cgilua.POST.name
	local a = cgilua.POST.address
	local pk = getrows('people')
	local time = os.date()
	local query = "Insert into people values('"..pk.."','"..n.."','"..a.."','"..time.."','"..time.."')"
	--print(n);
	--print(a);
	assert(con:execute(query))
	cur1:close()


	
else
  -- print('PLEASE ENTER PROPERLY')
end



--res = con:execute"DROP TABLE people"
res = assert (con:execute[[CREATE TABLE IF NOT EXISTS "people" (
					"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
					"name"  varchar(50),
					"address"  varchar(50),
					"created_at" DATETIME,
					"updated_at" TIMESTAMP
					)
				   ]])
	
	
	
cur = assert (con:execute"SELECT id ,name, address from people")
row = cur:fetch ({}, "a")
--print("Total Records : "..cur:numrows())
cgilua.put('<table class="tbl" border="1">');
cgilua.put('<tr>');
cgilua.put('<th>Id</th><th>Name</th><th>Address</th>');
cgilua.put('</tr>');
    while row do
      --print(string.format("ID: %s,Name: %s, Address: %s", row.id,row.name, row.address))
        cgilua.put('<tr>');
        cgilua.put('<td>'..string.format(row.id)..'</td>'); -- string concat with ..
        cgilua.put('<td>'..string.format(row.name)..'</td>');
        cgilua.put('<td>'..string.format(row.address)..'</td>');
        cgilua.put('</tr>');
        row = cur:fetch (row, "a")
    end
cgilua.put('</table>');
--close everything





cur:close()
con:close()
env:close()
	
	
%>
</div> <!-- id="content" -->
</div> <!-- id="main" -->
</div> <!-- id="container" -->
</body>
</html>

output lua sqlite

output lua sqlite

Share on Facebook




About Pragnesh Karia

Pragnesh Karia, Open Source Enthusiast, Software Professional, Software Developer, Technical Lead ,Magento, Joomla ,Joomla LMS , Moodel LMS ,PHP ,Mysql, Ajax, Javascript, Jquery, Linux, Fan of Open Sources , Annet Technologies , SEO Analyst , Mootools