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

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?
LVL 6
kiwistagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
Ray PaseurCommented:
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
kiwistagAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kiwistagAuthor Commented:
Hi Ray - yes, I am slowly moving to MySQLi as I go through everything as there are several sites to re-do.
0
Ray PaseurCommented:
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
kiwistagAuthor Commented:
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
kiwistagAuthor Commented:
Update: My hosting provider has updated to PHP 5.4 & enforced extra security.
Looks like a bit of recoding is required now. ..
0
Ray PaseurCommented:
Security was always required!  It's just more regularly applied now.
0
kiwistagAuthor Commented:
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
Ray PaseurCommented:
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
kiwistagAuthor Commented:
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
Ray PaseurCommented:
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
kiwistagAuthor Commented:
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
Ray PaseurCommented:
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
kiwistagAuthor Commented:
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
Ray PaseurCommented:
... 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
kiwistagAuthor Commented:
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
kiwistagAuthor Commented:
Minor workaround made, but as Ray Paseur pointed out, changes in the MySQLi functions need to be introduced/replaced.
0
Ray PaseurCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.