<%@page import="java.sql.*"%>
<%--
/*******************************************************************************
This script simulates a spreadsheet using any MySQL table with 4 columns (fields).
The first column must be an auto_increment integer named "id" but the rest you
can rename using the config variables below. Created by Neil Moomey
www.neilmoomey.com Feel free to use it as you wish. I only ask you give me credit.
Here is an example of how to set up a table and field names.
SQL query to create the table phone_book:
create table phone_book(
id integer not null auto_increment,
first_name varchar(50),
last_name varchar(50),
phone varchar(50),
primary key (id)
)
Now change these variables to fit the table you just created:
String table = "phone_book";
String field1 = "first_name";
String field1_label = "First Name";
String field2 = "last_name";
String field2_label = "Last Name";
String field3 = "phone";
String field3_label = "Phone";
String db_host = "localhost";
String db_user = "";
String db_pass = "";
String db = "";
*******************************************************************************/
--%>
<%
String table = "phone_book";
String field1 = "first_name";
String field1_label = "First Name";
String field2 = "last_name";
String field2_label = "Last Name";
String field3 = "phone";
String field3_label = "Phone";
String db_host = "localhost";
String db_user = "";
String db_pass = "";
String db = "";
// End of variables definitions. No need to edit code beyond this line
String query = "";
ResultSet result;
String url = "jdbc:mysql://localhost/" + db;
Connection con = null;
String sort = request.getParameter("sort")+"";
String read = request.getParameter("read")+"";
String keyword = request.getParameter("keyword")+"";
String update = request.getParameter("update")+"";
String delete = request.getParameter("delete")+"";
String insert = request.getParameter("insert")+"";
String field1_value = request.getParameter("field1_value")+"";
String field2_value = request.getParameter("field2_value")+"";
String field3_value = request.getParameter("field3_value")+"";
String driver = "org.gjt.mm.mysql.Driver";
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url, db_user, db_pass);
Statement select = con.createStatement();
%>
<%
//insert into table ("field1","field2","field3") values ("field1_value","field2_value","field3_value")
if (insert.equals("1")) query = "insert into " + table +"(" + field1 + "," + field2 + "," + field3 + ") values (\"" + field1_value + "\",\"" + field2_value + "\",\"" + field3_value + "\")";
//update table set field1="field1_value", field2="field2_value",field3="field3_value" where id=update";
if (!update.equals("null")) query = "update " + table + " set " + field1 + "=\"" + field1_value + "\", " + field2 + "=\"" + field2_value + "\"," + field3 + "=\"" + field3_value + "\" where id=" + update;
//delete from table where id=delete
if (!delete.equals("null")) query = "delete from " + table + " where id=" + delete;
// Execute query if there is one.
if (!query.equals("")) result = select.executeQuery(query);
query = "select * from " + table;
if (sort.equals("null")) sort=field1;
if (sort.equals("id")) query+=" order by id";
if (sort.equals(field1)) query+=" order by " + field1;
if (sort.equals(field2)) query+=" order by " + field2;
if (sort.equals(field3)) query+=" order by " + field3;
if (!read.equals("null")) {
query = "select * from " + table + " where id=" + read;
}
if (!keyword.equals("null")) {
query = "select * from " + table + " where " + field1 + " LIKE '%" + keyword + "%' OR " + field2 + " LIKE '%" + keyword + "%' OR " + field3 + " LIKE '%" + keyword + "%'";
}
result = select.executeQuery(query);
%>