Efficient way to insert records?

[Strahan]

Hello.  I'm trying to figure out an easy and efficient way to insert records.  Sometimes the schema changes, so I wanted something reasonably dynamic.  This is what I'm doing:

$pdo = new PDO("mysql:host=" . SQL_Server . ";dbname=" . SQL_Database, SQL_User, SQL_Pass);
$sql = $pdo->query("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table' AND column_comment <> 'skip'"); $rows = $sql->fetchAll();
$head = "INSERT INTO table ("; $tail = ") VALUES ("; $data = array();
foreach ($rows AS $row) { $head .= "{$row["column_name"]}, "; $tail .= "?, "; $data[] = $_REQUEST[$row["column_name"]]; }
$sql = $pdo->prepare(str_replace(", )", ")", "$head$tail)"));
$sql->execute($data);
$pdo = null;

I know it's a bit of a kludge, heh, but it works.  I'm just wondering what a more proper way would be?

[mac_gyver]

your code is technically correct, but there are some issues with some things -

 

1) since the column names can be anything, you should enclose them with identifier quote characters, which for mysql is a back-tick `.

 

2) by using the column_comment to control skipped columns, you cannot put a useful comment on those columns. it would be better that if a column name doesn't exist in the array of input data ($_REQUEST in your code), to leave that column out of the query.

 

3) at the point of running your database level code, you shouldn't know or care where the input data is coming from. the $_REQUEST array of input data should instead be a general php array variable, so that the data can come from any source, such as a form, a csv file, or computed values, without needing to change any of the lower level code.

 

4) if your table name is dynamic and comes from user supplied input, you will need to validate that it is only and exactly a permitted table name, since there's no protection you can use in the INSERT query for the supplied table name (you can use a prepared query for the SELECT column_name FROM ...  query to safely supply the table name in that query.)

 

5) i hope in general that you are not making a database connection, running one query, then closing the database connection. this is a killer on your database and can cause a noticeable increase in the time it takes your page to be generated on the server. 

 

and if you want, you can write that code using only php array functions - 

$pdo = new PDO("mysql:host=" . SQL_Server . ";dbname=" . SQL_Database, SQL_User, SQL_Pass);

function _add_colon($val){ // add the : to the array key/index names
  return ":$val";
}

$sql = $pdo->query("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table' AND column_comment <> 'skip'");
$cols = $sql->fetchAll(PDO::FETCH_COLUMN); // fetch just the column_name values as an array

$keys = array_map('_add_colon',array_keys($arr)); // $arr is the general purpose array holding the input data
$data = array_combine($keys,$arr);

// named place holders are used since the order of the columns and input data can be different without any loop in this code to assign values in the same order
$query = "INSERT INTO table (`".implode('`,`',$cols)."`) VALUES (:".implode(',:',$cols).")"; // form query in a variable so you can echo/log it for debugging
$sql = $pdo->prepare($query);
$sql->execute($data);
$pdo = null;

[Strahan]

Thanks, I appreciate the advise.  I'll implement your suggestions.  In regards to #5, in this particular case I am opening, querying, closing because nothing else on the site uses SQL.  In my more DB heavy pages, I create the connection at the top of the page and null it at the end.  The meat of the page calls functions when necessary to do DB IO and passes the connection variable to the function as a parameter.  Is that OK as far as performance?

[mac_gyver]

In my more DB heavy pages, I create the connection at the top of the page and null it at the end.  The meat of the page calls functions when necessary to do DB IO and passes the connection variable to the function as a parameter.  Is that OK as far as performance?

 

 

yes, that's okay. using one database connection through out the code on the page.