Multiple Row Order Form MySQL

macman169 · April 24, 2009

Hi Everyone,

I am in need of some guidance. My company is working on ways to go "green" and save some money. One of the ways we have been doing this is by eliminating paper forms and going electronic. I have been converting our order forms to a web-based ordering system. The particular form I am working on now has a list of rows with columns (almost like a spreadsheet) that I need to insert to a database. The number of rows is an unknown because the order can be 1 item or can be as many as 10 items. I built a javascript function that allows the user to dynamically add rows as needed. The row id is then incremented. What I am looking for is a way to store these form rows to a MySQL database. I'm thinking the best way to do this would be to have a MySQL table just for these items and use one table row for each item and have them linked by the order number. Would this be a good way to go about it? If so, how do I store them in the database then retrieve them later as text boxes again to modify them? Just so you know, I am using Smarty as the templating system for this site.

The code is below. Thank you in advance for any suggestions you can give me. Oh, and if any of you know about a piece of code I can purchase that does this, that would be fine too.

Javascript for row adds

// JavaScript Document
function altRowColorNum(rowID)
		{

			var oTable = document.getElementById('tblBilling')


				for (var loop=0, max=oTable.rows.length; loop<max; loop++) {
            			oTable.rows[loop].cells[0].innerHTML = loop + 1;
						//reset row classes so background colors still alternate after row deletion or addition.

						if(loop % 2 == 0){ 
       							oTable.rows[loop].className = "odd";
							oTable.rows[loop].cells[0].className = "oddCellBorder";
							oTable.rows[loop].cells[1].className = "oddCellBorder";
							oTable.rows[loop].cells[2].className = "oddCellBorder";
							oTable.rows[loop].cells[3].className = "oddCellBorder";
							oTable.rows[loop].cells[4].className = "oddCellBorder";
							oTable.rows[loop].cells[5].className = "oddCellBorder";
							oTable.rows[loop].cells[6].className = "oddCellBorder";
							oTable.rows[loop].cells[7].className = "oddCellBorder";
							oTable.rows[loop].cells[8].className = "oddCellBorder";
     						}else{ 
       							oTable.rows[loop].className = "even";
							oTable.rows[loop].cells[0].className = "evenCellBorder";
							oTable.rows[loop].cells[1].className = "evenCellBorder";
							oTable.rows[loop].cells[2].className = "evenCellBorder";
							oTable.rows[loop].cells[3].className = "evenCellBorder";
							oTable.rows[loop].cells[4].className = "evenCellBorder";
							oTable.rows[loop].cells[5].className = "evenCellBorder";
							oTable.rows[loop].cells[6].className = "evenCellBorder";
							oTable.rows[loop].cells[7].className = "evenCellBorder";
							oTable.rows[loop].cells[8].className = "evenCellBorder";

     						}
        			}

		}

		function addRow()
		{
			var newRow = document.getElementById("billingTable").insertRow(-1);


			var rowID = newRow.rowIndex;

			var oCell = newRow.insertCell(0);
			oCell.appendChild(document.createTextNode(rowID))

			oCell = newRow.insertCell(1);
			oCell.innerHTML = "<input type='text' name='date[]' id='date_" + rowID + "' size='10' onfocus='this.select();lcs(this)' onclick='event.cancelBubble=true;this.select();lcs(this)'>";

			oCell = newRow.insertCell(2);
			oCell.innerHTML = "<input type='text' name='qty[]' id='qty_" + rowID + "' size='3'>";

			oCell = newRow.insertCell(3);
			oCell.innerHTML = "<input type='text' name='desc[]' id='desc_" + rowID + "' size='40'>";

			oCell = newRow.insertCell(4);
			oCell.innerHTML = "<input type='text' name='unitCost[]' id='unitCost_" + rowID + "' size='7'>";

			oCell = newRow.insertCell(5);
			oCell.innerHTML = "<input type='text' name='clientCost[]' id='clientCost_" + rowID + "' size='7'>";

			oCell = newRow.insertCell(6);
			oCell.innerHTML = "<select name='tax[]' id='tax_" + rowID + "'><option value='No'>No</option><option value='Yes'>Yes</option></select>";

			oCell = newRow.insertCell(7);
			oCell.innerHTML = "<input type='text' name='total[]' id='total_" + rowID + "' size='7'>";

			oCell = newRow.insertCell(;
			oCell.innerHTML = "<input type='button' value='Delete' onclick='removeRow(this);'/>"; 

			altRowColorNum(rowID);
		}

		function removeRow(row)
		{
			var rowNum = row.parentNode.parentNode.rowIndex;

			if (rowNum == 1)
			{
				alert("You cannot delete the first row.");
			}
			else
			{
				document.getElementById('billingTable').deleteRow(rowNum);

				var oTable = document.getElementById('tblBilling')


			altRowColorNum(rowNum);


			}


		}

HTML Code for Table

<table id="billingTable" cellpadding="5" cellspacing="0" align="center" width="830">
		<tr>
			<td> </td>
			<td class="tblTitle">Date</td>
			<td class="tblTitle">Qty</td>
			<td class="tblTitle">Description</td>
			<td class="tblTitle">Unit Cost</td>
			<td class="tblTitle">Client Cost</td>
			<td class="tblTitle">7% Tax</td>
			<td class="tblTitle" colspan="2">Total</td>
		</tr>
		<tbody id="tblBilling">
		<tr class="odd">
			<td class="oddCellBorder">1</td>
			<td class="oddCellBorder"><input name="date[]" id="date_1" type="text" size="10"/></td>
			<td class="oddCellBorder"><input name="qty[]" id="qty_1" type="text" size="3" /></td>
			<td class="oddCellBorder"><input name="desc[]" id="desc_1" type="text" size="40" /></td>
			<td class="oddCellBorder"><input name="unitCost[]" id="unitCost_1" type="text" size="7" /></td>
			<td class="oddCellBorder"><input name="clientCost[]" id="clientCost_1" type="text" size="7" /></td>
			<td class="oddCellBorder"><select name="tax[]"><option value="No">No</option><option value="Yes">Yes</option></select></td>
			<td class="oddCellBorder"><input name="total[]" id="total_1" type="text" size="7" /></td>
			<td class="oddCellBorder"><input type="button" value="Delete" onClick="removeRow(this);" /></td>

		</tr>
		</tbody>
</table>

jonsjava · April 24, 2009

Here's a collection of functions I've written to do just what you are asking. It takes pretty much any form data, sanitizes it, and inports it into a database. Each form can have different info, and the forms can change, without hurting the output. I'll do this in a few steps. First, I'll give you the code, then I'll start writing up a how-to for using this code.

the functions page:

<?php
/*Table info
*
*
*	CREATE TABLE `TABLE_NAME` (
*	  `id` int(7) NOT NULL auto_increment,
*	  `data` varchar(9000) collate latin1_general_ci NOT NULL,
*	  `date` int(14) NOT NULL,
*	  PRIMARY KEY  (`id`)
*	) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
* 
*
/* ***************************** */
/* Database info. MUST BE SET!   */
/* ***************************** */
$host = "localhost";
$db = "database";
$table_name = "table_name";
$db_user = "user";
$db_password = "pass";
/* ***************************** */
/* END Database info.            */
/* ***************************** */
$link = mysql_connect($host, $db_user, $db_password);
mysql_select_db($db, $link) or die(mysql_error());

function cleanInput($input, $is_get=false){
if (is_array($input)){
	$new_array22 = array();
	foreach ($input as $key => $value){
		$value = checkForAttack($value, $is_get);
		$new_array22[$key] = mysql_real_escape_string($value);
	}
	$input = array();
	foreach ($new_array22 as $key=>$value){
		$input[$key] = $value;
	}
}
else{
	$$input = checkForAttack($input, $is_get);
	$input = mysql_real_escape_string($input);
}
$input['ip_address'] = $_SERVER['REMOTE_ADDR'];
return $input;
}

function checkForAttack($input, $is_get=false){
if ($is_get != false && stristr($input, "http")){
	$subject = "Hacking (GET) attempt on your website!";
	$to = "admin_email_address_goes_here";
	$their_ip = $_SERVER['REMOTE_ADDR'];
	$date = date("n/j/Y Hi s:u");
	$whois = shell_exec("/usr/bin/whois $their_ip");
	$page = $_SERVER['PHP_SELF'];
	$headers = "From: admin_email_address_goes_here";
	$message = "IP Address: $their_ip\n
	date Attempted: $date\n
	Page attacked: $page\n
	Data Passed: $input\n
	Who Is Info:\n\n
	$whois\n";
	mail($to, $subject, $message, $headers);
	session_unset();
	session_destroy();
	header("location:".$input);
	exit();
}
elseif (stristr($input, "SELECT *") || stristr($input, "INSERT INTO" || stristr($input, "DESCRIBE TABLE")) || stristr($input, "OR 1")){
	$subject = "Hacking attempt on your website!";
	$to = "admin_email_address_goes_here";
	$their_ip = $_SERVER['REMOTE_ADDR'];
	$date = date("n/j/Y Hi s:u");
	$whois = shell_exec("/usr/bin/whois $their_ip");
	$page = $_SERVER['PHP_SELF'].$_SERVER['QUERY_STRING'];
	$headers = "From: admin_email_address_goes_here";
	$message = "IP Address: $their_ip\n
	date Attempted: $date\n
	Page attacked: $page\n
	Data Passed: $input\n
	Who Is Info:\n\n
	$whois\n";
	mail($to, $subject, $message, $headers);
}
return $input;
}
/**
* Anti-Spam function. It takes the 344 most common spam words, 
* and compares input to the list.  Returns array containing
* number found, the actual text found, and a score for the input
*
* @param unknown_type $input : the data you want to check
* @return array
*/
function stopSpam($input){
$count = 0;
$data_array = array("'hidden' assets","-online","3.28","4u","accept credit cards",
"act now! don't hesitate!","additional income","addresses on cd","adipex","advicer",
"all natural","amazing stuff","apply online","as seen on","auto email removal",
"avoid bankruptcy","baccarrat","be amazed","be your own boss","being a member",
"big bucks","bill 1618","billing address","blackjack","bllogspot","booker",
"brand new pager","bulk email","buy direct","buying judgments","byob","cable converter",
"call free","call now","calling creditors","can't live without","cancel at any time",
"cannot be combined with any other offer","car-rental-e-site","car-rentals-e-site",
"carisoprodol","cash bonus","cashcashcash","casino","casinos","cell phone cancer scam",
"cents on the dollar","chatroom","check or money order","cialis","click below",
"click here link","click to remove","click to remove mailto","collect child support",
"compare rates","compete for your business","confidentially on all orders",
"congratulations","consolidate debt and credit","coolcoolhu","coolhu",
"copy accurately","copy dvds","credit bureaus","credit card offers","credit-card-debt",
"credit-report-4u","cures baldness","cwas","cyclen","cyclobenzaprine","dating-e-site",
"day-trading","dear email","dear friend","dear somebody","debt-consolidation",
"debt-consolidation-consultant","dig up dirt on friends","direct email","direct marketing",
"discreetordering","discusses search engine listings","do it today","don't delete",
"drastically reduced","duty-free","dutyfree","earn per week","easy terms",
"eliminate bad credit","email harvest","email marketing","equityloans",
"expect to earn","fantastic deal","fast viagra delivery","financial freedom",
"find out anything","fioricet","flowers-leading-site","for free","for instant access",
"for just $","free access","free cell phone","free consultation","free dvd",
"free grant money 	","free hosting","free installation","free investment",
"free leads","free membership","free money","free offer","free preview",
"free priority mail","free quote","free sample","free trial","free website",
"freenet","freenet-shopping","full refund","gambling-","get it now","get paid",
"get started now","gift certificate","great offer","guarantee","hair-loss",
"have you been turned down?","health-insurancedeals-4u","hidden assets",
"holdem","holdempoker","holdemsoftware","holdemtexasturbowilson","home employment",
"homeequityloans","homefinance","hotel-dealse-site","hotele-site","hotelse-site",
"human growth hormone","if only it were that easy","in accordance with laws","incest",
"increase sales","increase traffic","insurance","insurance-quotesdeals-4u",
"insurancedeals-4u","investment decision","it's effective","join millions of americans",
"jrcreations","laser printer","levitra","limited time only","long distance phone offer",
"lose weight spam","lower interest rates","lower monthly payment","lowest price",
"luxury car","macinstruct","mail in order form","marketing solutions","mass email",
"meet singles","member stuff","message contains disclaimer","mlm","money back",
"money making","month trial offer","more internet traffic","mortgage rates",
"mortgage-4-u","mortgagequotes","multi level marketing","name brand",
"new customers only","new domain extensions","nigerian","no age restrictions",
"no catch","no claim forms","no cost","no credit check","no disappointment",
"no experience","no fees","no gimmick","no inventory","no investment","no medical exams",
"no middleman","no obligation","no purchase necessary","no questions asked","no selling",
"no strings attached","not intended","off shore","offer expires","offers coupon",
"offers extra cash","offers free (often stolen) passwords","once in lifetime",
"one hundred percent free","one hundred percent guaranteed","one time mailing",
"online biz opportunity","online biz opportunity 	","online pharmacy",
"online-gambling","onlinegambling-4u","only $","opportunity","opt in","order now",
"order status","orders shipped by priority mail","ottawavalleyag","outstanding values",
"ownsthis","palm-texas-holdem-game","paxil","penis","pennies a day",
"people just leave money laying around","pharmacy","phentermine","please read",
"poker-chip","potential earnings","poze","print form signature","print out and fax",
"produced and sent out","profits","promise you ...!","pure profit","pussy","real thing",
"refinance home","removal instructions","remove in quotes","remove subject",
"removes wrinkles","rental-car-e-site","reply remove subject",
"requires initial investment","reserves the right","reverses aging","ringtones",
"risk free","roulette ","round the world","s 1618","safeguard notice",
"satisfaction guaranteed","save $","save big money","save up to","score with babes",
"search engine listings","section 301","see for yourself","sent in compliance",
"serious cash","serious only","shemale","shoes","shopping spree","sign up free today",
"slot-machine","social security number","special promotion","stainless steel",
"stock alert","stock pick","stop snoring","strong buy","stuff on sale",
"subject to credit","supplies are limited","take action now","terms and conditions",
"texas-holdem","the best rates","the following form","they keep your money -- no refund!",
"they're just giving it away","this isn't junk","this isn't spam","thorcarlson",
"top-e-site","top-site","tramadol","trim-spa","ultram","university diplomas","unlimited",
"unsecured credit/debt","urgent","us dollars","vacation offers",
"valeofglamorganconservatives","viagra","viagra and other drugs","vioxx",
"wants credit card","we hate spam","we honor all","weekend getaway",
"what are you waiting for?","while supplies last","while you sleep",
"who really wins?","why pay more?","will not believe your eyes","winner","winning",
"work at home","xanax","you are a winner","you have been selected","your income","zolus");
foreach ($data_array as $value){
	if (stristr($input, $value)){
		$array['bad_item'][] = $value;
		$count++;
	}
}
$array['item'][] = $input;
$total_spam_vars = count($data_array);
$score = ($count / $total_spam_vars) * 1000;
$array['total_found'] = $count;
$array['score'] = $score;
return $array;
}
function calcDate($time_back){
return date('U', strtotime("-$time_back day"));
}
function importLeads($array, $form, $table_name){
foreach ($array as $key=>$value){
	$value = str_replace("\"", "'", $value);
	if (!isset($count)){
		$output = "$key;;;;;$value";
		$count = 1;
	}
	else{
		$output .= ":::::$key;;;;;$value";
	}
}
$date = date("U");
$sql = "INSERT INTO `$table_name` VALUES('','$output', '$date', '$form');";
mysql_query($sql);
}
function getMasterKeys($lead){
$data_array = explode(":::::", $lead);
$count = count($data_array) - 1;
while ($count >= 0){
	$temp_array = explode(";;;;;", $data_array[$count]);
	if (strtolower($temp_array[0]) != "submit"){
		$output[$count] = $temp_array[0];
	}
	$count--;
}
sort($output);
return $output;
}
function parseCSV($lead, $masterKeys){
$masterkeyCount = count($masterKeys);
$data_array = explode(":::::", $lead);
$count = count($data_array) - 1;
$array_count = count($data_array);
while ($count >= 0){
	$temp_array = explode(";;;;;", $data_array[$count]);
	if (strtolower($temp_array[0]) != "submit"){
		$output[$temp_array[0]] = $temp_array[1];
	}
	$count--;
}
foreach ($masterKeys as $value){
	if (!array_key_exists($value, $output)){
		$output[$value] = "";
	}
}
ksort($output);
$outdata = "";
foreach ($output as $value){
	$outdata .= ",\"{$value}\"";
}
return $outdata;
}

function getCSV($timeframe, $form, $table_name){
$time_back = calcDate($timeframe);
$sql = "SELECT * FROM `$table_name` WHERE `date` >= $time_back  AND `form_name`='$form' ORDER BY `id` DESC;";
$result = mysql_query($sql);
$firstrow = "\"Date/Time\"";
$secondrow = "";
$headers_got = false;
$count = 0;
while ($row = mysql_fetch_assoc($result)){
	$data = $row['data'];
	$date = date("Y-m-d H:i:s", $row['date']);
	$secondrow .="\n\"$date\"";
	if ($count == 0){
		$masterKey = getMasterKeys($data);
		foreach ($masterKey as $value){
			$firstrow .= ",\"{$value}\"";
		}
		$count++;
	}
	$secondrow .= parseCSV($data, $masterKey);
}
$output = $firstrow.$secondrow;
$filename = "{$form}_{$table_name}_".date("jMY_Hi", strtotime("-365 day"))."-".date("jMY_Hi").".csv";
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
print "$output";
}

the admin page

<?php
session_start();
$forms = array();
$forms[] = "form_name_1";
$forms[] = "form_name_1";
$forms[] = "form_name_1";
$forms[] = "form_name_1";
$forms[] = "form_name_1";
$forms[] = "form_name_1";
sort($forms);
$admin_username = "admin";
$admin_pass = "setMe";
if (isset($_SESSION['valid_user']) && $_SESSION['valid_user'] == true){
if (isset($_POST['time']) && is_numeric($_POST['time']) && in_array($_POST['form'], $forms) && isset($_POST['increment']) && is_numeric($_POST['increment'])) {
	$time = $_POST['time'] * $_POST['increment'];
	include("functions.inc.php");
	getLeads($time, $_POST['form']);
}
elseif (isset($_GET['go']) && $_GET['go'] == "logout"){
	session_unset();
	session_destroy();
	header("location:?");
	exit();
}
else{
	?>
	<html>
		<head>
			<title>Lead Form Admin</title>
		</head>
		<body>
			<center><p><img src="your_logo.jpg"></p><a href="?go=logout">Logout</a>
				<form method="POST" action="?">
					<table border="0">
						<tr>
							<td>Form</td>
							<td><select name="form">
									<?php foreach ($forms as $value){
										print "<option value=\"$value\">$value</option>\n";
									}
									?></select></td>
						</tr>
						<tr>
							<td>Go back</td>
							<td><input type="text" name="time"><select name="increment">
									<option value="1" selected>Day(s)</option>
									<option value="7">Week(s)</option>
									<option value="30">Month(s)(30 days each month)</option>
									<option value="365">Year(s)(365 days each year)</option>
									</select></td>
						</tr>
						<tr>
							<td> </td>
							<td><input type="submit" value="Submit"></td>
						</tr>
					</table>
				</form>
			</center>
		</body>
	</html>
	<?php
}
}
elseif (isset($_POST['user']) && isset($_POST['pass']) && (($_POST['pass'] == $admin_pass) &&( $_POST['user'] == $admin_username))){
$_SESSION['valid_user'] = true;
header("location:?");
exit();
}
else{
?>
<html>
	<head>
		<title>Please Log In</title>
	</head>
	<body>
		<center><p><img src="your_logo.jpg"></p>
			<form method="POST" action="?">
				<table border="0">
					<tr>
						<td>Username:</td>
						<td><input type="text" name="user"></td>
					</tr>
					<tr>
						<td>Password:</td>
						<td><input type="password" name="pass"></td>
					</tr>
					<tr>
						<td> </td>
						<td><input type="submit" value="Login"></td>
					</tr>
				</table>
			</form>
		</center>
	</body>
</html>
<?php
}
?>

jonsjava · April 24, 2009

A simple method for using the script (which may cause some unknown issues, because it wasn't written to be used this way) is like this:

form processor

<?php
include("functions.inc.php");
$clean = cleanInput($_POST);
importLeads($clean, "form_name_1");
?>

a nicer method (but won't work if you have dynamically-generated fields) is to define all fields:

<?php
$formfields = array("subject", "name", "organization", "city_state", "telephone", "email", "quantity", "standby", "portable", "trailer",
				"PTO", "other_type", "under15", "a15to24", "a25to49", "a50to99", "a100to300", "a300to2mw","other", "industry",
				"destination", "timeline","description");
foreach ($formfields as $value){
if (empty($_POST[$value]) || !isset($_POST[$value])){
	$data_array[$value] = "not set";
}
else{
	$data_array[$value] = $_POST[$value];
}
}
$clean = cleanInput($data_array);
importLeads($clean, "form_name_1");
?>

This way, you keep all fields in order, and it will output the proper table headers when exporting to csv (part of the admin page).

Sign In

Multiple Row Order Form MySQL

Recommended Posts

macman169

Link to comment

Share on other sites

jonsjava

Link to comment

Share on other sites

jonsjava

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information