Re: How to store user's tastes

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 21 Apr 2019 18:28:51 GMT
Message-ID: <FILLPROC-20190421192149_at_ram.dialup.fu-berlin.de>

^Bart <gabriele1NOSPAM_at_hotmail.com> writes:
>How could I find the best way to store informations in a group named
>vegan with a lot of things?

  Main.sql

\w SET sql_mode = 'ANSI,TRADITIONAL';

DROP SCHEMA IF EXISTS Sq9i7vl$1bd4$_20190421182933; 
CREATE SCHEMA         Sq9i7vl$1bd4$_20190421182933; 
USE                   Sq9i7vl$1bd4$_20190421182933;

CREATE TABLE DIET
( DIET SERIAL PRIMARY KEY, ENGLISH VARCHAR( 50 ) NOT NULL UNIQUE ); INSERT INTO DIET( ENGLISH )VALUES( 'vegan' ); INSERT INTO DIET( ENGLISH )VALUES( 'vetarian' );

CREATE TABLE FOODSTYLES
( FOODSTYLE SERIAL PRIMARY KEY,
  DIET BIGINT UNSIGNED NOT NULL,
  INGREDIENT SMALLINT NOT NULL,
  FOREIGN KEY( DIET )REFERENCES DIET( DIET )); DELIMITER $$
DROP PROCEDURE IF EXISTS FILLPROC$$
CREATE PROCEDURE FILLPROC()
BEGIN
    DECLARE I INTEGER;
    DECLARE VEGAN_INGREDIENTS JSON;
    SET I = 0;
    SET VEGAN_INGREDIENTS = JSON_ARRAY( 4, 2, 8, 9, 3 );     WHILE I < JSON_LENGTH( VEGAN_INGREDIENTS )DO

        INSERT INTO FOODSTYLES( DIET, INGREDIENT )
        VALUES
        ( 1, JSON_EXTRACT( VEGAN_INGREDIENTS, CONCAT( '$[', I, ']' )));
        SET I = I + 1; 

    END WHILE;
END$$
DELIMITER ;
CALL FILLPROC; SELECT FOODSTYLE, ENGLISH, INGREDIENT FROM FOODSTYLES INNER JOIN DIET USING( DIET );   transcript

+-----------+---------+------------+
| FOODSTYLE | ENGLISH | INGREDIENT |
+-----------+---------+------------+

[Quoted] |         1 | vegan   |          4 |
|         2 | vegan   |          2 |
|         3 | vegan   |          8 |
|         4 | vegan   |          9 |
|         5 | vegan   |          3 |

+-----------+---------+------------+ Received on Sun Apr 21 2019 - 20:28:51 CEST