Code you're using to insert data into mysql database [closed]

My framework's ORM:

$User = new User();
$User->name = 'John';
$User->email = '[email protected]';
$User->homepage = 'http://example.com';
$User->save();

Keeping it simple, lightweight, and effective.

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']);
}

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();

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());

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);
    }
}

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.

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.

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();