Code you're using to insert data into mysql database

Question

Please, read this question carefully before answer or pressing "close" link.
This question is about sharing experience, exchange of tips and tricks.

What code you are using to insert data into mysql database?

Just a snip of code from your real project. Preferably of CRUD type.
I mean real code. Please, do not copy-paste code examples from manual. It's different from the real life needs. Please, do not answer "you can use these methods...". I know them all. I am asking not for methods but for real coding experience.

I find it very interesting and very enlightening to share your code, to learn from others experience.

Please note that code must be complete, including all data preparations. But no validation if possible. A backstage work can be omitted if there is too much of it (like model initializing and such). I am asking more for food for thought than for code to copy and paste.

Please, do not close this topic too fast.
I an hungry for real world code examples, there is a very little of them here, but dummy code snippets everywhere.

Languages other than PHP are welcome, as well as any ORM or framework usage. But please, remember - not copy-paste from documentation example, but from your own project. There is huge difference.

Answer 1

Here is an example from my one of my scripts:

$fields=array('city','region');
if ($id=intval($_POST['id'])) {
  $query="UPDATE $table SET ".dbSet($fields)." WHERE id=$id";
} else {
  $query="INSERT INTO $table SET ".dbSet($fields);
}
dbget(0,$query);

dbSet() is a helper function to produce an SQL SET statement out of $_POST array and array contains field names, using a consideration which makes form field names equal to table field names.

dbget() is a function to run a query, 0 means raw type of return value, in case it will be needed in the further code.

Answer 2

There's the Redbean ORM. What I have done is basically wrap my own code around its domain object, hence it looks like

 class Book extends RedBean_DomainObject
   {
       public function __construct($id)
       {
           if ($id!=0)
             $this->find($id);               
       }

       public function save_details($author, $title)
       { 
            // insert OR update new entry
            $this->author = $author;
            $this->title = $title;
            $this->save(); 
        }
   }

The code will check if the 'bean' exists; if it does, it will load it. You assign properties to the class, and call the save() method to save it to the bean. The RedBean ORM will automatically detect if it is a save or an update. Note: The RedBean Domain Object has been replaced by something better, though.

I am also using WordPress wp-db, and I like the syntax

 $wpdb->insert("books", array('title' => $title, 'author' =>$author));

I found a little wrapper online which allows me to do INSERT...ON DUPLICATE KEY too.

$wpdb->insert_on_duplicate("author_book_relationship", 
                          array('book_id' => $book_id,
                                'date_published' =>$date_published),
                          array('book_id' => $book_id));

The first parameter is the table, the second is the insert/update information and the last one is the where clause for the UPDATE part.

Edit

I usually wrap SQL functions in a helper

class BookHelper
{
    public function save_relationship($id, $book, $author)
    {
       global $wpdb;

       $wpdb->insert_on_duplicate("author_book_relationship", 
                          array('book_id' => $book_id,
                                'date_published' =>$date_published),
                          array('book_id' => $book_id));
    }
}

And inside a strategy

class BookSaveStrategy
{
     protected $book_helper;

     public function save_relationship($id, $book, $title)
     {
          // validate id, book and title
          //.....
          // Save if ok
          $this->book_helper->save_relationship($id, $book, $title);
     }
}

Which can be use in a controller

if (isset($_POST['save_book']))
{
    $book_save_strategy->save($_POST['id'], $_POST['author'], $_POST['title']);
}

Answer 3

public static function insertSQL($table, $fields, $values)
{
    if (self::$_databaseLogger == null) { self::$_databaseLogger = &LoggerManager::getLogger('databaseAccess'); }

    $fieldCount = count($fields);
    $valueCount = count($values);

    if ($fieldCount != $valueCount) {
        self::$_databaseLogger->error('database.insertSQL() Error: field list is different size to value list');
        throw new Exception("Database::insertSQL field list is different size to value list");
    } else {
        $sql = 'INSERT INTO '.$table.' ('.implode(', ',$fields).') VALUES ('.implode(', ', $values).')';
        self::$_databaseLogger->databaseQuery('database.insertSQL(): '.$sql);
        $statement = database::getDBConnection()->prepare($sql);

        $insertedRows = $statement->execute();
        if ($insertedRows === False) {
            self::$_databaseLogger->error('database.insertSQL(): insertSQL ERROR');
            throw new Exception('database::insertSQL ERROR');
        }
        self::$_databaseLogger->databaseResult('database.insertSQL(): Inserted '.$insertedRows.' rows');

        return $insertedRows;
    }
}   //  function insertSQL()

All data values are validated, quoted where appropriate for strings and escaped prior to calling the insertSQL() method. The logger used is log4PHP.

EDIT

Use case:

$fileTreeTableFieldsArray = array ( 'FILE_ID',
                                    'FILE_TYPE',
                                    'FILE_NAME',
                                    'FILE_PARENT_ID',
                                    'FILESIZE',
                                    'CREATED_BY',
                                    'CREATED_ON',
                                    'APPLICATION_CONTEXT' );

$fileTreeTableValuesArray = array ( database::getSQLValueString($this->_fileID,'int'),
                                    database::getSQLValueString($fileType,'text'),
                                    database::getSQLValueString($name,'text'),
                                    database::getSQLValueString($parentID,'int'),
                                    database::getSQLValueString($fileSize,'int'),
                                    database::getSQLValueString($_SESSION["USERID"],'int'),
                                    database::getSQLValueString('sysdate','datetime'),
                                    database::getSQLValueString($_SESSION["APPLICATION"],'int') );

Database::startTransaction();
try {
    $result = database::insertSQL('RCD_FILE_TREE',
                                  $fileTreeTableFieldsArray,
                                  $fileTreeTableValuesArray);
} catch (Exception $e) {
    Database::rollback();
    $error = $this->_setError(baseFileClassInsertException, $this->_fileCategory, $this->_fileName, $sql, $e->getMessage());
    $this->_logger->error($this->_ErrorMessage);
    return $error;
}
Database::commitTransaction();

Answer 4

Below are some examples from our company's framework.

Raw queries:

Db::query(
    "UPDATE sometable SET city = %s, region = %s WHERE id = %d",
    $city, $region, $id);

// or

Db::query(
    "UPDATE sometable SET city = %(city)s, region = %(region)s WHERE id = %(id)d",
    array('city' => $city, 'region' => $region, 'id' => $id));

There is a number of placeholders supported, such as %s (string), %d (integer), %f (float), %? (auto-detects type of the parameter), %- (no escape, insert as is) and even %as, %ad etc. (arrays of strings/integers/whatever, replaced with comma-separated values). Everything is properly escaped behind the scene.

There is also some kind of ORM with quite limited features:

$city = !empty($id) ? City::fetchOneById($id) : City::create();
$city->setValues(array('city' => $city, 'region' => $region));
$city->save();

Answer 5

Using ADOdb:

$stmt = $db->Prepare("INSERT INTO `table` (`column1`, `column2`, `column3`) VALUES (?, ? ,?)");

$insert = $db->Execute($stmt, array($value1, $value2, $value3));

if($insert === false) throw new Exception($db->ErrorMsg());

Answer 6

Function taken from a recent project's model (using Codeigniter). It's used to insert a period of vacation in the calendar and in the table that keeps track of variations (minus for vacations taken, plus is system added every month).

DateIterator is a custom iterator which returns consecutive dates in the format yyyy-mm-dd, work_days is a custom helper that count the - ugh - number of working days between two supplied dates. Apologies for italian variable names.

function richiesta_ferie($utente, $inizio, $fine) {
    // INSERT INTO Ferie
    $data = array(
        'ID_Utente' => $utente,
        'Richiesta' => date('Y-m-d H:i:s'),
        'Inizio'    => $inizio,
        'Fine'      => $fine,
        'Stato'     => 'P',
    );
    $this->db->insert('Ferie', $data);

    $ID_Ferie = $this->db->insert_id();

    // INSERT INTO Variazione
    $work_days = -1 * work_days($inizio, $fine);
    $data = array(
        'ID_Richiesta' => $ID_Ferie,
        'ID_Utente'    => $utente,
        'Giorni'       => $work_days,
    );
    $this->db->insert('Variazione', $data);

    // INSERT INTO Giorno
    // DateIterator defined in helpers/MY_date_helper.php
    $DateIterator = new DateIterator($inizio, $fine);
    foreach ( $DateIterator as $date ) {
        // skip sundays
        if ( date('w', strtotime($date)) == 0 ) {
            continue;
        }
        $data = array(
            'ID_Utente'     => $utente,
            'ID_Richiesta'  => $ID_Ferie,
            'TipoRichiesta' => 'F',
            'Giorno'        => $date,
            'Stato'         => 'P',
        );
        $this->db->insert('Giorno', $data);
    }
}

Answer 7

try {
$pSt = $dbh->prepare('INSERT INTO voucher (field1, field2, field3) VALUES (:field1, :field2,:field3)');

$pSt->execute(array(':field1'=>$field1, ':field2' =>$field2,':field3'=>$field3));
$status=$pSt->errorCode();
if($status=='00000'){
echo "Voucher $voucher created successfully.";
}else{
$error=$pSt->errorInfo();
echo $error[2];
}
} catch (Exception $e) {
  echo "Failed: " . $e->getMessage();
}

Changed field names.Otherwise this is the code that I use.