[RESOLVED] checking user input information against database records

[ejwf]

I have a user input form, which adds new records into the database, However I am getting a lot of errors (users not watching what they are typing) which is skewing my reports. I want to add some safety checks into my code so that I can check the validity of the user input before is goes into the database.

For example, last months figure for 'london' was 1002, I am expecting that this months figure is going to be higher, so I want to say, last month = A, this month = X - query = is X greater than A ? if so add the new record, if not throw up an error message.......

On the user input form I have added 'last months data' as a visual prompt for the user and also included the data as a hidden filed to carry across to the process page - but when I query it in the process form page, I cannot get it to work.

One of the problems I think might be due to the fact that on the user input form I have numerous lines to record different information for different locations so the form looks something like;

location code previous month this month

london text box 1002 text box
xxxxxx text box 5114 text box
xxxxxx text box 1222 text box
xxxxxx text box 4562 text box

so I am doing a foreach query so that I look line by line at each location to verify the records.........

I would be very grateful if someone could take a look and see where I am going wrong - and hopefully put me back on track.

Code for User Input Form

require_once ('../mysql_connect.php');		//connect to the database	

// 2 variables taken from previous page
$CoName = $_POST['client'];
$SName = $_POST['S'];

//query goes here to get this variable
  $CRef = $row111["CId"];  

//query goes here to get this variable
  $ConRef = $row11["ConRef"];  

//query goes here to get this variable
  $SRef = $row["SId"];  

//query goes here to get this variable
$List = $row1["CList"]; 

//list locations
$query = "SELECT (SNo) AS SNo, (CName) AS Location, (MId) AS MId, 
(CList) AS CList FROM `tblM` WHERE SRef = '$SRef' AND ConRef = '$ConRef'"; 
$result2 = mysql_query($query) 
or die(mysql_error());

if ($result2)
{
echo' <form action="process.php" method="POST" > <FONT FACE="arial" SIZE="1">
<table align="centre" cellspacing="0" cellpadding="0"> 
<tr valign="top">
<td width="2"></td>
<td width="2"></td>
<td align="right" width="50"><b>Location</b></td>
<td align="right" width="30"><b>Code</b></td>
<td width=\"2\" align=\"left\"></td> 
<td align="right" width="170">BW - <br>Previous Month </td>
<td align="left" width="200"><b>BW -<br> This Month</b></td> 
</tr >';

while ($row2 = mysql_fetch_array($result2,MYSQL_NUM)) 
{ 
$query= "SELECT (MRef) AS MRef, Max(data) As LastResult
FROM tblResults 
WHERE MRef = '".$row2[2]."' 
AND CRef = $CRef
AND SRef = $SRef
AND ConRef = $ConRef
GROUP BY MRef, CRef, SRef, ConRef";
$result3 = mysql_query($query) or die (mysql_error());
$row3 = mysql_fetch_array($result3);

echo "<td width=\"2\" align=\"left\"><input type=\"hidden\" name=\"CList\" value=\"{$row2[3]}\"></td>
<td width=\"2\" align=\"left\"><input type=\"hidden\" name=\"SRef\" value=\"$SRef\"></td>"; 

echo "
<td width=\"50\" align=\"left\"> {$row2[0]}</td> 
<td width=\"30\" align=\"left\">  <input type=\"text\" name=\"MId[]\" size=\"3\"></td>
<td width=\"170\" align=\"right\"> $row3[1]&nbsp;&nbsp </td>
<td width=\"2\" align=\"left\"><input type=\"hidden\" name=\"PreviousMonth\" value=\"{$row3[1]}\"></td> 
<td width=\"200\" align=\"left\">  <input type=\"text\" name=\"data[]\"></td>
";
}
echo "</font></tr>\n"; 
}  // close while loop 

} //close main if

echo '</font></table>';
echo "<br><p align=\"center\"><input type=\"submit\" name=\"submit\" value=\"Submit\"> "; 
echo '</form>';

Code For processing the form

require_once ('../mysql_connect.php');		//connect to the database

$SRef = $_POST['SRef'];

$query = "SELECT ConRef, CRef FROM tblS WHERE SId = '$SRef'";
$result101 = mysql_query($query) or die (mysql_error());
$row101 = mysql_fetch_array($result101);
$CoRef = $row101["ConRef"];
$ClRef = $row101["CRef"];

// define all the machines for this section for this input
$query = "SELECT MId FROM tblM, tblUser 
WHERE tblM.CRef = '$ClRef'
AND tblM.SRef = '$SRef' 
AND tblUser.CRef = tblM.CRef";
$result1001 = mysql_query($query) or die (mysql_error());
while ($row1001 = mysql_fetch_array($result1001))
$M[] = $row1001["MId"];

// check this months data is greater than previous
if (is_array($_POST['MId']) && count($_POST['MId']) >0)
{
foreach (($_POST['MId']) as $value)
  	{  

if (($_POST['data']) < ($_POST['PreviousMonth']))
       {
			echo "<br><br>" . 
             "<font face=\"Arial, Helvetica, sans-serif\">" . 
             "Sorry, it appears that this months data </b> " . 
             "is lower than that entered last month.<br>Please kindly return to the " . 
             "input form, and re-enter this data.  " . 
             "<br>Thank you" . 
             "</font>"; 
	   }


   } //close foreach

   } //close if

   // insert the record 
    else     

    { // code to insert the rcords goes here
    }

[sneakyimp]

it would help to know specifically what the problem is....something more than 'i cannot get it to work'

[ejwf]

this is the whole problem - nothing happens, no error message, nothing.

I have tested the code by running the web page as a user would, but thrying to force an error by inputting the data incorrectly.
I would expect to receive the error message, but I get no error message.

I am testing this bit of code(which is taken from the process form page)

  

    if (($_POST['data']) < ($_POST['PreviousMonth'])) 
           { 
                echo "<br><br>" .  

                 "<font face=\"Arial, Helvetica, sans-serif\">" .  

                 "Sorry, it appears that this months data </b> " .  

                 "is lower than that entered last month.<br>Please kindly return to the " .  

                 "input form, and re-enter this data.  " .  

                 "<br>Thank you" .  

                 "</font>";  

           } 

I know that $POST['data'] is definitely less than $POST['PreviousMonth'] because I entered it that way, but I don't get the error message......

If you could help I would be very grateful (boss is pressuring me to find a solution and I am banging my head against a wall, I just can't see it). Thanks

[sneakyimp]

well one problem i see is that $POST['data'] is an array. You do a foreach on $POST['MId'] which will loop through all the MId values but within that foreach you make no reference to $value which is the value coming from MId. Basically when you do this:

if (($POST['data']) < ($POST['PreviousMonth']))

then you are comparing an array (data) to a simple value (PreviousMonth) which won't get you what you expect.

Additionally, your code that generates your form is probably going to have quite a few different hidden inputs for PreviousMonth. This may or may not cause any problems.

Also, i think you have a misplaced }, a missing <TR> tag, and an extra </font> tag.

} <--- WHAT IS THIS FOR?
echo "</font></tr>\n";  

}  // close while loop 

It looks like what you are trying to do is fetch records from your database and loop through the returned records ($row3 holds the values for each record). Does each $row3 have a different value for PreviousMonth? or is there one value for PreviousMonth that applies over and over again? I'm guessing the former is what you really want to do.

If each record has a different value for PreviousMonth that needs to be compared to the data value for each record that the user is submitting then you need to do this:

<?
$record_number = 0;
while ($row2 = mysql_fetch_array($result2,MYSQL_NUM))  

{  

  $record_number++;
  $query= "SELECT (MRef) AS MRef, Max(data) As LastResult 
    FROM tblResults 
    WHERE MRef = '".$row2[2]."' 
    AND CRef = $CRef 
    AND SRef = $SRef 
    AND ConRef = $ConRef 
    GROUP BY MRef, CRef, SRef, ConRef"; 
  $result3 = mysql_query($query) or die (mysql_error()); 
  $row3 = mysql_fetch_array($result3); 

?>
<tr>
<td><?=$record_number ?></td>
<td width="2" align="left"><input type="hidden" name="CList[]" value="<?=$row2[3] ?>"></td> 
<td width="2" align="left"><input type="hidden" name="SRef[]" value="<?=$SRef ?>"></td>

<td width="50" align="left"> <?=$row2[0] ?></td>
<td width="30" align="left">  <input type="text" name="MId[]" size="3"></td> 
<td width="170" align="right"> <?=$row3[1] ?>&nbsp;&nbsp </td> 
<td width="2" align="left"><input type="hidden" name="PreviousMonth[]" value="<?=$row3[1] ?>"></td> 
<td width="200" align="left">  <input type="text" name="data[]"></td>

</tr>
<?
}  // close while loop 

?>

then in your file process.php, you will find that you have several arrays defined in $_POST all of which are the same size and you can do this:

<?
$record_count = sizeof($_POST['MId']);
for($i=0; $i<record_count; $i++) {
  if ($_POST['data'][$i] < $_POST['PreviousMonth'][$i]) {
    echo "This month's data in row" . ($record_count+1) . " is less than last months<br>";
  } // close if
} // end for loop
?>

[ejwf]

thank you for this
I have now got all the arrays straight, but it still isn't doing the comparison of this month against last month.

I know the arrays are working as I can echo them out OK and they balance - are the same size, and the data is in the correct order to do a comparison, but the comparison check isn't happening

I have got this as your suggestion for the code to carry out the comparison....

$record_count = sizeof($_POST['MId']);
for($i=0; $i<record_count; $i++) 
{
  if ($_POST['data'][$i] < $_POST['PreviousMonth'][$i])
 {
    echo "This month's data in row" . ($record_count+1) . " is less than last months<br>";
  } // close if
} // end for loop

I have also checked $record_count - this is Ok, it is echoing out the correct number value for the number of rows, so I am sure that thanks to your suggestions we now have the correct information in the correct format - I just cannot find the correct code to perform the comparison of the 2 values (this months against last months).......
Any other thoughts? TIA

[sneakyimp]

so you are echoing this month and last month...but the comparison isn't working?? any more details? have you tried echoing the values to be compared on your process.php page?

[ejwf]

Yes - I have used print_r to echo out the user input information from $data[] and the hidden field $PreviousMonth[] in process.php.

So I know the information is being carried over to this file - it is just the actual comparison that isn't happening........

[sneakyimp]

what do you mean isn't happening? perhaps use intval() on the values before comparing?

[ejwf]

Hello,
the code for comparing the 2 values ($data and $PreviousMonth) and if one is lower than the other, give an error message still isn't working - when I force and error by inputting information I know is wrong ($data lower than $PreviousMonth) when I run the code the error message doesn;t come up.

I have tried intval(), but I am not familiar with this so perhaps I am using it incorrectly.......
I have

$record_count = sizeof($_POST['MId']); 
for($i=0; $i<record_count; $i++)  

{ 
   $now = intval($_POST['data']);
   $previous = intval($_POST['PreviousMonth']);

  if ($now [$i] < $previous [$i]) 
 { 
    echo "This month's data in row" . ($record_count+1) . " is less than last months<br>"; 
  } // close if 
} // end for loop 

again, with this code, when I run the code I don't get the error message, even though I have forced the error by entering 'incorrect' data.

I have also tried adding
echo $now; echo $previous;
into this code, but it doesn't output anything - so I know these variables aren't being set, so I know the comparision cannot happen (no data/integers to compare). But like I say I am not familiar with intval() and looking in the php manual I cannot see any examples to follow, so I might be on completely the wrong track........ any advice would be gratefully received.

[sneakyimp]

ok.

you don't seem to fully understand arrays. when you do inputs like this

<input type="text" name="foo[]">
<input type="text" name="foo[]">
<input type="text" name="foo[]">
<input type="text" name="foo[]">
<input type="text" name="foo[]">
<input type="text" name="foo[]">
<input type="text" name="foo[]">

and you POST it to a form like your process.php

then it's going to define an ARRAY. which is not a single value but a whole bunch of values in a collection. complicating matters iS the fact that $POST is also an array. In this particular case, not only is $POST going to be an array, but $_POST['foo'] is also going to be an array. These are called nested arrays.

so what you have is going to be 2-dimensional array. think of rows and columns. coordinates.

now your code:

 $record_count = sizeof($_POST['MId']);  

for($i=0; $i<record_count; $i++)   

{  

   $now = intval($_POST['data']); 
   $previous = intval($_POST['PreviousMonth']); 

  if ($now [$i] < $previous [$i])  

 {  

    echo "This month's data in row" . ($record_count+1) . " is less than last months<br>";  

  } // close if 
} // end for loop 

looks to be to be a bit inefficient because your are assigning an entire array to $now each time you do the loop.

try something like this...and if the comparison doesn't work, then you need to reach out, take a deep breath, and start writing tons of echo statements to determine where the confusion is happening and see which values are not getting set properly. sadly, i just don't have enough time to do it for you. if you keep trying, the progammer's awakening will eventually happen.

// all three of these record counts should be the same
// if they aren't, then you need to check your html form and make sure it
// the names are what you think they are.

$record_count = sizeof($_POST['MId']);  

echo "there are $record_count records<br>";

$data_array = $_POST['data'];
$data_size = sizeof($data_array);
echo "data_array has $data_size records<br>";

$previous_month_array = $_POST['PreviousMonth'];
$prev_size = sizeof($previous_month_array);
echo "previous_month_array has $prev_size records<br>";

for($i=0; $i<record_count; $i++)   

{  

  $now = intval($data_array[$i]);
  echo "&nbsp;now:$now<br>";
  $previous = intval($previous_month_array[$i]); 
  echo "&nbsp;previous:$previous<br>";

  if ($now < $previous)  

 {  

    // i think i screwed this line up last time...$i is the current row number.
    echo "This month's data in row" . ($i+1) . " is less than last months- BAD!<br>";  

  } else {
    echo "AOK<BR>";
  } // close if 
} // end for loop 

If this doesn't work for you, please don't just say "it doesn' work, please loog again" because i won't. bring back a specific error message or let me know what the output of this EXACT code is. if you don't i won't be able to help you any more.

[ejwf]

thank you for all your help in this matter sneakyimp...
I have got it working thanks to your help - I now have error messages appearing whenever the user does something wrong !

you are not going to believe what was happening to stop this from running in the code........ I didn't have the $ from the variable $record_count for the line
for($i=0; $i<record_count; $i++)
that was all that was stopping the code from working as I wanted it.........

having said all that I think I am now clearer on arrays and how they work - I did not know about the $_POST part also being an array and nested arrays - so, as well as making my boss very happy you have taught me something valuable - so big thanks 🙂 .

[sneakyimp]

glad it worked out. i missed that record_count bug too.