[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Checking for numerical consistency in a large web table (php/mysql)

Posted on 2014-07-14
21
Medium Priority
?
327 Views
Last Modified: 2014-10-16
I have a site where users need to enter in choices for a ballot rated from 1 to 75. The ballot is for 25 areas with 3 different beats/periods.
Not all boxes need to be filled and it can vary from a single choice, to several, to all.

The issue is that due to some users personal preference for a booking they want to choose number 1 on an area for all 3 beats.
I'm currently unsure on the approach on how to look at the data and spot inconsistencies in this fashion so that if someone is silly enough to choose say Site 3 - periods A, B & C by choosing 1,1,1 rather than say 1,3,2 or any fashion over the 25 areas, that I can have it fail the addition and have them go back (basically a checking function would be required).
The data is collated for MySQL in this fashion:
1A_46,1B_47,1C_48,2A_28,2B_29,2C_30,5A_43,5B_44,5C_45,6A_25,6B_26,6C_27,7A_10,7B_11,7C_12,9A_4,9B_5,9C_6,10A_1,10B_2,10C_3,11A_7,11B_8,11C_9,14A_22,14B_23,14C_24,15A_40,15B_41,15C_42,16A_19,16B_20,16C_21,17A_31,17B_32,17C_33,18A_16,18B_17,18C_18,19A_13,19B_14,19C_15,21A_34,21B_35,21C_36,22A_37,22B_38,22C_39

Open in new window

With say 1A being the area & beat and 46 being the choice preference.

In the reporting side I have it already set up to reverse the above data layout so it's preference followed by block/beat to make it easier for others to work out.

Any ideas on a formula to check for consistent data?
0
Comment
Question by:kiwistag
  • 10
  • 9
20 Comments
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 40194225
I would start with the form.  What does that look like (code)?   Some js on the front side will help.   You still need the back end.

Is this bad or good?
1A_46,1B_47,1C_48,2A_28,2B_29,2C_30,
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40194255
Please post the CREATE TABLE statement(s) and the HTML forms used to gather the input.  My sense from looking at the question and the code snippet is that the application is misdesigned, but I'd like to be sure I understand what's really going on with the code and data before I make a recommendation.
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40196522
Hi Ray: The data is posted into an existing table. The results from the block/beat setup is written in this format to a text column.

MySQL:
CREATE TABLE  `Ballot_2014` (
  `RequestNo` int(10) NOT NULL AUTO_INCREMENT,
  `PartyNumbers` varchar(50) DEFAULT NULL,
  `RequestorNo` int(7) DEFAULT NULL,
  `Members` varchar(50) DEFAULT NULL,
  `Blocks_Beats` text,
  `CancelOptions` varchar(8) DEFAULT '',
  `Notes` text,
  `TransactionID` varchar(90) DEFAULT NULL,
  `Submitted` datetime DEFAULT NULL,
  PRIMARY KEY (`RequestNo`),
  KEY `UID` (`RequestNo`)) ENGINE=InnoDB AUTO_INCREMENT=481 DEFAULT CHARSET=latin1;

Open in new window


HTML:
<html>

<head>
<meta http-equiv="Content-Language" content="en-nz">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Ballot Allocation Request</title>
<style type="text/css">
.wrapper {
	width: 950px;
}
.style1 {
	text-align: center;
}
.style2 {
	text-decoration: underline;
}
option.opt1 {
	background: #F1F1F1;
}
option.opt2 {
	background: #0088FF;
}
</style>

</head>

<body style="background-color: #EEEEEE">
  <div class="wrapper">
<p class="style1">
&nbsp;</p>
	  <form method="POST" action="brqprty.php" name="chseblk">

	<p>&nbsp;<table border="1" width="500" id="table1">
	<tr>
		<td width="230"><b>Block</b></td>
		<td width="90"><b>1st Period</b></td>
		<td width="90"><b>2nd Period</b></td>
				<td width="90"><b>3rd Period</b></td> 	</tr>
	<tr>
		<td width="225">1: </td>
		<td width="90"><input type="text" name="blkA1" size="10"></td>
		<td width="90"><input type="text" name="blkB1" size="10"></td>
				<td width="90"><input type="text" name="blkC1" size="10"></td> 	</tr>
	<tr>
		<td width="225">2: </td>
		<td width="90"><input type="text" name="blkA2" size="10"></td>
		<td width="90"><input type="text" name="blkB2" size="10"></td>
				<td width="90"><input type="text" name="blkC2" size="10"></td> 	</tr>
	<tr>
		<td width="225">3: </td>
		<td width="90"><input type="text" name="blkA3" size="10"></td>
		<td width="90"><input type="text" name="blkB3" size="10"></td>
				<td width="90"><input type="text" name="blkC3" size="10"></td> 	</tr>
	<tr>
		<td width="225">4: </td>
		<td width="90"><input type="text" name="blkA4" size="10"></td>
		<td width="90"><input type="text" name="blkB4" size="10"></td>
				<td width="90"><input type="text" name="blkC4" size="10"></td> 	</tr>
	<tr>
		<td width="225">5: </td>
		<td width="90"><input type="text" name="blkA5" size="10"></td>
		<td width="90"><input type="text" name="blkB5" size="10"></td>
				<td width="90"><input type="text" name="blkC5" size="10"></td> 	</tr>
	<tr>
		<td width="225">6: </td>
		<td width="90"><input type="text" name="blkA6" size="10"></td>
		<td width="90"><input type="text" name="blkB6" size="10"></td>
				<td width="90"><input type="text" name="blkC6" size="10"></td> 	</tr>
	<tr>
		<td width="225">7: </td>
		<td width="90"><input type="text" name="blkA7" size="10"></td>
		<td width="90"><input type="text" name="blkB7" size="10"></td>
				<td width="90"><input type="text" name="blkC7" size="10"></td> 	</tr>
	<tr>
		<td width="225">8: </td>
		<td width="90"><input type="text" name="blkA8" size="10"></td>
		<td width="90"><input type="text" name="blkB8" size="10"></td>
				<td width="90"><input type="text" name="blkC8" size="10"></td> 	</tr>
	<tr>
		<td width="225">9: </td>
		<td width="90"><input type="text" name="blkA9" size="10"></td>
		<td width="90"><input type="text" name="blkB9" size="10"></td>
				<td width="90"><input type="text" name="blkC9" size="10"></td> 	</tr>
	<tr>
		<td width="225">10: </td>
		<td width="90"><input type="text" name="blkA10" size="10"></td>
		<td width="90"><input type="text" name="blkB10" size="10"></td>
				<td width="90"><input type="text" name="blkC10" size="10"></td> 	</tr>
	<tr>
		<td width="225">11: </td>
		<td width="90"><input type="text" name="blkA11" size="10"></td>
		<td width="90"><input type="text" name="blkB11" size="10"></td>
				<td width="90"><input type="text" name="blkC11" size="10"></td> 	</tr>
	<tr>
		<td width="225">12: </td>
		<td width="90"><input type="text" name="blkA12" size="10"></td>
		<td width="90"><input type="text" name="blkB12" size="10"></td>
				<td width="90"><input type="text" name="blkC12" size="10"></td> 	</tr>
	<tr>
		<td width="225">13: </td>
		<td width="90"><input type="text" name="blkA13" size="10"></td>
		<td width="90"><input type="text" name="blkB13" size="10"></td>
				<td width="90"><input type="text" name="blkC13" size="10"></td> 	</tr>
	<tr>
		<td width="225">14: </td>
		<td width="90"><input type="text" name="blkA14" size="10"></td>
		<td width="90"><input type="text" name="blkB14" size="10"></td>
				<td width="90"><input type="text" name="blkC14" size="10"></td> 	</tr>
	<tr>
		<td width="225">15: </td>
		<td width="90"><input type="text" name="blkA15" size="10"></td>
		<td width="90"><input type="text" name="blkB15" size="10"></td>
				<td width="90"><input type="text" name="blkC15" size="10"></td> 	</tr>
	<tr>
		<td width="225">16: </td>
		<td width="90"><input type="text" name="blkA16" size="10"></td>
		<td width="90"><input type="text" name="blkB16" size="10"></td>
				<td width="90"><input type="text" name="blkC16" size="10"></td> 	</tr>
	<tr>
		<td width="225">17: </td>
		<td width="90"><input type="text" name="blkA17" size="10"></td>
		<td width="90"><input type="text" name="blkB17" size="10"></td>
				<td width="90"><input type="text" name="blkC17" size="10"></td> 	</tr>
	<tr>
		<td width="225">18: </td>
		<td width="90"><input type="text" name="blkA18" size="10"></td>
		<td width="90"><input type="text" name="blkB18" size="10"></td>
				<td width="90"><input type="text" name="blkC18" size="10"></td> 	</tr>
	<tr>
		<td width="225">19: </td>
		<td width="90"><input type="text" name="blkA19" size="10"></td>
		<td width="90"><input type="text" name="blkB19" size="10"></td>
				<td width="90"><input type="text" name="blkC19" size="10"></td> 	</tr>
	<tr>
		<td width="225">20: </td>
		<td width="90"><input type="text" name="blkA20" size="10"></td>
		<td width="90"><input type="text" name="blkB20" size="10"></td>
				<td width="90"><input type="text" name="blkC20" size="10"></td> 	</tr>
	<tr>
		<td width="225">21: </td>
		<td width="90"><input type="text" name="blkA21" size="10"></td>
		<td width="90"><input type="text" name="blkB21" size="10"></td>
				<td width="90"><input type="text" name="blkC21" size="10"></td> 	</tr>
	<tr>
		<td width="225">22: </td>
		<td width="90"><input type="text" name="blkA22" size="10"></td>
		<td width="90"><input type="text" name="blkB22" size="10"></td>
				<td width="90"><input type="text" name="blkC22" size="10"></td> 	</tr>
	<tr>
		<td width="225">23: </td>
		<td width="90"><input type="text" name="blkA23" size="10"></td>
		<td width="90"><input type="text" name="blkB23" size="10"></td>
				<td width="90"><input type="text" name="blkC23" size="10"></td> 	</tr>
	<tr>
		<td width="225">24: </td>
		<td width="90"><input type="text" name="blkA24" size="10"></td>
		<td width="90"><input type="text" name="blkB24" size="10"></td>
				<td width="90"><input type="text" name="blkC24" size="10"></td> 	</tr>
	<tr>
		<td width="225">25: </td>
		<td width="90"><input type="text" name="blkA25" size="10"></td>
		<td width="90"><input type="text" name="blkB25" size="10"></td>
				<td width="90"><input type="text" name="blkC25" size="10"></td> 	</tr>
</table></p>
<p><input type="checkbox" name="cannotify" value="1" onclick="javascript:document.chseblk.prefblok.disabled=false">Keep 
Application Form in case of a cancellation<br>
&nbsp;&nbsp; <input type="checkbox" name="prefblok" value="1" disabled>If Yes, 
preferred blocks listed above<br>
&nbsp;&nbsp; <input type="checkbox" name="colph" value="1"> By collect phone 
call at short notice</p>
	<p>&nbsp;</p>
<p><input type="submit" value="Submit" name="submitbutton" disabled="disabled"> <input type="reset" value="Reset" name="B2"><br/>
		  </p>
</form>
</div>
</body>
</html>

Open in new window

The next web page on submit processes on variable variables.

So basically:
<?php
.............

$bclp = 0;
while ($bclp < $numB) {

	$blnum=mysql_result($resultB,$bclp,"BlockNumber");
	$blkrunA = "blkA" . $blnum;

if ($$blkrunA != null and ctype_digit($$blkrunA)==TRUE) {
	// echo "$blkrunA<br>";
	$blkchseA = $blnum . "A" . "_" . $$blkrunA;
	//$blkchseA = $blnum . $blbeat . "_" . $bpref;
if ($blkchseN==null) { $blkchseN = $blkchseA;
} else {
	$blkchseN = $blkchseN . "," . $blkchseA; }
}

	$blkrunB = "blkB" . $blnum;

if ($$blkrunB != null and ctype_digit($$blkrunB)==TRUE) {
	//echo "$blkrunB<br>";
	$blkchseB = $blnum . "B" . "_" . $$blkrunB;
	// $blkchseB = $blnum . $blbeat . "_" . $bpref;
if ($blkchseN==null) { $blkchseN = $blkchseB;
} else {
	$blkchseN = $blkchseN . "," . $blkchseB; }
}

	// if ($beatamnt == "3") {

	$blkrunC = "blkC" . $blnum;

if ($$blkrunC != null and ctype_digit($$blkrunC)==TRUE) {
	//echo "$blkrunB<br>";
	$blkchseC = $blnum . "C" . "_" . $$blkrunC;
	// $blkchseB = $blnum . $blbeat . "_" . $bpref;
if ($blkchseN==null) { $blkchseN = $blkchseC;
} else {
	$blkchseN = $blkchseN . "," . $blkchseC; }
} // }

	// $blkA_Blnum = 0;

	++$bclp;
}

?>

Open in new window


The html form layout matches the paper form layout for those who wish to do it the paper way hence this format.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1500 total points
ID: 40196561
I'll take a deeper look at this today, but first - this jumped out at me in the PHP script.

$blnum=mysql_result($resultB,$bclp,"BlockNumber");

All of the PHP functions are documented in the online man pages, required reading if you're going to use PHP.  Please see the documentation here, where you will find the large red warning label:
http://php.net/manual/en/function.mysql-result.php

The script relies on the obsolete MySQL extension and will need to be refactored in order to keep it running in the future.  You can learn why PHP is taking this action and what you must do to keep your scripts running in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40196605
Hi Ray - yes, I am slowly moving to MySQLi as I go through everything as there are several sites to re-do.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40196610
Understood.  MySQLi conversion is a big task, but at least you can do it query-by-query.  Just make two database connections, then the remediation is much easier and does not have to be done all at once.

Can you please show us the rest of the PHP action script (action="brqprty.php")?  Also, why is the submit button disabled in the HTML?
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40208387
I've cut down the code, but basically the Submit button is disabled (I should have edited that) until another option is selected.

I'll hopefully get that code on here soon once I'm logged into the system.
Edit: The PHP code in the last section of my first reply was for that file, I assume you need more outside of that?
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40223737
Update: My hosting provider has updated to PHP 5.4 & enforced extra security.
Looks like a bit of recoding is required now. ..
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40223810
Security was always required!  It's just more regularly applied now.
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40223907
I agree, I got a shock as over a few days more & more went assumedly 'wrong' with the scripts as more securities were applied.
Most were informational errors - i.e. PHP PEAR Mail/mime having a fit with:
Strict Standards: Non-static method PEAR::isError() should not be called statically, assuming $this from incompatible context

Open in new window


So I've turned off the error reporting to the site. The hosts suggestion was to use php mail() but it doesn't support smtp authentication...


After brqprty.php it runs the following to re-order the output into selection order.
function mysort($a, $b, $dlm = '_')
{
	$a = end(explode($dlm, $a));
	$b = end(explode($dlm, $b));
	if ($a == $b) return 0;
	return ($a < $b) ? -1 : 1;
}

...
$arr = explode(',', $blkchseN); //This is the record in MySQL
// SORT
usort($arr, 'mysort');
// RECONSTRUCT THE STRING
$blkchseNPrint = implode(',', $arr);

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40224206
turned off the error reporting to the site
You might want to leave error logging turned on.  Also, that end(explode()) construct will trigger some kind of message.  I've had to remediate about a hundred of those compound statements!
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40233223
Thanks, I'll slowly get through it all as the site is now live but I'm debugging and updating as I go.
In regards to the function, can you suggest what needs changed to avoid the "Strict Standards: Only variables should be passed by reference in..."?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40234138
suggest what needs changed to avoid...
Please see the earlier answer, above.  Please also see AntiPractice #9.  This is something programmers should never be doing.  Now PHP is calling us out for it!  I can't tell you how to fix anything related to Pear - you may want to upgrade that to the latest version.  I think it's a matter of going through the code on a message-by-message basis and remediating the standards violations one at a time.
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40238691
Thanks.

It's now:
function mysort($a, $b, $dlm = '_')
{
	$exp1 = explode($dlm, $a);
	$a = end($exp1);
	
	$exp2 = explode($dlm, $b);
	$b = end($exp2);
	if ($a == $b) return 0;
	return ($a < $b) ? -1 : 1;
}

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1500 total points
ID: 40238938
That looks right.  Here is a similar situation where I used a compound statement and had to fix it.  I had always treated this one-liner as a black-box to isolate and normalize the file extension.

// ORIGINAL
$f_ext = strtoupper(trim(end(explode('.', $fname))));

// REMEDIATED
$f_ext = explode('.', $fname);
$f_ext = end($f_ext);
$f_ext = strtoupper(trim($f_ext));
if (!in_array($f_ext, $file_exts)) trigger_error( ... 

Open in new window

0
 
LVL 6

Author Comment

by:kiwistag
ID: 40269844
Apologies for delays.
I've put this in as a temporary workaround.
$_tmpB = explode(",",$blkchseNPrint);

$RptBlkA	=	$_tmpB[0];
$_RptBlkA1	=	explode("_",$RptBlkA);
$RptBlkB	=	$_tmpB[1];
$_RptBlkB1	=	explode("_",$RptBlkB);

Open in new window

So basically on the ordered structure it checks the first 2 records for choice 1. Blunt and ugly but it works.
Still doesn't stop someone going 2,2,2 etc.
0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1500 total points
ID: 40269855
... hosting provider has updated to PHP 5.4 ...
And not a minute too soon!  See the end-of-life notice for PHP 5.3 here:
http://php.net/
0
 
LVL 6

Author Comment

by:kiwistag
ID: 40308891
Thanks Ray.
I'm starting to get through most of the scripts now.
In regards to a function/method to check the repetition overall - any ideas? My temporary workaround has knocked most of the issues on the head so far but there is the odd double-up still happening.
0
 
LVL 6

Author Closing Comment

by:kiwistag
ID: 40383921
Minor workaround made, but as Ray Paseur pointed out, changes in the MySQLi functions need to be introduced/replaced.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40384127
What was wrong with the answers?  Please see the grading guidelines and explain the marked-down grade, thanks.
http://support.experts-exchange.com/customer/portal/articles/481419
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to count occurrences of each item in an array.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Suggested Courses
Course of the Month18 days, 7 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question