How to insert select update and delete data in magento2

I have created a new table called ‘student’ in the Magento database. This table includes fields like student_id, name, age, address, and status. The goal is to perform MySQL operations on this table through a module called ‘Jrb_CrudOperation,’ covering INSERT, UPDATE, DELETE, and SELECT operations. I will try to explain How to insert select update and delete data in magento2 one by one.

INSERT:

To insert data, I gather values for title, content, and status, storing them in an array named ‘data.’ Utilizing the Magento structure, I set these values into my ‘student’ model and execute the save() method to perform the MySQL insert operation. Below code is just a simple insert operation with simple model which is not a optimized way. for better optimized way inserting multiple record please follow the article Insert Multiple Records into Magento 2 Database Tables

$data = array(
    array(
        "name" => 'Jhon Boss',
        "age" => "14",
        "address" => "Niali, Cuttack",
        "status" => 0
    ),
    array(
        "name" => 'Ryan Biswal',
        "age" => "15",
        "address" => "Niali, Cuttack",
        "status" => 1
    ),
    array(
        "name" => 'Raj Nayak',
        "age" => "14",
        "address" => "Firozabad, Delhi",
        "status" => 0
    ),
    array(
        "name" => 'Jack Aryan',
        "age" => "16",
        "address" => "Parel, Mumbai",
        "status" => 1
    )
);

try{
    foreach($data as $_data){
        $studentModel = $this->studentFactory->create();
        $studentModel->addData($_data);
        $saveData = $studentModel->save();
        if($saveData){
            echo $saveData->getId().' inserted successfully !'.PHP_EOL;
        }
    }
} catch (\Exception $e){
    print_r($e);
}

SELECT:

For select operations, I use the getCollection() method to obtain a collection object. Iterating through a foreach loop allows retrieval of all data, and specific data can be accessed using methods like getName(), getAddress(), getStatus(), or getStudentId() etc..

$student = $this->studentFactory->create();
$studentCollection = $student->getCollection();
foreach($studentCollection as $_studentCollection){
    print_r($_studentCollection->getData());
}

UPDATE:

To update data, I fetch the id from the request, update values in the ‘data’ array, load the model, and call the save() method. Alternatively, for a single data update, I directly load the model, modify the data, and save it.

$id = 21;
$data = array(
    "name" => 'Jhon Boss',
    "age" => "17",
    "address" => "Saltlake, Kolkata",
    "status" => 1
);

$student = $this->studentFactory->create();
$studentModel = $student->load($id);
$studentModel->addData($data)->save();

DELETE:

For deletion, I retrieve the id from the request and use the delete() method on the ‘student’ model.

$id = 21;
$student = $this->studentFactory->create();
$student->load($id)->delete();

Complete code will be like below.

<?php
/**
 * Copyright © www.jyotiranjan.in All rights reserved.
 * See COPYING.txt for license details.
 */
declare(strict_types=1);

namespace Jrb\CrudOperation\Model;

use Magento\Framework\Model\AbstractModel;

class StudentScript extends AbstractModel
{
    /**
     * studentFactory
     *
     * @var \Jrb\CrudOperation\Model\StudentFactory
     */
    protected $studentFactory;

    /**
     * Constructor
     * 
     * @param \Jrb\CrudOperation\Model\StudentFactory $studentFactory
     */
    public function __construct(
        \Jrb\CrudOperation\Model\StudentFactory $studentFactory
    )
    {
        $this->studentFactory = $studentFactory;
    }

    /**
     * Execute the custom script
     *
     * @return void
     */
    public function execute()
    {
        // inser query for inserting single/multiple data
        $data = array(
            array(
                "name" => 'Jhon Boss',
                "age" => "14",
                "address" => "Niali, Cuttack",
                "status" => 0
            ),
            array(
                "name" => 'Ryan Biswal',
                "age" => "15",
                "address" => "Niali, Cuttack",
                "status" => 1
            ),
            array(
                "name" => 'Raj Nayak',
                "age" => "14",
                "address" => "Firozabad, Delhi",
                "status" => 0
            ),
            array(
                "name" => 'Jack Aryan',
                "age" => "16",
                "address" => "Parel, Mumbai",
                "status" => 1
            )
        );
        $this->insertDummyData($data);

        //selct query for getting collection
        $this->selectDummyData();

        $id = 21;
        $data = array(
            "name" => 'Jhon Boss',
            "age" => "17",
            "address" => "Saltlake, Kolkata",
            "status" => 1
        );
        $this->updateDummyData($id, $data);

        $id = 22;
        $this->deleteDummyData($id);
    }

    public function insertDummyData($data){
        try{
            foreach($data as $_data){
                $studentModel = $this->studentFactory->create();
                $studentModel->addData($_data);
                $saveData = $studentModel->save();
                if($saveData){
                    echo $saveData->getId().' inserted successfully !'.PHP_EOL;
                }
            }
        } catch (\Exception $e){
            print_r($e);
        }
    }

    public function selectDummyData(){
        $student = $this->studentFactory->create();
        $studentCollection = $student->getCollection();
        foreach($studentCollection as $_studentCollection){
            print_r($_studentCollection->getData());
        }
    }

    public function updateDummyData($id, $data){
        $student = $this->studentFactory->create();
        $studentModel = $student->load($id);
        $studentModel->addData($data)->save();
    }

    public function deleteDummyData($id){
        $student = $this->studentFactory->create();
        $student->load($id)->delete();
    }
}

If you discovered value in this article, don’t hesitate to pass it along to others who might find it beneficial. you can get complete module from here