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:
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?
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_39In 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?
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.
ASKER
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:
HTML:
So basically:
The html form layout matches the paper form layout for those who wish to do it the paper way hence this format.
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>
<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">
</p>
<form method="POST" action="brqprty.php" name="chseblk">
<p> <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>
<input type="checkbox" name="prefblok" value="1" disabled>If Yes,
preferred blocks listed above<br>
<input type="checkbox" name="colph" value="1"> By collect phone
call at short notice</p>
<p> </p>
<p><input type="submit" value="Submit" name="submitbutton" disabled="disabled"> <input type="reset" value="Reset" name="B2"><br/>
</p>
</form>
</div>
</body>
</html>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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Ray - yes, I am slowly moving to MySQLi as I go through everything as there are several sites to re-do.
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?
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?
ASKER
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?
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?
ASKER
Update: My hosting provider has updated to PHP 5.4 & enforced extra security.
Looks like a bit of recoding is required now. ..
Looks like a bit of recoding is required now. ..
Security was always required! It's just more regularly applied now.
ASKER
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:
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.
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 contextSo 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);turned off the error reporting to the siteYou 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!
ASKER
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..."?
In regards to the function, can you suggest what needs changed to avoid the "Strict Standards: Only variables should be passed by reference in..."?
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.
ASKER
Thanks.
It's now:
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;
}SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Apologies for delays.
I've put this in as a temporary workaround.
Still doesn't stop someone going 2,2,2 etc.
I've put this in as a temporary workaround.
$_tmpB = explode(",",$blkchseNPrint);
$RptBlkA = $_tmpB[0];
$_RptBlkA1 = explode("_",$RptBlkA);
$RptBlkB = $_tmpB[1];
$_RptBlkB1 = explode("_",$RptBlkB);Still doesn't stop someone going 2,2,2 etc.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Minor workaround made, but as Ray Paseur pointed out, changes in the MySQLi functions need to be introduced/replaced.
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
http://support.experts-exchange.com/customer/portal/articles/481419
Is this bad or good?
1A_46,1B_47,1C_48,2A_28,2B