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.

Could you make it CW, please? Obviously there isn't one correct answer to your question. — Piskvor left the building, Sep 23 '10 at 09:37
Did you find the best way to insert data into mysql? If yes and from the list below, which one? Otherwise add one and mark as answer. TY. — abel, Oct 25 '10 at 15:56

Extrakun · Answer 1 · 2010-09-23T11:43:15.163

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

Well you have to get these $title and $author somewhere. To have complete picture there will be some other code — Your Common Sense, Sep 23 '10 at 09:55

score 2 · Answer 2 · answered Nov 23 '10 at 17:34

2

My framework's ORM:

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

Keeping it simple, lightweight, and effective.

answered Nov 23 '10 at 17:34

Lotus Notes

6,302
7
32
47

If there will be more than a dozen of fields, it turns quite boring to write all this data. I'd wish some helper to automate this routine a bit. But I have to admit, it looks very clear and simple. +1 – Your Common Sense Nov 25 '10 at 16:57
There's also an associative array syntax for bulk inserts. – Lotus Notes Nov 25 '10 at 18:54
Lotus, can we talk in private? I swear to god I wrote a system that works exactly like this, without external help, of course. – Christian Mar 29 '11 at 20:02

score 1 · Answer 3 · answered Sep 23 '10 at 13:12

1

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

answered Sep 23 '10 at 13:12

djn

3,950
22
21

score 0 · Answer 4 · answered Sep 23 '10 at 09:48

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

score 0 · Answer 5 · answered Sep 23 '10 at 13:33

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

score 0 · Answer 6 · answered Oct 12 '10 at 17:56

0

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.

answered Oct 12 '10 at 17:56

abel

2,377
9
39
62

Don't you find it quite boring to write each field name four times? Especially if there are 15-20 of them? – Your Common Sense Oct 13 '10 at 05:45
@Col. Shrapnel it kills me, but all in the name of 'security'! – abel Oct 13 '10 at 07:30
2

but this job can be automated. see http://stackoverflow.com/questions/3773406/insert-update-helper-function-using-pdo/3921798#3921798 – Your Common Sense Oct 13 '10 at 08:11
It's ok to IM here. but sorry I know nothing of image processing. Interesting question though. – Your Common Sense Oct 13 '10 at 08:35

score -1 · Answer 7 · answered Sep 23 '10 at 09:20

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.

Mark Baker · Answer 8 · 2010-09-23T09:54:42.600

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

it seems a backend. Can you add a usage example please? – Your Common Sense Sep 23 '10 at 09:45 — Your Common Sense, Sep 23 '10 at 09:45

Code you're using to insert data into mysql database

8 Answers8