MySQL Query Builder: Insert, Update, Delete

- Updates
- Deletes

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries.
- It uses PDO parameter binding to protect your application against SQL injection. There is no need to clean strings passed as bindings.

First, add the DB facade in your controller:

use Illuminate\Support\Facades\DB;

Then, you may use the table() method on the DB facade to begin a query.

Inserts

To execute Inserts statements with query builder, use the insert() method. It accepts an array of column names and values.

DB::table('users')->insert(['name'=>'MarPlo', 'votes'=>0]);

- To insert several records into the table with a single call to insert(), pass an array of arrays.

DB::table('users')->insert([
 ['name'=>'MarPlo', 'votes'=>0],
 ['name'=>'PloMar', 'votes'=>0]
]);

Auto-Incrementing ID

If the table has an AUTO-INCREMENTING id, use the insertGetId() method to insert a record and then retrieve the ID.

$id = DB::table('users')->insertGetId(['name'=>'MarPlo', 'votes'=>0]);

Updates

To update records in MySQL with query builder, use the update() method. It accepts an array of column names and values.
You may also use where() clauses.

DB::table('users')->where('id', 1)->update(['votes'=>1]);

- When updating a JSON column (on databases that support JSON columns), use "->" syntax to access the appropriate key in the JSON object.

DB::table('users')->where('id', 1)->update(['options->enabled'=>true]);

Increment / Decrement

For incrementing or decrementing the value of a column, you may use the increment('col', $nr) / decrement('col', $nr) methods.
The first argument is the 'column name', the second argument is optional, a number to control the amount by which the column should be incremented or decremented.

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

- You may also specify additional columns to update during the operation.

DB::table('users')->increment('votes', 1, ['name'=>'MarPlo']);

Deletes

To delete records in MySQL with query builder, use the delete() method.
You may also use where() clauses.

DB::table('users')->where('votes', '>', 10)->delete();

- If you wish to truncate the entire table (which will remove all rows and reset the auto-incrementing ID to zero), you may use the truncate() method.

DB::table('users')->truncate();

- Documentation: Laravel - Database: Query Builder

<<-MySQL Database: Query Builder -- MySQL Database, Query Builder: ..->>

Daily Test with Code Example

HTML

CSS

JavaScript

PHP-MySQL

Which tag is used to add lists into <ul> and <ol> elements?

<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>

Which value of the "display" property creates a block box for the content and ads a bullet marker?

block list-item inline-block

.some_class {
  display: list-item;
}

Which instruction converts a JavaScript object into a JSON string.

JSON.parse() JSON.stringify eval()

var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}

Indicate the PHP class used to work with HTML and XML content in PHP.

stdClass PDO DOMDocument

$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net