How to Read Database Columns Into Html Dropdown Field in Python
Introduction
MySQL version five.vii.eight introduces a JSON data type that allows you to access information in JSON documents.
SQL databases tend to be rigid in design. By its nature, the structured query language enforces information type and size constraints.
In comparison, NoSQL databases encourage flexibility in blueprint. In these schema-less databases, in that location is no imposed structural restriction, just information to be saved.
The JSON information type in MySQL grants you the strengths of both of these systems. It allows you to structure some parts of your database and leave others to be flexible.
The first half of this commodity will design a database with JSON fields. It will pace through using the built-in functions available to MySQL to create, read, update, and delete rows.
The second half of this article volition utilize the Eloquent ORM with Laravel to communicate with the database. You will build an admin panel that supports displaying products, adding new products, modifying existing products, and deleting products.
Prerequisites
If you would like to follow forth with this article, y'all will need:
- MySQL 5.7.8 or afterwards and PHP vii.3.24 or afterwards. You lot tin can consult our tutorials on installing Linux, Apache, MySQL, and PHP
- Some familiarity with SQL queries.
- Some familiarity with writing PHP.
- Some familiarity with Laravel.
- This tutorial utilizes Laravel installation via Composer in mind. You can consult our tutorial on installing Composer.
Notation: Laravel now provides a tool called Sail to work with Docker that will configure an surround with MySQL, PHP, and Composer.
This may be an culling option if y'all are having difficulty setting up your local environment.
This tutorial was verified with MySQL v8.0.23, PHP v7.three.24, Composer v2.0.ix, and Laravel v8.26.1.
Stride 1 — Defining the Schema
For the purposes of this tutorial, y'all will be building from a schema that defines the inventory of an online store that sells a diverseness of electronics.
Traditionally, the Entity–aspect–value model (EAV) pattern would be used to allow customers to compare the features of products.
Yet, with the JSON data type, this apply case can be approached differently.
The database will be named e_store and take iii tables named brands, categories, and products respectively.
Create the e_store database:
CREATE DATABASE IF NOT EXISTS `e_store` DEFAULT CHARACTER Ready utf8 DEFAULT COLLATE utf8_general_ci; SET default_storage_engine = INNODB ; The brands and categories tables will each have an id and a name field.
Create the brands table:
CREATE Table `e_store` . `brands` ( `id` INT UNSIGNED Not NULL auto_increment , `name` VARCHAR ( 250 ) NOT NULL , Chief Fundamental ( `id` ) ) ; Create the categories table:
CREATE TABLE `e_store` . `categories` ( `id` INT UNSIGNED Non NULL auto_increment , `proper noun` VARCHAR ( 250 ) NOT NULL , Principal Key ( `id` ) ) ; Side by side, add some sample brands:
INSERT INTO `e_store` . `brands` ( `name` ) VALUES ( 'Samsung' ) ; INSERT INTO `e_store` . `brands` ( `name` ) VALUES ( 'Nokia' ) ; INSERT INTO `e_store` . `brands` ( `proper name` ) VALUES ( 'Catechism' ) ; Then, add some categories:
INSERT INTO `e_store` . `categories` ( `name` ) VALUES ( 'Television' ) ; INSERT INTO `e_store` . `categories` ( `name` ) VALUES ( 'Mobile Phone' ) ; INSERT INTO `e_store` . `categories` ( `proper noun` ) VALUES ( 'Camera' ) ; Next, create a products tabular array with the id, name, brand_id, category_id, and attributes fields:
CREATE Table `e_store` . `products` ( `id` INT UNSIGNED Not Zip AUTO_INCREMENT , `name` VARCHAR ( 250 ) NOT Nix , `brand_id` INT UNSIGNED Non NULL , `category_id` INT UNSIGNED NOT NULL , `attributes` JSON Not NULL , Chief KEY ( `id` ) , Index `CATEGORY_ID` ( `category_id` ASC ) , Index `BRAND_ID` ( `brand_id` ASC ) , CONSTRAINT `brand_id` Strange Fundamental ( `brand_id` ) REFERENCES `e_store` . `brands` ( `id` ) ON DELETE RESTRICT ON UPDATE Pour , CONSTRAINT `category_id` FOREIGN Primal ( `category_id` ) REFERENCES `e_store` . `categories` ( `id` ) ON DELETE RESTRICT ON UPDATE CASCADE ) ; This tabular array definition specifies foreign key constraints for the brand_id and category_id fields, specifying that they reference the brands and categories table respectively. This table definition too specifies that the referenced rows should not exist allowed to delete and if updated, the changes should reverberate in the references likewise.
The attributes field'south column type has been declared to be JSON which is the native information type now bachelor in MySQL. This allows you to use the various JSON related constructs in MySQL on the attributes field.
Here is an entity relationship diagram of the created database:
This database design is not the best in terms of efficiency and accurateness. There are some common real-world use cases that are not accounted for. For example, there is no price cavalcade in the products table and there is no support for a product being in multiple categories. Nevertheless, the purpose of this tutorial is not to teach database design but rather how to model objects of different nature in a unmarried tabular array using MySQL'due south JSON features.
Stride 2 — Creating Data in the JSON Field
At present, you are going to create products to add to the database using INSERT INTO and VALUES.
Hither are some example televisions with information on screen size, resolution, ports, and speakers using stringified JSON objects:
INSERT INTO `e_store` . `products` ( `proper name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Prime' , '1' , '1' , '{"screen": "l inch", "resolution": "2048 ten 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}' ) ; INSERT INTO `e_store` . `products` ( `proper noun` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Octoview' , 'one' , 'ane' , '{"screen": "forty inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "correct": "10 watt"}}' ) ; INSERT INTO `e_store` . `products` ( `proper name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Dreamer' , 'one' , '1' , '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": one, "usb": 1}, "speakers": {"left": "10 watt", "right": "ten watt"}}' ) ; INSERT INTO `e_store` . `products` ( `proper name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Bravia' , 'i' , '1' , '{"screen": "25 inch", "resolution": "1366 10 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}' ) ; INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Proton' , '1' , 'i' , '{"screen": "xx inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}' ) ; This example declares five different television set products.
Alternatively, you could utilise the congenital-in JSON_OBJECT part to create JSON objects.
The JSON_OBJECT function accepts a list of cardinal/value pairs in the form JSON_OBJECT(key1, value1, key2, value2, ... cardinal(n), value(n)) and returns a JSON object.
Here are some example mobile phones using the JSON_OBJECT function:
INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Desire' , '2' , 'ii' , JSON_OBJECT( "network" , JSON_ARRAY( "GSM" , "CDMA" , "HSPA" , "EVDO" ) , "torso" , "5.11 x 2.59 ten 0.46 inches" , "weight" , "143 grams" , "sim" , "Micro-SIM" , "display" , "iv.5 inches" , "resolution" , "720 x 1280 pixels" , "os" , "Android Jellybean v4.3" ) ) ; INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Passion' , '2' , 'ii' , JSON_OBJECT( "network" , JSON_ARRAY( "GSM" , "CDMA" , "HSPA" ) , "body" , "6.xi x iii.59 x 0.46 inches" , "weight" , "145 grams" , "sim" , "Micro-SIM" , "display" , "four.5 inches" , "resolution" , "720 x 1280 pixels" , "os" , "Android Jellybean v4.3" ) ) ; INSERT INTO `e_store` . `products` ( `proper name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Emotion' , '2' , '2' , JSON_OBJECT( "network" , JSON_ARRAY( "GSM" , "CDMA" , "EVDO" ) , "body" , "5.50 x 2.50 x 0.fifty inches" , "weight" , "125 grams" , "sim" , "Micro-SIM" , "display" , "5.00 inches" , "resolution" , "720 x 1280 pixels" , "bone" , "Android KitKat v4.3" ) ) ; INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Sensation' , '2' , '2' , JSON_OBJECT( "network" , JSON_ARRAY( "GSM" , "HSPA" , "EVDO" ) , "body" , "four.00 x two.00 ten 0.75 inches" , "weight" , "150 grams" , "sim" , "Micro-SIM" , "display" , "3.5 inches" , "resolution" , "720 x 1280 pixels" , "os" , "Android Lollipop v4.3" ) ) ; INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Joy' , '2' , '2' , JSON_OBJECT( "network" , JSON_ARRAY( "CDMA" , "HSPA" , "EVDO" ) , "body" , "seven.00 x 3.l ten 0.25 inches" , "weight" , "250 grams" , "sim" , "Micro-SIM" , "display" , "6.5 inches" , "resolution" , "1920 x 1080 pixels" , "os" , "Android Marshmallow v4.3" ) ) ; This example declares 5 different mobile phone products.
It also utilized the JSON_ARRAY office which returns a JSON assortment when passed a set of values.
If yous specify a single cardinal multiple times, only the commencement primal/value pair will exist retained. This is called normalizing the JSON in MySQL's terms. Also, as part of normalization, the object keys are sorted and the extra white-space between primal/value pairs is removed.
Additionally, you could utilize the congenital-in JSON_MERGE_PRESERVE or JSON_MERGE_PATCH functions to create JSON objects.
Annotation: In previous versions of MySQL, you could utilise JSON_MERGE, but this office has been deprecated.
'JSON_MERGE' is deprecated and volition be removed in a futurity release. Delight apply JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead For the purpose of this tutorial, you volition use the JSON_MERGE_PRESERVE function. This part takes multiple JSON objects and produces a single, aggregate object.
Here are some example cameras using the JSON_MERGE_PRESERVE function:
INSERT INTO `e_store` . `products` ( `proper name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Explorer' , '3' , '3' , JSON_MERGE_PRESERVE( '{"sensor_type": "CMOS"}' , '{"processor": "Digic DV III"}' , '{"scanning_system": "progressive"}' , '{"mount_type": "PL"}' , '{"monitor_type": "LCD"}' ) ) ; INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Runner' , '3' , 'three' , JSON_MERGE_PRESERVE( JSON_OBJECT( "sensor_type" , "CMOS" ) , JSON_OBJECT( "processor" , "Digic DV Ii" ) , JSON_OBJECT( "scanning_system" , "progressive" ) , JSON_OBJECT( "mount_type" , "PL" ) , JSON_OBJECT( "monitor_type" , "LED" ) ) ) ; INSERT INTO `e_store` . `products` ( `proper name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Traveler' , '3' , '3' , JSON_MERGE_PRESERVE( JSON_OBJECT( "sensor_type" , "CMOS" ) , '{"processor": "Digic DV Two"}' , '{"scanning_system": "progressive"}' , '{"mount_type": "PL"}' , '{"monitor_type": "LCD"}' ) ) ; INSERT INTO `e_store` . `products` ( `name` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Walker' , '3' , '3' , JSON_MERGE_PRESERVE( '{"sensor_type": "CMOS"}' , '{"processor": "Digic DV I"}' , '{"scanning_system": "progressive"}' , '{"mount_type": "PL"}' , '{"monitor_type": "LED"}' ) ) ; INSERT INTO `e_store` . `products` ( `proper noun` , `brand_id` , `category_id` , `attributes` ) VALUES ( 'Jumper' , 'iii' , '3' , JSON_MERGE_PRESERVE( '{"sensor_type": "CMOS"}' , '{"processor": "Digic DV I"}' , '{"scanning_system": "progressive"}' , '{"mount_type": "PL"}' , '{"monitor_type": "LCD"}' ) ) ; This example declares five dissimilar camera products.
Observe that only objects are passed to the JSON_MERGE_PRESERVE function. Some of them have been constructed using the JSON_OBJECT role. Others have been passed as valid JSON strings.
In the instance of the JSON_MERGE_PRESERVE function, if a key is repeated multiple times, its value is retained every bit an array in the output.
For example, hither is a drove of JSON objects with the same network primal:
SELECT JSON_MERGE_PRESERVE( '{"network": "GSM"}' , '{"network": "CDMA"}' , '{"network": "HSPA"}' , '{"network": "EVDO"}' ) ; This will produce an array of values:
Output
{"network": ["GSM", "CDMA", "HSPA", "EVDO"]} At present, at this signal, you can verify your queries past using the JSON_TYPE function to display the field value type:
SELECT JSON_TYPE(attributes) FROM `e_store` . `products` ; This query volition produce 15 OBJECT results to represent all of the products - v televisions, five mobile phones, and five cameras.
At present, you can create data in the JSON field.
Footstep 3 — Reading the Data from the JSON Field
Now that you accept some products in the database to piece of work with, y'all can experiment with reading the data.
For typical MySQL values that are not of blazon JSON, y'all would usually rely upon a WHERE clause. Heuristically, when working with JSON columns, this does not piece of work.
When you wish to select rows using a JSON field, you should be familiar with the concept of a path expression. Path expressions use a dollar sign symbol ($) and the target object keys.
When used in combination with the JSON_EXTRACT function, you can recall the values for the specified cavalcade.
Consider a scenario where you are interested in all of the televisions that have at least one USB and 1 HDMI port:
SELECT * FROM `e_store` . `products` WHERE `category_id` = 1 AND JSON_EXTRACT( `attributes` , '$.ports.usb' ) > 0 AND JSON_EXTRACT( `attributes` , '$.ports.hdmi' ) > 0 ; The offset statement to the JSON_EXTRACT function is the JSON to use the path expression to which is the attributes cavalcade. The $ symbol tokenizes the object to piece of work with. The $.ports.usb and $.ports.hdmi path expressions translate to "have the usb cardinal under ports" and "take the hdmi cardinal under ports" respectively.
Once you accept extracted the keys you are interested in, you can employ the MySQL operators such equally the greater than symbol (>) on them.
This query volition produce 3 results:
These three televisions have at least one USB port and ane HDMI port. The "Bravia" and "Proton" models practice not meet these conditions.
Alternatively, the JSON_EXTRACT function has the alias -> that you lot can apply to make your queries more readable.
Revise the previous query to utilize the -> allonym:
SELECT * FROM `e_store` . `products` WHERE `category_id` = 1 AND `attributes` - > '$.ports.usb' > 0 AND `attributes` - > '$.ports.hdmi' > 0 ; Now, you can read data from the JSON field.
Stride iv — Updating Information in the JSON Field
You can update data in JSON fields with the JSON_INSERT, JSON_REPLACE, and JSON_SET functions. These functions also crave a path expression to specify which parts of the JSON object to change. The output of these functions is a valid JSON object with the changes applied.
First, update JSON fields with JSON_INSERT to add together a new chipset key with the value "Qualcomm" for all mobile phones:
UPDATE `e_store` . `products` SET `attributes` = JSON_INSERT( `attributes` , '$.chipset' , 'Qualcomm' ) WHERE `category_id` = two ; The $.chipset path expression identifies the position of the chipset property to be at the root of the object.
Examine the updated mobile phone category with the following query:
SELECT * FROM `e_store` . `products` WHERE `category_id` = 2 "Qualcomm" is now present for all mobile phones:
Now, update JSON fields with JSON_REPLACE to alter the existing chipset primal with the value "Qualcomm Snapsdragon" for all mobile phones:
UPDATE `e_store` . `products` SET `attributes` = JSON_REPLACE( `attributes` , '$.chipset' , 'Qualcomm Snapdragon' ) WHERE `category_id` = 2 ; "Qualcomm" is now replaced with "Qualcomm Snapdragon" for all mobile phones:
Lastly, update JSON fields with JSON_SET to add a new body_color central with the value "red" for all televisions:
UPDATE `e_store` . `products` SET `attributes` = JSON_SET( `attributes` , '$.body_color' , 'cerise' ) WHERE `category_id` = 1 ; "red" color is at present applied to all televisions:
All of these functions seem identical but there is a departure in the mode they deport.
The JSON_INSERT role will only add together the property to the object if information technology does not exists already.
The JSON_REPLACE role substitutes the property just if it is plant.
The JSON_SET role will add together the property if it is not found else replace it.
At present, you can update data from the JSON field.
Step five — Deleting Data from the JSON Field
You can delete data in JSON fields with the JSON_REMOVE role and DELETE.
JSON_REMOVE allows you to delete a certain primal/value from your JSON columns.
Using JSON_REMOVE office, information technology is possible to remove the mount_type fundamental/value pairs from all cameras:
UPDATE `e_store` . `products` Set up `attributes` = JSON_REMOVE( `attributes` , '$.mount_type' ) WHERE `category_id` = three ; The JSON_REMOVE function returns the updated JSON later on removing the specified key based on the path expression.
Alternatively, yous can DELETE unabridged rows using a JSON column.
Using DELETE and JSON_EXTRACT and Similar, information technology is possible to remove all the mobile phones that have the "Jellybean" version of the Android operating arrangement:
DELETE FROM `e_store` . `products` WHERE `category_id` = two AND JSON_EXTRACT( `attributes` , '$.os' ) LIKE '%Jellybean%' ; This query will remove the "Desire" and "Passion" models of mobile phones.
Working with a specific aspect requires the use of the JSON_EXTRACT function. First, the os property of mobile phones is extracted. And so the LIKE operator is applied to DELETE all records that incorporate the string Jellybean.
Now, you tin delete information from the JSON field.
Step 6 — Creating the Migrations
Now, create a new Laravel project.
Warning: This web application is for tutorial purposes simply and should not be used in a production setting.
Open your final window and run the following command:
- composer create-projection laravel/laravel estore-case
Navigate to the newly created projection directory:
- cd estore-example
Configure your Laravel awarding to use a MySQL database.
You may need to alter your .env file to set the DB_DATABASE, DB_USERNAME, and DB_PASSWORD.
Yous are going to create three migrations for brands, categories, and products respectively.
Brand a create_brands migration:
- php artisan make:migration create_brands
Modify the create_brands.php migration with the post-obit lines of code:
database/migrations/(...)create_brands.php
<?php use Illuminate\Database\Migrations\Migration ; use Illuminate\Database\Schema\Design ; utilise Illuminate\Back up\Facades\Schema ; course CreateBrands extends Migration { /** * Run the migrations. * * @return void */ public function up ( ) { Schema :: create ( 'brands' , function ( Pattern $table ) { $table -> engine = 'InnoDB' ; $table -> increments ( 'id' ) ; $table -> cord ( 'name' ) ; $table -> timestamps ( ) ; } ) ; } /** * Reverse the migrations. * * @return void */ public part down ( ) { Schema :: dropIfExists ( 'brands' ) ; } } Make a create_categories migration:
- php artisan make:migration create_categories
Modify the create_categories.php migration with the following lines of code:
database/migrations/(...)create_categories.php
<?php utilise Illuminate\Database\Migrations\Migration ; utilise Illuminate\Database\Schema\Blueprint ; use Illuminate\Support\Facades\Schema ; class CreateCategories extends Migration { /** * Run the migrations. * * @return void */ public function upwards ( ) { Schema :: create ( 'categories' , office ( Pattern $table ) { $tabular array -> engine = 'InnoDB' ; $table -> increments ( 'id' ) ; $table -> cord ( 'name' ) ; $table -> timestamps ( ) ; } ) ; } /** * Reverse the migrations. * * @return void */ public part downwards ( ) { Schema :: dropIfExists ( 'categories' ) ; } } The create_products migration will also have the directives for indexes and strange keys:
- php artisan make:migration create_products
Change the create_products.php migration with the post-obit lines of lawmaking:
database/migrations/(...)create_products.php
<?php use Illuminate\Database\Migrations\Migration ; apply Illuminate\Database\Schema\Blueprint ; use Illuminate\Back up\Facades\Schema ; class CreateProducts extends Migration { /** * Run the migrations. * * @return void */ public function upward ( ) { Schema :: create ( 'products' , role ( Design $table ) { $table -> engine = 'InnoDB' ; $table -> increments ( 'id' ) ; $table -> string ( 'name' ) ; $table -> unsignedInteger ( 'brand_id' ) ; $table -> unsignedInteger ( 'category_id' ) ; $tabular array -> json ( 'attributes' ) ; $table -> timestamps ( ) ; // strange key constraints $tabular array -> foreign ( 'brand_id' ) -> references ( 'id' ) -> on ( 'brands' ) -> onDelete ( 'restrict' ) -> onUpdate ( 'pour' ) ; $table -> foreign ( 'category_id' ) -> references ( 'id' ) -> on ( 'categories' ) -> onDelete ( 'restrict' ) -> onUpdate ( 'pour' ) ; // indexes $table -> alphabetize ( 'brand_id' ) ; $table -> index ( 'category_id' ) ; } ) ; } /** * Reverse the migrations. * * @return void */ public function downward ( ) { Schema :: dropIfExists ( 'products' ) ; } } Pay attention to the $table->json('attributes'); argument in the migration.
Note: This will only work for database engines that support the JSON data type.
Engines, such as older versions of MySQL will non be able to carry out these migrations.
Like to creating other types of table fields using the appropriate data type named method, yous have created a JSON column using the json method with the name attributes.
Step vii — Creating the Models
You are going to create three models for brands, categories, and products respectively.
Create a Brand model:
- php artisan make:model Brand
Modify the Make.php file with the following lines of code:
app/Models/Brand.php
<?php namespace App\Models ; use Illuminate\Database\Eloquent\Factories\HasFactory ; use Illuminate\Database\Eloquent\Model ; class Brand extends Model { employ HasFactory ; // A brand has many products public office products ( ) { return $this -> hasMany ( 'Product' ) } } Create a Category model:
- php artisan make:model Category
Change the Category.php file with the post-obit lines of code:
app/Models/Category.php
<?php namespace App\Models ; use Illuminate\Database\Eloquent\Factories\HasFactory ; use Illuminate\Database\Eloquent\Model ; class Category extends Model { // A category has many products public function products ( ) { return $this -> hasMany ( 'Production' ) } } Create a Product model:
- php artisan brand:model Production
Modify the Product.php file with the following lines of lawmaking:
app/Models/Product.php
<?php namespace App\Models ; use Illuminate\Database\Eloquent\Factories\HasFactory ; use Illuminate\Database\Eloquent\Model ; class Product extends Model { use HasFactory ; public $timestamps = fake ; // Cast attributes JSON to assortment protected $casts = [ 'attributes' => 'assortment' ] ; // Each product has a make public role brand ( ) { return $this -> belongsTo ( 'Brand' ) ; } // Each product has a category public function category ( ) { return $this -> belongsTo ( 'Category' ) ; } } The $casts array which has the key attributes set to array makes sure whenever a product is fetched from the database, its attributes JSON is converted to an associated assortment. This allows you to update records from your controller actions.
Step eight — Creating a Product
The focus of the remainder of this tutorial will be on the camera production category.
Y'all will be edifice a view with a form that has fields that are specific for cameras. For brevity, the goggle box and mobile phone production categories will not be covered - but would exist very like in blueprint.
Create the controller for the camera product category:
- php artisan brand:controller CameraController
Modify the CameraController.php with the post-obit lines of lawmaking:
app/Http/Controller/CameraController.php
<?php namespace App\Http\Controllers ; use Illuminate\Http\Request ; class CameraController extends Controller { // creates product in database // using form fields public part shop ( Request $request ) { // create object and gear up backdrop $photographic camera = new \App\Models\Production ( ) ; $photographic camera -> name = $request -> proper name ; $camera -> brand_id = $request -> brand_id ; $camera -> category_id = $asking -> category_id ; $photographic camera -> attributes = [ 'processor' => $asking -> processor , 'sensor_type' => $request -> sensor_type , 'monitor_type' => $asking -> monitor_type , 'scanning_system' => $request -> scanning_system , ] ; // relieve to database $camera -> save ( ) ; // show the created photographic camera return view ( 'product.camera.show' , [ 'photographic camera' => $camera ] ) ; } } This completes the store function for cameras.
Create a view past making a new.blade.php file in the resources/views/product/camera directory tree:
resources/views/product/camera/new.blade.php
<form method = "Mail" activity = "/product/photographic camera/store" > @csrf <tabular array > <tr > <td > <label for = "name" > Name </characterization > </td > <td > <input id = "proper name" proper noun = "name" type = "text" > </td > </tr > <tr > <td > <label for = "brand-id" > Brand ID </label > </td > <td > <select id = "make-id" proper noun = "brand_id" > <option value = "1" > Samsung </pick > <choice value = "2" > Nokia </option > <pick value = "iii" > Canon </option > </select > </td > </tr > <tr > <td > <label for = "attributes-processor" > Processor </label > </td > <td > <input id = "attributes-processor" name = "processor" type = "text" > </td > </tr > <tr > <td > <characterization for = "attributes-sensor-type" > Sensor Type </label > </td > <td > <input id = "attributes-sensor-blazon" proper noun = "sensor_type" type = "text" > </td > </tr > <tr > <td > <label for = "attributes-monitor-type" > Monitor Type </characterization > </td > <td > <input id = "attributes-monitor-type" name = "monitor_type" type = "text" > </td > </tr > <tr > <td > <label for = "attributes-scanning-system" > Scanning System </label > </td > <td > <input id = "attributes-scanning-system" name = "scanning_system" type = "text" > </td > </tr > </table > <input name = "category_id" value = "3" type = "hidden" > <button type = "submit" > Submit </button > </form > The brand_id is presented every bit a hardcoded select element with the 3 brands that were created earlier as picks. The category_id is presented as a hardcoded subconscious input value set to the id for cameras.
Modify the routes in routes/web.php to display the cameras:
routes/web.php
// ... use App\Http\Controllers\CameraController ; Route :: go ( '/product/camera/new' , function ( ) { render view ( 'product/photographic camera/new' ) ; } ) ; Route :: mail ( '/product/camera/shop' , [ CameraController :: form , 'shop' ] ) ; Serve the awarding with the following command:
- php artisan serve
Then, visit localhost:8000/product/camera/new) with your web browser. Information technology will display a course for adding a new camera.
Footstep nine — Fetching Products
The $casts array that was alleged earlier in the Production model volition assistance yous read and edit a production by treating attributes every bit an associative array.
Change the CamerasController with the post-obit lines of code:
app/Http/Controller/CameraController.php
<?php // ... class CameraController extends Controller { // ... store ... // fetches a single product // from database public office evidence ( $id ) { $camera = \App\Models\Production :: find ( $id ) ; return view ( 'product.camera.show' , [ 'camera' => $photographic camera ] ) ; } } This completes the prove function for cameras.
Create a view by making a show.blade.php file in the resources/views/production/camera directory tree:
resources/views/product/photographic camera/show.blade.php
<table > <tr > <td > Name </td > <td > {{ $photographic camera->proper noun }} </td > </tr > <tr > <td > Brand ID </td > <td > {{ $camera->brand_id }} </td > </tr > <tr > <td > Category ID </td > <td > {{ $camera->category_id }} </td > </tr > <tr > <td > Processor </td > <td > {{ $camera->attributes['processor'] }} </td > </tr > <tr > <td > Sensor Type </td > <td > {{ $photographic camera->attributes['sensor_type'] }} </td > </tr > <tr > <td > Monitor Type </td > <td > {{ $camera->attributes['monitor_type'] }} </td > </tr > <tr > <td > Scanning System </td > <td > {{ $camera->attributes['scanning_system'] }} </td > </tr > </table > Alter the routes in routes/web.php to display the cameras:
routes/web.php
// ... Route :: get ( '/product/photographic camera/prove/{id}' , [ CameraController :: class , 'show' ] ) ; Serve the application with the post-obit command:
- php artisan serve
Then, visit a valid id for a camera product (e.g., localhost:8000/product/camera/show/xi) with your web browser. It will display a table of the photographic camera information for the production with an id of "11".
Step 10 — Editing a Product
By using a combination of the techniques for store and bear witness, yous can create a view to edit an existing product.
You tin create a class similar to the 1 in new.blade.php. And so prepopulate it with a product variable similiar to the one used in testify.bract.php:
<tr> <td> <label for = "attributes-processor" >Processor< /characterization> < /td> <td> <input id= "attributes-processor" proper name= "processor" type= "text" value= "{{ $camera -> attributes ['processor'] }}" > < /td> < /tr> Now, the course displays the existing values, making information technology easier for users to run across what needs updating.
Showtime, the id is used to retrieve the model. Next, the values from the request are applied. Lastly, the new values are saved to the database.
Step eleven — Searching Based on JSON Attributes
You tin besides query JSON columns using the Eloquent ORM.
Consider a search page that allows users to search for cameras based upon attributes that they are interested in.
public part search ( Request $request ) { $cameras = \App\Models\Product :: where ( [ [ 'attributes->processor' , 'like' , $request -> processor ] , [ 'attributes->sensor_type' , 'like' , $asking -> sensor_type ] , [ 'attributes->monitor_type' , 'similar' , $asking -> monitor_type ] , [ 'attributes->scanning_system' , 'like' , $request -> scanning_system ] ] ) -> get ( ) ; return view ( 'production.camera.search' , [ 'cameras' => $cameras ] ) ; } The retrieved records will now exist available to the production.camera.search view as a $cameras collection. This will allow you to loop through the results and brandish the cameras that satisfy the weather condition from the user'due south search request.
Step 12 — Deleting a Product
Using a non-JSON column attribute, you can delete products by specifying a where clause and then calling the delete method.
For example, in the instance of an id.
\App\Models\Product :: where ( 'id' , $id ) -> delete ( ) ; For JSON columns, specify a where clause using a unmarried or multiple attributes then call the delete method.
\App\Models\Production :: where ( 'attributes->sensor_type' , 'CMOS' ) -> delete ( ) ; } In this example, this lawmaking will remove all products that have a sensor_type aspect set to "CMOS".
Conclusion
In this commodity, yous designed a MySQL database with the JSON data blazon and connected to information technology with a Laravel web awarding.
Whenever you demand to save data equally key/value pairs in a carve up table or piece of work with flexible attributes for an entity, yous should consider using a JSON data type field instead as information technology can heavily contribute to compressing your database design.
If you are interested in diving deeper, the MySQL documentation is a great resource to explore JSON concepts further.
For more than information on Laravel, you can consult our tech talk on Getting Started with Laravel.
Source: https://www.digitalocean.com/community/tutorials/working-with-json-in-mysql
0 Response to "How to Read Database Columns Into Html Dropdown Field in Python"
Post a Comment