Insert script

[gecko]

Hi,

 

Hopefully I can make myself clear here on what I'm trying to do

 

I'm looking for a way to have a single insert script thgat inserts records into a mySQL DB, with the ability to insert into different tables - one file inserts all, was what I was hoping

 

So, instead of having many different files that insert records into different ables with different attributes and values, I;m trying to find a way to have ONE insert file, that can handle different insert requests to different tables, just basically to save time and cut back on the number of files in a directory. For the site I;m doing, I have a lot of functionality, mainly with insert queries (e.g - add assignment, add student, add staff, etc etc)

It would just make sense to have one file that could insert into m,any different tables depending on the varibales sent from a previous form.

 

For exmaple, instead of INSERT INTO tblStudent, a script may read INSERT INTO $table; $table being a value sent from a previous form, like "student".

 

Hopefully this all makes sense; I just can't work out how to do this, nor do I even know if this is possible!

 

Any suggestions would be much appreciated! 

[JD*]

You can build a custom function, i.e.

 

function insert_to_db($db, $fields, $values)
{
   //Do checking here to make sure $db, $fields and $values are valid

  mysql_query("INSERT INTO ".$db."(".$fields.") VALUES(".$values.")") or die(mysql_error());
  echo "Values Inserted";
}

 

Just make sure you clean all of your data, and that your values are surrounded by single quotes, etc.

[Ken2k7]

That should be INSERT INTO table, not INSERT INTO db. Some sanitation work couldn't hurt. And SQL error checking.

[JD*]

Whoops, I meant table...early mornings and no caffine!

[gecko]

That looks about right, guys.

 

One question : how do I define the fields and values used?

 

I should think I would be POSTing them to this file, but not sure how to handle them. There could be 2 fields on one occasion, whereas on another occasion. there may be 5 fields to be inserted, together with their values.

 

Any ideas?

 

Thanks again

[Ken2k7]

$table_name = 'TBL_NAME';

$fields = array ('field_one', 'field_two');
$values = array ('value_one', 'value_two');

$sFields = array();
foreach ($fields as $field) {
     $sFields[] = '`' . $field . '`';
}

$sValues = array();
foreach ($values as $value) {
     $sValues[] = '"' . $value . '"';
}

insert_to_db($table_name, implode(',',$sFields), implode(',',$sValues));

[the182guy]

Or better still create a function that will take 2 arguments: an array of data and the table name, then you can call it like:

 

$table = 'table_name';

$data = array(
     'field1' => 'value1',
     'field2' => 'value2',
     'field3' => 'value3',
);

$id = insertQuery($table, $data);

 

One advantage to this is you can add an argument which acts a switch for escaping the values, or use double/single quotes. The same thing can be done for the UPDATE query, just need to add an extra argument for the WHERE clause.

 

I use a function like this when I extend the MySQLi class.

[gecko]

Thanks for your replies.

 

Although, I don't quite understand how the script can be dynamic with regards to using it with several forms; the file I'm assuming will have the values posted to it from a form, where the values and text fields will be different with each form, so I don't understand how the 'field1' and 'value1' variables can acept data from different forms.

 

Sorry for my stupidity if I'm missing something!