The code you use to insert data into mysql database

Question

Please read this question carefully before answering, or click close.
This question is about sharing experiences, sharing tips and tricks.

What code do you use to insert data into mysql database?

Just a snippet of code from your real project. Preferably type CRUD.
I mean the real code. Please do not copy the code examples from the manual. This is different from the real needs of life. Please do not answer "you can use these methods ...". I know them all. I ask not for methods, but for real coding experience.

I find it very interesting and very useful to share my code, learn from others.

Please note that the code must be complete, including all preparatory operations. But without verification, if possible. The back work can be omitted if it is too much (for example, initializing the model, etc.). I ask more for food for thought than for copying and pasting code.

Please do not close this topic too quickly. I'm hungry for real world code examples, there are very few of them, but there are snippets of code everywhere.

Languages ​​other than PHP are welcome, as well as any use of ORM or frameworks. But please remember - do not copy-paste from the sample documentation, but from your own project. There is a huge difference.

Answer 1

There is Redbean ORM . What I did was basically wrap my own code around a domain object, so 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 checks for a bean; if that happens, he will download it. You assign properties to the class and call the save () method to save it to the bean. RedBean ORM will automatically detect if this is a save or update. Note The RedBean domain object has been replaced with something better.

I also use WordPress wp-db and I like the syntax

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

I found a little online wrapper that 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 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 the 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); } } 

What can be used in the controller

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

Answer 2

My ORM framework:

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

Keeping it simple, easy and efficient.

Answer 3

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 create an SQL SET statement from the $ _POST array, and the array contains field names using a consideration that makes the form field names equal to the table field names.

dbget() is a function to run the query, 0 means the raw type of the return value, if needed in the following code.

Answer 4

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 5

 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 checked, quoted as appropriate for rows, and escaped before 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 6

Below are some examples from our system.

Raw requests:

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

Several supported placeholders are supported, such as %s (string), %d (integer), %f (float), %? (automatically determines the type of parameter), %- (no escape, insert as is), and even %as , %ad , etc. (arrays of strings / integers / independently, replaced by values ​​separated by commas). Everything is properly shielded behind the scenes.

There is also an ORM with fairly limited features:

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

Answer 7

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

DateIterator is a user iterator that returns consecutive dates in the format yyyy-mm-dd , work_days is a user assistant that calculates the value - ugh - the number of working days between two set dates. Apologies for variable names.

 function richiesta_ferie($utente, $inizio, $fine) { // INSERT INTO Ferie $data = array( 'ID_Utente' => $utente, 'Richiesta' => date('Ymd 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 8

 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 I'm using.