Solved

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

Posted on 2014-07-14
291 Views
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?
0
Question by:kiwistag

LVL 52

Expert Comment

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 107

Expert Comment

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

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

LVL 107

Accepted Solution

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

Hi Ray - yes, I am slowly moving to MySQLi as I go through everything as there are several sites to re-do.
0

LVL 107

Expert Comment

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

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

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

LVL 107

Expert Comment

Security was always required!  It's just more regularly applied now.
0

LVL 6

Author Comment

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

LVL 107

Expert Comment

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

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 107

Expert Comment

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

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

LVL 107

Assisted Solution

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

LVL 6

Author Comment

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

LVL 107

Assisted Solution

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

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

Minor workaround made, but as Ray Paseur pointed out, changes in the MySQLi functions need to be introduced/replaced.
0

LVL 107

Expert Comment

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsersâ€¦
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, dâ€¦

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!