Page 1 of 1

Check for valid data before inserting into a database

Posted: Mon Aug 06, 2007 5:31 am
by dream2rule
Hello All,

I have a generic mysql application script wherein a function called insert_table() is present which helps in inserting data into a MySQL database.

I want to verify the data to be inserted is of the valid datatype or not.

i.e, while adding a table to a database, i am specifying the datatype for the field types. Now, When i want to insert data into table i would like to check whether it is of the same data type as the one during table creation.

In the below code, I am able to get the datatypes( using mysql_field_type() ) and the values to be inserted.

I want to check whether the datatypes are int,varchar etc...

Everything is done using functions. So when i call the insert function it checks for the respective datatypes and inserts the data into the table created.

Here's the code:

mysql_1.php

Code: Select all

function query_db($sql)
{
	//executes the query that is being passed as an argument
     	$result = mysql_query($sql,$this->dbc);
	echo $result;
	return ($result);
}


function add_table($db_name,$tblname,$num_fields,$fields)
{
	//adding a table into the database
	//add_table("my_table",4,"LastName varchar(30),FirstName varchar(30),Address varchar(255),Age int(3)")
	/** For this kind of table, call a function add_table() as above
	** CREATE TABLE my_table 
	   (
			LastName varchar(30),
			FirstName varchar(30),
			Address varchar(255),
			Age int(3) 
           )
	**/
		//echo $db_name;
		$conn = mysql_select_db($db_name,$this->dbc);
		$sql="CREATE TABLE ".$tblname;
		if(!empty($num_fields))
		{
			$sql.="(".$fields.")TYPE=MyISAM;";
			//echo $sql."<br>";
		
			$tmp=mysql_query($sql);
			//echo $tmp;
			if(!empty($tmp))
			{
				echo "<br>Table <b>$tblname</b> Created Successfully.";
			}
			else 
			{
				echo("<br>Table cannot be created.It may be that it already exists.Please drop the existing table and create a new one!");
			}
		}
}
		

function insert_table($db_name,$tab,$campos,$valores)
   	{
		//inserting a record in a table of the database
   		$conn = mysql_select_db($db_name,$this->dbc);
		
		//Here, $campos=>the column_names array and $valores => the values to be inserted and $tab=> the table name
    	$inicio="INSERT INTO $tab(";
    	$meio=") VALUES (";
    	$fim=")";
    	$valor = sizeof($campos); //counting the number of elements present in the column_names
    	
		//checking for the field type while inserting data into table
		$result = mysql_query("SELECT * FROM $tab");
		$fields = mysql_num_fields($result);
		$type = "";
		for($j=0; $j < $fields; $j++)
		{
			$type.=mysql_field_type($result, $j);
			if($j != $fields)
			{
          		$type.=",";
        	}
		}
		
		$strc="";
    	for($i=0;$i <= ($valor-1);$i++)
		{
        	$strc.="$campos[$i]";
        	if($i != ($valor-1))
			{
          		$strc.=",";
        	}
     	}
    	
		$strv="";
    	for($k=0;$k <= ($valor-1);$k++)
		{
        	$strv.="\"$valores[$k]\"";
        	if($k != ($valor-1))
			{
          		$strv.=",";
        	}
     	}

		echo "<br>".$type."<br>".$strv."<br>"; 
                
                //here i get the values and field-type in 2 different arrays respectively
		
                $values = explode(",",$strv);
		$type = explode(",",$type);
		
		$count_values = count($values);
		//since the array is split considering the "," we get 1 extra value than the existing one,
		//so deduct 1 from the obtained count of the datatypes
		$count_type = count($type) - 1;
		
		//here i would like to check for the similarity between table field datatypes and values to be inserted

                $insere="$inicio$strc$meio$strv$fim";
		//$sts = $this->query_db($insere);
	 	if(!empty($sts))
	 	{
			echo "<br>Record Inserted Successfully.";
	 	}
	 	else
	 	{
			echo("<br><b>Error:</b> Record cannot be inserted into the Database.");
	 	}
    }
I am calling the functions in another page called mysql_2.php

mysql_2.php

Code: Select all

include("mysql_1.php");

$c = new database();//defining the object for class database 

//SYNTAX - names_db();
$c->names_db(); //displays a list of all the databases

//SYNTAX - add_table($db_name,$tblname,$num_fields,$fields);
$c->add_table("test3","mytable",6,"id smallint(5) NOT NULL auto_increment,LastName varchar(30) NOT NULL,FirstName varchar(30) NOT NULL,Address varchar(255) NOT NULL,Age int(3) NOT NULL,email varchar(255) NOT NULL,PRIMARY KEY(id)"); 

//SYNTAX - insert_table($db_name,$table_name,$column_names,$values);
$column_names = array("LastName","FirstName","Address","Age","email");
$values = array("mirza","sania","india","25","saniamirza@yahoo.com");
$c->insert_table("test3","mytable",$column_names,$values);

Can anyone help me with this...???
Awaiting Reply..

Thanks and Regards,
Dream2rule