Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form validation with MYSQL

FORMS

Form validation with MYSQL

by  hos2  Posted    (Edited  )
Since I have to do some form checks on MYSQL to see if a record already exists I tried to work it out to do all form checks with mysql. I have never seen it before so I don't know if I come up with something good or that there is perhaps a good reason not to do it this way but here is how it goes

STEP 1 First of all the basic form with username and email

Code: 
include "validation.php3";

?>
<HTML>
<BODY class=visit>
<TABLE width=100% border=0>
<form action="arform.php3?arid=<? print "$arid"; ?> " target="_self" method=post>  

<tr><td>Username</td><td><INPUT TYPE="text" name=arname value="<? print "$arname"; ?>" size=20></td><td><? print $error['arname']; ?></td></tr>
<tr><td>E-mail</td><td><INPUT TYPE="text" name=aremail value="<? print "$aremail"; ?>" size=20></td><td><? print $error['aremail'] ; ?></td></tr>
<tr><td><input type="hidden" name="process" value="1"></td></tr>
<tr><td><input type="hidden" name="formname" value="artistform"></td></tr>

<tr><td colspan=3><input type="Submit" value="Submit" name="Submit"></td></tr>
</td></tr>
</TABLE>
</BODY>
</HTML>

where value="<? print "$arname"; ?>" is converted from the posted info to a variable with the same name. since this form can also be used to update records but that's beyond the scope of this FAQ

<? print $error['arname']; ?> contains the errormessages about what is wrong with the input.

STEP2: create a table in mysql for the validation rules

here the example of the table I used in this example
Code: 
CREATE TABLE `validationrulestable` (
  `vrid` int(11) NOT NULL auto_increment,
  `vrname` varchar(20) NOT NULL default '',
  `vrdescr` tinytext NOT NULL,
  `vrquery` text,
  PRIMARY KEY  (`vrid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `validationrulestable` VALUES (1, 'notempty', 'Field may not be empty',  'SELECT length(''$fieldvalue1'') = 0');
INSERT INTO `validationrulestable` VALUES (2, 'validemail', 'Email adress is not correct',  'SELECT ''$fieldvalue1'' not like ''%@%.%''');
INSERT INTO `validationrulestable` VALUES (3, 'useralreadyexist', 'username already exists', 'SELECT count(arname) from artisttable where arname like ''$fieldvalue1''');

explanation:
the query SELECT length(''$fieldvalue1'') = 0 returns 1 if true so when $fieldvalue1 is empty then 1 is returned if not empty then 0 is returned

the rules are the most common
- field not empty
- valid email (sql check can ofcourse changed to be more specific)
- record already exists

STEP3 3 validation.php

Code: 
if ($_POST['process'] == 1) { // only process validation.php after it is submitted , process is a hidden field in the form

include $rootpath."cgi-bin/connopen.php3"; // connect to mysql

global $totalcheck; // variable which holds the total amount of errors

$query="SELECT * from validationrulestable"; // select all rules from the database
$rs=mysql_query($query,$conn);
$i=0;

$list = mysql_num_rows($rs); 
while($i <= $list)	
{
    $row = mysql_fetch_array($rs); 

// put every rule in an array with the short name provided like 'notempty' 
    $vrf[$row["vrname"]]['message']=$row["vrdescr"];
    $vrf[$row["vrname"]]['query']=$row["vrquery"];
    $i++;
		
}

// loop through all the post vars and convert them to variables with the same name and also in an array for further processing after totalcheck=0
while ( list($field, $value) = each ($HTTP_POST_VARS)){ 
		$fieldvalues[$field]=$value;
		$$field=$value;
		global $$field; // to be able to print the values back in the form when there is something wrong
}

// function for checking the posted vars with the query

function controlesql($query,$fieldvalue1,$fieldname,$warning,$conn){
$query=str_replace("\$fieldvalue1",$fieldvalue1,$query);
$list2=mysql_result(mysql_Query($query,$conn),0);
// if list2 =1 then it's not good if 0 it's good

$error=array() ;
global $error;

// return the warning if list2 <> 0 or add the warning to previous checks that failed
if ($list2 == 0) {return 0;} else {$error[$fieldname] = $error[$fieldname] . "<br>". $warning; return 1;}

}


// FORM VERIFICATION
check the form with the checks you want to perform on the corresponding fields
// the formname is also a hiddenfield in the form. so you can easily process other forms in the validation.php 

if ($formname == 'artistform') {
	$totalcheck=0;
	$totalcheck= $totalcheck + controlesql($vrf['notempty']['query'],$arname,"arname",$vrf['notempty']['message'],$conn);
	$totalcheck= $totalcheck + controlesql($vrf['notempty']['query'],$aremail,"aremail",$vrf['notempty']['message'],$conn);
	$totalcheck= $totalcheck + controlesql($vrf['useralreadyexist']['query'],$arname,"arname",$vrf['useralreadyexist']['message'],$conn);
	$totalcheck= $totalcheck + controlesql($vrf['validemail']['query'],$aremail,"aremail",$vrf[validemail][message],$conn);


}
}// end if process=1

if ($totalcheck=0) { process information to mysql}

hope someone finds this code usefull to their benefits, since all the rules are in mysql it's also very easy to expand it to multilanguage errormessages if necessary
I think that it won't be the fastest way to do form validation but at least everything is done now from a central file and table and can be managed more easily instead of functions build into the actual form

an example of how it works is on
http://thehospages.com/testvalidatie/index.php3
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top