MySQL - Insert Data Automation

This is where you share YOUR scripts with others

Moderators: egami, macek, gesf

Post Reply
User avatar
php-forum GURU
php-forum GURU
Posts: 2192
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Fri Nov 05, 2010 5:39 am

I wrote this a while ago, and it's very useful to me, hopefully it will be of use to you as well.

First, keep in mind that my db field names have prefixes. I don't want my HTML form names to be exactly like my db field names for obvious reasons. So, example is:

<input type="text" name="firstname" value="">

This gives the standard user the name of the input variable. But on the back end, they can't see what it's being transformed to.

I add a pre-determined field according to how I name my tables, which are pretty wacky and far fetched.

Then I parse through the arrays to match the fields, then create an input string to minimize any extra guess work that may or may not need to happen.

Code: Select all

$table  = 'My_Table';
$prefix = 'ccmt_'; // because the db field names are different than the HTML Form Names

$query = "SHOW COLUMNS FROM $table";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    $fields[] = $row['Field'];
// Produce an array of all of the field names

//replace _POST vars with our prefix
foreach($_POST as $key => $value) {
    $newkey = $prefix.$key;
    $array[$newkey] = $value;

foreach($fields as $key => $value) {
  // Key == 0,1,2,3etc.
  // $value = fieldname, ie..  id,cost,lastname,etc.
  // but usually I prepend my field names to be different than the form names
    if (in_array($value,array_keys($array))) {
        $newarray[$value] = mysql_real_escape_string(trim($array[$value]));
    } else {
        $newarray[$value] = '';

unset($array); // kill the array we created. We don't need it anymore.

$counta = 1;
$ar_cnt = count($newarray);
$string = '';

foreach($newarray as $key => $value) {
    if ($counta != $ar_cnt) {
        $string .= "'$value', ";
    } else {
        $string .= "'$value' ";

$query  = "INSERT INTO $table VALUES ($string)";
$result = mysql_query($query) or die ("Something just went horribly wrong. ".mysql_error());
if (mysql_affected_rows() > 0) {
    echo "Some success message here.<br>";
} else {
    echo "Some failed message here.";

Post Reply