gecko Posted May 1, 2009 Share Posted May 1, 2009 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! Quote Link to comment Share on other sites More sharing options...
JD* Posted May 1, 2009 Share Posted May 1, 2009 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. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 1, 2009 Share Posted May 1, 2009 That should be INSERT INTO table, not INSERT INTO db. Some sanitation work couldn't hurt. And SQL error checking. Quote Link to comment Share on other sites More sharing options...
JD* Posted May 1, 2009 Share Posted May 1, 2009 Whoops, I meant table...early mornings and no caffine! Quote Link to comment Share on other sites More sharing options...
gecko Posted May 1, 2009 Author Share Posted May 1, 2009 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 Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 1, 2009 Share Posted May 1, 2009 $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)); Quote Link to comment Share on other sites More sharing options...
the182guy Posted May 1, 2009 Share Posted May 1, 2009 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. Quote Link to comment Share on other sites More sharing options...
gecko Posted May 2, 2009 Author Share Posted May 2, 2009 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! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.