Trouble with multiple table insert

[J_C]

Hey All

Im just got started with OOP and having some trouble inserting form data into mysql table.

I store all the the form data in a variable eg: $field_array and pass it to a function called 'tableInsert'. Then I loop through the array and construct the insert query.

The above works fine. My problem is that the form data get stored in two different tables and that all the data is located in a single array. How can I loop through the array and insert it into two different tables.

I hope the above made sense. Below is the code i use.

$field_array = "$HTTP_POST_VARS"; //gets form data

$tableFunction = new tableFunctions();
$tableFunction-> tableInsert("tbl_agent_general",$field_array);

//class code follows below

class tableFunctions extends dbConnection
{

    var $tableName;
    var $fieldsArray;

    function tableInsert($tableName,$fieldsArray)
    {

        $this->tableName = $tableName;
        $this->fieldsArray = $fieldsArray;

         $sql_query = "INSERT INTO $tableName SET ";

         while(list($key, $value) = each($fieldsArray))
         {
            $sql_query .= "$key = '$value', ";
            echo "$key = $value<br>";
         }
         $sql_query = rtrim($sql_query, ', ');

         mysql_query($sql_query,"$this->dbcnx") or die(mysql_error());

    }

}

I have have searched around and cant find anything. If anybody can help, I will greatly appreciate it.

Thanks

[paulnaj]

The way you've got it setup just means you have to call the tableInsert() function twice, once for each table.

Paul 🙂

[J_C]

Thanks for the reply Paul

Just want want know. By doing this, it will try to insert data into the tables that does not belong there. Cant i split the array up and run through it then? The problem is that some of my other forms like the client information, stores the data in three different tables.

Is there any other way of doing this, without having to write the queries everytime.

Thanks for the help.

[paulnaj]

Hi J_C,

You could try naming the form fields accordingly ...

table1_field1
table1_field2

table2_field1
table2_field2

table3_field1
table3_field2

Then ...

<?php
// ... split into separate fieldArrays
$fieldArrays = array();

foreach($_POST as $key => $value){ // Use $_POST rather than $HTTP_POST_VARS
	// Get the tablename and fieldname
	$key = explode('_', $key);
	$table = $key[0];
	$field = $key[1];

// See if fieldArray already exists, if not create it.
if(!isset($fieldArrays[$table])){
	$fieldArrays[$table] = array();
}

// Store the data
$fieldArrays[$table][$field] = $value;
}

// Now insert into each table

$tableFunction = new tableFunctions(); 

$foreach($fieldArrays as $table => $fieldArray){
	$tableFunction->tableInsert($table, $fieldArray);
}
?> 

Of course, this will only work in certain circumstances, but you get the drift.

Paul 🙂

[J_C]

Shot for the help Paul.

Got it working. Like you said, wont work for every situation. Will just work on it a bit.

Thanks Again