working with database using data model and master model
Friday, April 18th, 2008
1) db-master-data.php
<?
class master{
var $name=”";
function Add($datamodelname,$data_array,$userlevel,$explicit_table_name=”") {
//this datamodelname is without suffix “_data”
$dbserverobj=new dbserver();
$insert_query=$dbserverobj->model_insertquery($datamodelname,$data_array);
$result=$dbserverobj->insert_query($datamodelname,$insert_query,$explicit_table_name);
return $result;
}
function Edit($datamodelname,$data_array,$userlevel,$where_string,$explicit_table_name=”") {
//this datamodelname is without suffix “_data”
$dbserverobj=new dbserver();
$update_query=$dbserverobj->model_updatequery($datamodelname,$data_array);
$dbserverobj->update_query($datamodelname,$update_query,$where_string,$explicit_table_name);
return 1;
}
}
class data{
var $fields;
var $datatype;
function get_fields()
{
return $this->fields;
}
function get_datatype(){
return $this->datatype;
}
function get_fieldtype($key){
if (array_key_exists($key,$this->datatype)){
return $this->datatype[$key];
}
}
}
class dbserver{
var $host;
var $username;
var $password;
var $db;
var $tablename;
var $resultset;
var $dbvar_array =array(’host’=>”",’username’=>”",’password’=>”",’db’=>”",’tablename’=>”",’resultset’=>”");
function set($key,$value){
if (array_key_exists($key,$this->dbvar_array)){
$this->dbvar_array[$key]=$value;
}
}
function get($key){
if (array_key_exists($key,$this->dbvar_array)){
return $this->dbvar_array[$key];
}
}
function datamodel_fields($datamodelname)
{
$datamodel=$datamodelname.”_data”;
$datamodelobj=new $datamodel();
return $datamodelobj->get_fields();
}
function model_insertquery($datamodel,$data_array)
{
$datamodel=$datamodel.”_data”;
$modelobj=new $datamodel();
if (is_array($data_array)){
$Field=”;
$FieldValue=”;
$i=-1;
foreach($data_array as $Key=>$Value){
$i++;
if(!get_magic_quotes_gpc()){
if($i>0){
$Value=addslashes($Value); //no need to add slash for id field which is pk as it is ” while adding to avoid \’\’ to go into id field which is int and pk
}
}
$Field.= $Key.”,”;
if(array_key_exists($Key,$modelobj->get_datatype())){
$Type=$modelobj->get_fieldtype($Key);
switch ($Type) {
case “int”:
case “float”:
$FieldValue.=$Value.”,”;
break;
case “string”:
case “date”:
$FieldValue.=”‘”.$Value.”‘,”;
break;
}
}
}
}
return “(”.substr($Field,0,-1).”)values(”.substr($FieldValue,0,-1).”)”;
}
function model_updatequery($datamodel,$data_array){
// print_r($datamodel);
// print_r($data_array);
$datamodel=$datamodel.”_data”;
$modelobj=new $datamodel();
//print_r($modelobj);
if (is_array($data_array)){
$Field=”;
$FieldValue=”;
foreach($data_array as $Key=>$Value){
if(!get_magic_quotes_gpc()){
$Value=addslashes($Value);
}
$Field.= $Key.”,”;
if(array_key_exists($Key,$modelobj->get_datatype())){
$Type=$modelobj->get_fieldtype($Key);
switch ($Type) {
case “int”:
case “float”:
$FieldValue=$Value.”,”;
break;
case “string”:
case “date”:
$FieldValue=”‘”.$Value.”‘,”;
break;
}
}
if(isset($Value) && !empty($Value)){
$StrUpdate.= $Key .”=”. $FieldValue;
}
}
//echo “<br>”.substr($StrUpdate,0,-1);
return substr($StrUpdate,0,-1);
}
}
function connectdb($host,$username,$password,$db){
$this->set(”host”,$host);
$this->set(”username”,$username);
$this->set(”password”,$password);
$this->set(”db”,$db);
//print_r($this->dbvar_array);
$link = mysql_connect($this->get(”host”), $this->get(”username”), $this->get(”password”)) or die( mysql_error());
if (!$link) {
die(’Not connected : ‘ . mysql_error());
}
$db_selected = mysql_select_db($this->get(”db”), $link);
if (!$db_selected) {
die (’Can\’t use foo : ‘ . mysql_error());
}
return $link;
}
function query ($query){
//echo $query;
//exit;
$result= mysql_query($query) or die(mysql_error());
$rowsaffected=mysql_affected_rows();
$queryresult[’count’]=0;
if($result==1){ //insert case
$queryresult[’count’]=$result;
}
else{
if(mysql_num_rows($result)) //select case
{
$count=mysql_num_rows($result);
if($count)
{
// echo “count is “. $count;
$queryresult[’count’]=$count;
}
}
else{
if(strtolower(substr(trim($query),0,6))==”select”)
{
$queryresult[’count’]=0;//check dupluicte username exists select qurey didnt bring any result
}
else{
$queryresult[’count’]=1; //update didt caused any change to any field
}
$queryresult[’resultset’]=1;//update didt caused any change to any field
}
}
$queryresult[’resultset’]=$result;
return $queryresult;
}
function insert_query($datamodelname,$insert_query_part,$explicit_table_name=”"){
if(isset($explicit_table_name) & !empty($explicit_table_name))
$table=$explicit_table_name;
else
$table=$datamodelname;
$query=”insert into ” . $table. $insert_query_part;//echo $query;exit;
return $this->query($query);
//exit;
/* below comments already taking care in access-validation class so no required to take this care here*/
//is user given acess to do this operation? is user allowed to insert every fields ?
//fields_toinsert is a comma separated value , explode it ,
//then turn byturn for each exploded element check whether user has been given access to insert this element
//access checking can be made from has_field_access(column,user)
}
function update_query($datamodelname,$update_query_part ,$where_string,$explicit_table_name=”"){
if(isset($explicit_table_name) & !empty($explicit_table_name))
$table=$explicit_table_name;
else
$table=$datamodelname;
$query= “update ” . $table. ” set “ . $update_query_part . ” where ” . $where_string;
return $this->query($query);
/* below comments already taking care in access-validation class so no required to take this care here*/
//is user given acess to do this operation? is user allowed to insert every fields ?
//fields_toinsert is a comma separated value , explode it ,
//then turn byturn for each exploded element check whether user has been given access to insert this element
//access checking can be made from has_field_access(column,user)
}
function delete_query($datamodelname,$where_string,$explicit_table_name=”"){
if(isset($explicit_table_name) & !empty($explicit_table_name))
$table=$explicit_table_name;
else
$table=$datamodelname;
$query= “delete from “ . $table . ” where “. $where_string;
//echo $query;exit;
return $this->query($query);
}
function select_query($datamodelname,$fields,$where_string=”",$countfield=”"){
if(isset($where_string)&& !empty($where_string)){
$where_string= ” where ” .$where_string;
}
if(isset($countfield)&& !empty($countfield)){
$fields=”count($countfield)”;
}
$query= “select ” . $fields. ” from “ . $datamodelname . $where_string;
return $this->query($query);
}
function get_onerecord_object($datamodelname)//generally datamodel name is equal tablename appended with “_data”
{
}
function get_single_record_array($result=”")
{
while($row1=mysql_fetch_assoc((isset($result[resultset]) && !empty ($result[resultset]))? $result[resultset] : $this->resultset))
{
return $row1;
}
}
}
?>
2) singnup.php
<?php
$commandaction=”add_member”;
require_once(”./include/config.php”);
require_once(”common.php”);
if ($_POST[’commandaction’]){
$checkEmail =”select username from teamboard_users where email =’$_POST[email]’”;
$check= $dbobj->query($checkEmail);
if($check[count]==0){
$result=operation_result($commandaction);
$insertId = mysql_insert_id();
$name =$_POST[firstname].” “.$_POST[lastname];
$sql = “INSERT into marty VALUES (null,’$name’,”,”,”,”, ”, ”,”, ”, 1, ”, now(),’1′,’$insertId’)”;
$return= $dbobj->query($sql);
save_onfailure($result,”signup.php”,”Add=0″);
save_onsuccess($result,”member_account.php”,”Add=1″);
}
else{
redirect(”signup.php”,”Exist=1″);
}
}
else{
$middle=”signup_view.php”;
}
?>
<html xmlns=”http://www.w3.org/1999/xhtml“>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″ />
<title>::Signup::</title>
<link href=”style.css” mce_href=”style.css” rel=”stylesheet” type=”text/css”></head>
<script type=”text/javascript”>
function checkPhone(phone){
if (phone.length == 0) {
window.alert(”You must provide a phone number.”);
return false;
}
phone = phone.replace(”-”,”");
phone = phone.replace(” “,”");
phone = phone.replace(”(”,”");
phone = phone.replace(”)”,”");
phone = phone.replace(”.”,”");
if (phone.length != 10) {
window.alert(”Phone numbers must only include a 3-digit area code and a 7-digit phone number.”);
return false;
}
for (i=0; i<phone.length; i++) {
if (phone.charAt(i) < “0″ || phone.charAt(i) > “9″) {
window.alert(”Phone numbers must only contain numbers.”);
return false;
}
}
return true;
}
function checkEmail(email) {
if (email.length == 0) {
window.alert(”You must provide an e-mail address.”);
return false;
}
if (email.indexOf(”/”) > -1) {
window.alert(”E-mail address has invalid character: /”);
return false;
}
if (email.indexOf(”:”) > -1) {
window.alert(”E-mail address has invalid character: :”);
return false;
}
if (email.indexOf(”,”) > -1) {
window.alert(”E-mail address has invalid character: ,”);
return false;
}
if (email.indexOf(”;”) > -1) {
window.alert(”E-mail address has invalid character: ;”);
return false;
}
if (email.indexOf(”@”) < 0) {
window.alert(”E-mail address is missing @”);
return false;
}
if (email.indexOf(”\.”) < 0) {
window.alert(”E-mail address is missing .”);
return false;
}
if (email.indexOf(”@\.”) > 0) {
window.alert(”E-mail address is invalid”);
return false;
}
return true;
}
function Validate()
{
/* if (document.frm_signup.company_name.value == ” )
{
alert(’You must enter the company name’);
return false;
}*/
if (document.frm_signup.firstname.value == ” )
{
alert(’You must enter the firstname’);
return false;
}
if (document.frm_signup.lastname.value == ” )
{
alert(’You must enter the lastname’);
return false;
}
/*if (document.frm_signup.email.value == ” )
{
alert(’You must enter the email’);
return false;
}*/
if (document.frm_signup.password.value == ” )
{
alert(’You must enter the password’);
return false;
}
//phone= document.frm_signup.telephone.value;
if(!checkPhone(document.frm_signup.telephone.value))
return false;
if(!checkEmail(document.frm_signup.email.value))
return false;
return true;
}
</script>
<body>
<table width=”100%” border=”0″>
<tr>
<td height=”100″> </td>
</tr>
<tr>
<td align=”center” valign=”middle”><table width=”45%” border=”0″ align=”center” cellpadding=”0″ cellspacing=”0″ bgcolor=”#FFFFFF”>
<tr>
<td height=”20″ align=”center” valign=”top”><img src=”images/logo.gif” mce_src=”images/logo.gif” width=”430″ height=”67″ align=”middle”></td>
</tr>
<tr>
<td height=”20″ valign=”top”> </td>
</tr>
<tr>
<td valign=”top”><img src=”images/spacer.gif” mce_src=”images/spacer.gif” width=”1″ height=”5″></td>
</tr>
<tr>
<td valign=”top”><table width=”100%” border=”0″ cellspacing=”0″ cellpadding=”0″>
<tr>
<td align=”center” class=”arial16″> Sign
up now! </td>
</tr>
<tr>
<td align=”center” valign=”middle” class=”content-text”> </td>
</tr>
<tr>
<td align=”center” valign=”middle” class=”content-text”><? include ($middle); ?></td>
</tr>
</table></td>
</tr>
<tr>
<td valign=”top”><img src=”images/spacer.gif” mce_src=”images/spacer.gif” width=”1″ height=”5″></td>
</tr>
</table></td>
</tr>
<tr>
<td> </td>
</tr>
</table>
<link rel=”stylesheet” type=”text/css” href=”teamboard.css” mce_href=”teamboard.css”>
</body>
</html>
3) signup_view.php
<?
if ($_Get[’Exist’]==1) { echo “User already exists”); }
?>
<form name=”frm_signup” action=”signup.php” method=”post” onSubmit=”return Validate()”>
<table width=”75%” align=”center” class=”addinvestor-text”>
<tr align=”right”>
<td colspan=”3″><?
if ($_Get[’Add’]==1) { echo “Record Successfully”); }
else if ($_Get[’Add’]==0) { echo “Opertion failed”); }
?>
</td>
</tr>
<tr>
<td><span id=”" class=”content-text” style=”color:OliveDrab;”></span> </td>
</tr>
<tr>
<td><div id=”" class=”content-text” style=”color:Red;display:none;”></div></td>
</tr>
<tr>
<td align=”center” valign=”middle”><table width=”100%” align=”center” cellspacing=”1″ class=”content-text”>
<tr>
<td width=”39%” class=”arial12″> First name*:</td>
<td colspan=2><input name=”firstname” type=”text” maxlength=”50″ id=”firstname” class=”content-text” style=”width:125px;” /></td>
</tr>
<tr>
<td class=”arial12″> Last name*:</td>
<td colspan=2><input name=”lastname” type=”text” maxlength=”50″ id=”lastname” class=”content-text” style=”width:125px;” /></td>
</tr>
<tr>
<td class=”arial12″> Email*:</td>
<td colspan=2><input name=”email” type=”text” maxlength=”50″ id=”" class=”content-text” style=”width:125px;” />
<span id=”" class=”content-text” style=”color:Red;visibility:hidden;”> </span> </td>
</tr>
<tr>
<td class=”arial12″> Password*:</td>
<td colspan=2><input name=”password” type=”password” maxlength=”50″ id=”" class=”content-text” style=”width:125px;” /><span id=”" style=”color:Red;visibility:hidden;”> </span></td>
</tr>
<tr>
<td class=”arial12″> Confirm password*:</td>
<td colspan=2><input name=”tbcpass” type=”password” maxlength=”50″ id=”" class=”content-text” style=”width:125px;” />
<span id=”" class=”content-text” style=”color:Red;visibility:hidden;”> </span></td>
</tr>
<tr>
<td class=”arial12″> Telephone #:</td>
<td colspan=2><input name=”telephone” type=”text” maxlength=”50″ id=”" class=”content-text” style=”width:125px;” /> </td>
</tr>
<tr>
<td align=center> </td>
<td align=left> </td>
<td align=center> </td>
</tr>
<tr>
<td align=center><input type=”hidden” name=”commandaction” value=’<? echo $commandaction ?>’ /></td>
<td width=”25%” align=left>
<input type=”submit” name=”Submit” value=”Sign up”> </td>
<td width=”36%” align=center> </td>
</tr>
<tr>
<td colspan=3 align=center></td>
</tr>
</table></td>
</tr>
</table>
</form>
4) module_operation.php
<?
function operation_result($commandaction,$request=”")
{
global $dbobj;
switch ($commandaction){
case “add_member”:
$userobj=new user_master();
$datamodel_array=prepare_datamodel_from_form_POST(”user”,$_POST); //user is logical data model where cal_users is physical data model ,pass the third parameter if name of primarykey is not “id”
$result=$userobj->Add(”user”,$datamodel_array,”admin”,”teamboard_users”);//fourth parameter is needed if we want to provide explicit_table_name
if($result)
{ $_SESSION[’logged’] = true;
$_SESSION[’level’] = $datamodel_array[user_level];
$_SESSION[’user_id’] = mysql_insert_id();
$_SESSION[’username’] = $_POST[’username’];
$_SESSION[’registered’] = $datamodel_array[’registered’];
$_SESSION[’last_logged’] = time();
$_SESSION[’firstname’] = $datamodel_array[’firstname’];
$_SESSION[’lastname’] = $datamodel_array[’lastname’];
$_SESSION[’email’] = $datamodel_array[’email’];
$_SESSION[’company_name’] = $datamodel_array[’company_name’];
}
break;
case “list_member”:
if ($request==”del”){
$where_string=” user_id = “.$_GET[del];
$result1=$dbobj->delete_query(”teamboard_users”,$where_string,”");
}
else{
$userobj=new user_master();
$result=$dbobj->select_query(”teamboard_users”,”user_id,company_name,firstname,lastname,email,telephone”,”",”"); //$where_string=”",$countfield=”"
}
break;
}
return $result;
}
?>
5) prepare_data.php
<?
function prepare_datamodel_from_form_POST($datamodel,$post=”",$record_key=”"){
global $dbobj;
if(isset($record_key) && !empty($record_key)){
$id=$record_key;
}
else
{
$id=”id”;
}
$datamodel_array=array();
switch ($datamodel){
case “user”:
$id=”user_id”;
$datamodel_array=$dbobj->datamodel_fields(”user”); //datamodelname with suffix _data
if(isset($_POST[id]) && !empty($_POST[id])){
$datamodel_array[$id] = $_POST[id];
}
else{
$datamodel_array[$id] = “””;
}
$datamodel_array[’firstname’]=$_POST[’firstname’];
$datamodel_array[’lastname’]=$_POST[’lastname’];
$datamodel_array[’email’]=$_POST[’email’];
$datamodel_array[’user_level’]=1;
$datamodel_array[’username’]=$_POST[’email’];
$datamodel_array[’password’]=md5($_POST[’password’]);
$datamodel_array[’last_logged’]=”";
$datamodel_array[’registered’]=date(”Y-m-d H:i:s”);
$datamodel_array[’last_ip’]=$_SERVER[’REMOTE_ADDR’];;
$datamodel_array[’company_name’]=$_POST[’company_name’];
$datamodel_array[’company_desc’]=$_POST[’company_desc’];
$datamodel_array[’telephone’]=$_POST[’telephone’];
$datamodel_array[’website_url’]=$_POST[’website_url’];
break;
}
return $datamodel_array;
}
?>
6) models.php
<?
class user_master extends master {
var $name,$tables,$datamodels;
function user_master(){
$this->name=”member_user”;
$this->tables();
$this->datamodels();
}
function tables()
{
$this->tables=array(”teamboard_users”);
}
function datamodels()
{
$this->datamodels=array(”user_data”);
}
}
class user_data extends data {
function user_data(){
$this->fields=array(’user_id’=>”,
’firstname’=>”,
‘lastname’=>”,
‘email’=>”,
‘user_level’=>”,
‘username’=>”,
‘password’=>”,
‘last_logged’=>”,
‘registered’=>”,
‘last_ip’=>”,
‘company_name’=>”,
‘company_desc’=>”,
‘telephone’=>”,
‘website_url’=>”
);
$this->datatype=array(
‘user_id’=>’int’,
’firstname’=>’string’,
‘lastname’=>’string’,
‘email’=>’string’,
‘user_level’=>’int’,
‘username’=>’string’,
‘password’=>’string’,
‘last_logged’=>’string’,
‘registered’=>’string’,
‘last_ip’=>’string’,
‘company_name’=>’string’,
‘company_desc’=>’string’,
‘telephone’=>’string’,
‘website_url’=>’string’
);
}
}
?>