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.");
}
}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