# 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
``````
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
###### 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.

Developer & 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.

1A_46,1B_47,1C_48,2A_28,2B_29,2C_30,
0
Commented:
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
Author 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;
``````

HTML:
``````<html>

<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>

<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>
``````
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;
}

?>
``````

The html form layout matches the paper form layout for those who wish to do it the paper way hence this format.
0
Commented:
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

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

Author Commented:
Hi Ray - yes, I am slowly moving to MySQLi as I go through everything as there are several sites to re-do.
0
Commented:
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
Author 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
Author Commented:
Update: My hosting provider has updated to PHP 5.4 & enforced extra security.
Looks like a bit of recoding is required now. ..
0
Commented:
Security was always required!  It's just more regularly applied now.
0
Author 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
``````

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);
``````
0
Commented:
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
Author 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
Commented:
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
Author 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;
}
``````
0
Commented:
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( ...
``````
0
Author 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);
``````
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
Commented:
... 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
Author 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
Author Commented:
Minor workaround made, but as Ray Paseur pointed out, changes in the MySQLi functions need to be introduced/replaced.
0
Commented:
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.