×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

FORMS

Form validation with MYSQL by hos2
Posted: 24 Sep 05

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

Back to PHP FAQ Index
Back to PHP Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close