Location: PHPKode > scripts > dbApiCreate > dbapicreate/dbApiCreate.inc
<?php
/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 *   CreateApi Class by Mark Williamson hide@address.com
 *   This class will create database access functions for you - just tell it 
 *   the database and table and what functions you would like and it will do 
 *   the rest. It is even possible to get it to work on the fly so that access functions
 *   are never written but dynamically generated and run when needed by a script.  - makes
 *   for some very very very unfriendly coding though ;}  its more useful to just grab the output
 *   and use it as your access library for whatever table.  
 *
 *   See bottom of this file for example usage instructions.
 ***************************************************************************/


class CreateApi {

	var $fields = array();
	var $table = "";
	
	function CreateApi(){
		echo "<?php\n\n";
		echo "\t   /////////////////////////////////////////////////////////////////////////////////////\n";
		echo "\t  // Api Created using dbApiCreate.inc auto-access-lib generation by Mark Williamson //\n";
		echo "\t // hide@address.com available from www.phpclasses.org            //\n";
		echo "\t/////////////////////////////////////////////////////////////////////////////////////\n";
		return(true);
	}

	function addField($field_name){
		if(empty($field_name)){
			$this->errorText("Field name was empty!");
			return(false);	
		}
		if(is_array($field_name)){
			foreach($field_name as $field){
				$this->fields[] = $field;
			}
		}else{
			$this->fields[] = $field_name;
		}
	}

	var $primary_key;
	function setPrimaryKey($key){
		$this->primary_key = $key;
		return(true);
	}

	function setTable($table_name){
		$this->table = $table_name;
		return(true);
	}

	var $db;
	function setDb($dbname){
		$this->db = $dbname;
	}

	var $named_connection = "";
	function setNamedConnection($connection){
		$this->named_connection = $connection;
	}


	var $func;
	var $end;
	var $pre_sql;
	var $pre_sql2;	
	var $slash;
	function createAddFunction(){
		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: add_to_$this->table()\n";
		$this->func .= "// Purpose: Adds a row to $this->table\n";
		$this->func .= "// Returns: MySQL Insert ID\n";
		$this->func .= "/////////////////////////////////////////\n";
		
		$this->func .= "function add_to_".$this->table."(";
		$this->end = count($this->fields)-1;
		$this->slash = "";

		$x=0;
		foreach($this->fields as $f){
			$this->func .= "\$".$f;	
			$this->slash .= "\t\$".$f." = addslashes(\$".$f.");\n";
				$this->pre_sql .= $f;
				$this->pre_sql2 .= "'\$".$f."'";
			if($x != $this->end){
				$this->func .=", ";
				$this->pre_sql .=", ";
				$this->pre_sql2 .=", ";
			}
			$x++;
		}
		$this->func .= "){\n\n";
		$this->func .= $this->slash;
		$this->func .= "\t\$connection = get_named_connection(\"".$this->named_connection."\");\n";
		$this->func .= "\t\$sql = \"INSERT INTO $this->table (".$this->pre_sql.")\n";
		$this->func .= "\t\tVALUES (".$this->pre_sql2.")\";\n\n";
		$this->func .= "\t \$result = mysql_query(\$sql, \$connection) or die(mysql_error(\$connection));\n\n";
		$this->func .= "\t \$insert_id = mysql_insert_id(\$connection);\n";	
		$this->func .= "\treturn(\$insert_id);\n}\n\n";

		echo $this->func;
	}

	function createEditFunction(){
		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: update_$this->table()\n";
		$this->func .= "// Purpose: Updates specified row in $this->table\n";
		$this->func .= "// Returns: True on success (die on error) \n";
		$this->func .= "/////////////////////////////////////////\n";
	
		$this->func .= "function update_".$this->table."(\$".$this->primary_key.", ";
		$this->pre_sql = "";
		$this->slash = "";

		$this->end = count($this->fields)-1;
		$x=0;
		foreach($this->fields as $f){
			$this->func .= "\$".$f;
			$this->pre_sql .= "$f = '\$".$f."'";
			$this->slash .= "\t\$".$f." = addslashes(\$".$f.");\n";
			if($x != $this->end){
				$this->func .=", ";
				$this->pre_sql .=", ";
			}
			$x++;
		}
	
		$this->func .= "){\n\n";
		$this->func .= "\t\$connection = get_named_connection(\"".$this->named_connection."\");\n";
		$this->func .= "\t\$sql = \"UPDATE $this->table SET \n\t\t$this->pre_sql\n";
		$this->func .= "\t\tWHERE $this->primary_key = '\$".$this->primary_key."'\";\n\n";
		$this->func .= "\t\$result = mysql_query(\$sql, \$connection) or die(mysql_error(\$connection));\n\n";
		$this->func .= "\treturn(TRUE);\n}\n\n";

		echo $this->func;
	}

	function createDeleteFunction(){

		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: delete_from_$this->table()\n";
		$this->func .= "// Purpose: Deletes specified row in $this->table\n";
		$this->func .= "// Returns: True on success (die on error) \n";
		$this->func .= "/////////////////////////////////////////\n";

		$this->func .= "function delete_from_".$this->table."(\$".$this->primary_key."){\n ";
		$this->pre_sql = "";
			$this->slash = "\t\$".$this->primary_key." = addslashes(\$".$this->primary_key.");\n";

		$this->end = count($this->fields)-1;
		$x=0;
		foreach($this->fields as $f){
			$this->pre_sql .= "$f = '\$".$f."'";
			if($x != $this->end){
				$this->pre_sql .=", ";
			}
			$x++;
		}
		$this->func .= $this->slash;	
		$this->func .= "\t\$connection = get_named_connection(\"".$this->named_connection."\");\n";
		$this->func .= "\t\$sql = \"DELETE * FROM $this->table WHERE $this->primary_key = '\$$this->primary_key'\";\n";
		$this->func .= "\t\$result = mysql_query(\$sql, \$connection) or die(mysql_error(\$connection));\n\n";
		$this->func .= "\treturn(TRUE);\n}\n\n";

		echo $this->func;
	}

	var $find_field;
	var $field_type;
	function createFindByFunction($find_field, $field_type="int"){
		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: ".$this->table."_find_by_$this->find_field()\n";
		$this->func .= "// Purpose: Finds rows with specified value for $this->find_field in $this->table\n";
		$this->func .= "// Returns: struct of all matching rows \n";
		$this->func .= "/////////////////////////////////////////\n";


		if(empty($find_field)){
			$find_field = $this->primary_key;
		}
		$this->find_field = $find_field;	
		$this->field_type = $field_type;	
		$this->slash = "\t\$".$this->find_field." = addslashes(\$".$this->find_field.");\n";

		switch($field_type){
			case "string" :
				$this->pre_sql = " $this->find_field LIKE '%\$$this->find_field%' ";
			break;
			case "int" : 
				$this->pre_sql = " $this->find_field = '\$$this->find_field' ";
			break;
		}

		$this->func .= "function ".$this->table."_find_by_".$this->find_field."(\$$find_field){\n";	
		$this->func .= $this->slash;
		$this->func .= "\t\$connection = get_named_connection(\"".$this->named_connection."\");\n";
		$this->func .= "\t\$sql = \"SELECT * FROM $this->table WHERE $this->pre_sql \";\n";
		$this->func .= "\t\$result = mysql_query(\$sql, \$connection) or die(mysql_error(\$connection));\n\n";
		$this->func .= "\t\$return = array();\n";
		$this->func .= "\twhile(\$rows = mysql_fetch_array(\$result, MYSQL_ASSOC)){\n";
		$this->func .= "\t\t \$return[] = \$rows;\n";
		$this->func .= "\t}\n";

		$this->func .= "\t return(\$return);\n}\n";
		echo $this->func;


	}

	function createGenericFind(){

		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: ".$this->table."_generic_find()\n";
		$this->func .= "// Purpose: Finds rows with specified value for specified fields in $this->table\n";
		$this->func .= "// Returns: struct of all matching rows \n";
		$this->func .= "/////////////////////////////////////////\n";

		$this->func .= "function ".$this->table."_generic_find(\$find){\n";	
		$this->func .= "\t\$connection = get_named_connection(\"".$this->named_connection."\");\n";
		$this->func .= "\t".'$x=0; $n=count($find)-1;'.";\n";
		$this->func .= "\t".'foreach($find as $key=>$val){'.";\n";
		$this->func .= "\t\t".'$whereSQL .= "$key = \\"$val\\""'.";\n";
		$this->func .= "\t\t".'$whereSQL.=($x<$n)?" AND ":"\n"'.";\n";
		$this->func .= "\t\t".'$x++;'."\n\t}\n";

		$this->func .= "\t \$sql = \"SELECT * FROM $this->table WHERE \$whereSQL \";\n";

		$this->func .= "\t\$result = mysql_query(\$sql, \$connection) or die(mysql_error(\$connection));\n\n";
		$this->func .= "\t\$return = array();\n";
		$this->func .= "\twhile(\$rows = mysql_fetch_array(\$result, MYSQL_ASSOC)){\n";
		$this->func .= "\t\t \$return[] = \$rows;\n";
		$this->func .= "\t}\n";

		$this->func .= "\t return(\$return);\n}\n";

		echo $this->func;


	}

	function createGenericEdit(){

		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: ".$this->table."_generic_update()\n";
		$this->func .= "// Purpose: update specified fields with values in $this->table\n";
		$this->func .= "// Returns: mysql_affected_rows() \n";
		$this->func .= "/////////////////////////////////////////\n";


		$this->func .= "function ".$this->table."_generic_update(\$find, \$matches){\n";	
		$this->func .= "\t\$connection = get_named_connection(\"".$this->named_connection."\");\n";
		$this->func .= '	$x = count($find); $i=0; $y=count($matches); $n=0;';
		$this->func .= '
	foreach($find as $key=>$val){
		$updsql .= "$key=\'$val\'";
		if($i<$x-1){
			$updsql.=",";
		}	
		++$i;	
	 }
	foreach($matches as $key=>$val){
		$match_sql .= "$key=\'$val\'";
		if($n<$y-1){
			$match_sql.=",";
		}	
		++$n;	
	 }
	$sql = "UPDATE '.$this->table.' SET $updsql WHERE $match_sql";'."\n";
				
		$this->func .= "\t\$result = mysql_query(\$sql, \$connection) or die(mysql_error(\$connection));\n\n";
		$this->func .= "\t\$return = mysql_affected_rows(\$result);\n";
		$this->func .= "\t return(\$return);\n}\n";

		echo $this->func;

	}


	var $host; var $user; var $pass; var $db;
	function  create_named_connection($host, $user, $pass){
		$this->host = $host;
		$this->user = $user;
		$this->pass = $pass;

		$this->func = "\n\n/////////////////////////////////////////\n";
		$this->func .= "// Function: get_named_connection('database')\n";
		$this->func .= "// Purpose: Connects to specified database \n";
		$this->func .= "// Returns: \$connection handle for mysql_query(\$sql, \$connection) \n";
		$this->func .= "/////////////////////////////////////////\n";


		$this->func .= "function get_named_connection(\$db){\n";
		$this->func .= "\t//Recommend moving this function outside webroot and including it to protect passwords\n";
		$this->func .= "\t\$connection = mysql_pconnect('$host', '$user', '$pass');\n";
		$this->func .= "\t\$selectdb = mysql_select_db(\$db);\n";
		$this->func .= "\treturn(\$connection);\n}\n";
		echo $this->func;

	}

	var $field_list = array();
	function LearnTableLayout(){
		$c = mysql_pconnect($this->host, $this->user, $this->pass) or die('mysql error - unable to connect to '.$this->host);		
		mysql_select_db($this->db);
		$result = mysql_query("desc $this->table", $c) or die(mysql_error());
		while($rows = mysql_fetch_assoc($result)){
			if($rows['Key'] == 'PRI'){
				$this->setPrimaryKey($rows['Field']);
			}else{
				$this->addField($rows['Field']);
			}
			$this->field_list[] = $rows['Field'];
		}
	}

	function get_field_list(){
		return($this->field_list);
	}

	//just an error handler
	function errorText($msg){
		echo "Error: ".$msg."\n";
		return(true);	
	}

	function finishApi(){
		echo "\n\n?>\n";	
	}
}




 
/*


//EXAMPLE USAGE - where the db connection details are different from your own you must specify the fields 

$t = new CreateApi();                                         //call class
$t->setNamedConnection("cybertillv1_0");                      //tell it the name of the database the table is in
$t->setTable("stk_item");                                     //tell it the name of the table
$t->setPrimaryKey("itemID");                                  //tell it the primary key of the mysql table you are creating api for
$t->create_named_connection("127.0.0.1", "dbuser", "dbpass"); //tell it your database connection details

                                                              //tell it all the field names in the mysql table
$t->addField(array("prodID", "locationID", "p_rrp", "p_store", "p_web", "p_staff", "item_name", "stk_min", "stk_max"));	

                                                              //tell it which functions to create for you
$t->createAddFunction();
$t->createEditFunction();
$t->createDeleteFunction();
$t->createGenericEdit();
$t->createGenericFind();
                                                      //tell it which SPECIFIC fields you would like functions to search on
$t->createFindByFunction("prodID", "int");
$t->createFindByFunction("valueID", "string");
$t->createFindByFunction("itemRef", "string");
$t->createFindByFunction("stk_min", "int");
$t->createFindByFunction("stk_max", "int");

$t->finishApi(); //adds the final ?> onto the string thats outputted. 

*/





?>
Return current item: dbApiCreate