Rewrite the title as: If data is already inserted or not inserted into table A in MySQL, insert an id from table A into table B

Question

I've been coding for about a year now and I'm currently working on a portfolio (a recipe website) but I'm stuck and not sure how to proceed.

My problem is that I need to find a way to insert an auto-incrementing ingredient id from table A (whether inserted or not) into table B.

When a recipe is submitted, I have code set up that uses the following MySQL query to check if there are any submitted ingredients in my ingredients table.

SELECT 
        i.ingredient
    CASE
        WHEN 
            LOWER(?) = LOWER(ingredient) 
            THEN INSERT IGNORE INTO 
                i (ingredient) VALUES ?
        ELSE 
            LOWER(?) != LOWER(ingredient)
            THEN INSERT INTO
                i VALUES ?
    END
FROM
    ingredients i

I initially tried using a post-insert trigger, but realized that this approach would not work if the ingredient already existed in the database and was not newly inserted. Therefore, I think my approach may need to be refactored.

My goal is, when submitting a recipe, to compile the ingredient into an array, using the following code:

let ingredients = req.body.ingredients;
    let ingredientArray = Array();
    ingredients.forEach(ingredient => {
        ingredientArray.push([ingredient.ingredient]);
    });

Then run the MySQL query. If the ingredient does not exist yet, insert it into table A. If it already exists, ignore it. After these two options, the ingredient's auto-incrementing id will be inserted into table B. To make the problem more complicated, recipe_id will be extracted from table C (containing various other recipe information), amount will be the value typed by the user in the input field (converted to an array), unit_id will be the one selected by the user and predefined in the database A set of units corresponding to a predefined set of measurement units (also converted to an array). These two arrays will be made into arrays using the JavaScript code above.

Table A is as follows:

id	Element
1	sugar
2	flour

Table B is as follows:

id	recipe_id	Amount	unit_id	ingredient_id
1	45	1	5	1
2	76	4	4	2

Update method

Hopefully this is a better approach, however, I'm having trouble figuring out how to make the false result of my if statement execute @checkIngredientFalse and then @checkIngredientTrue Difficult, because this will add ingredient's id to recipe_ingredients's ingredient_id table column.

recipe.js

async function createRecipeIngredient(newRecipeId, ingredientName, unitId, amount) {
    let ingredients = req.body.ingredients;
    let ingredientArray = Array();
    ingredients.forEach(ingredient => {
        ingredientArray.push([ingredient.ingredient]);
    });

const ingredientQuery = `
SET @checkIngredient = 
    (SELECT 
        i.id 
    FROM 
        ingredients i 
    WHERE 
        i.ingredient = LOWER(?)
    );

SET @newRecipeId = 
    (SELECT
        max(r.id)
    FROM
        recipes r
    );
    
SET @checkIngredientTrue = 
    (INSERT INTO
        recipeIngredients (recipe_id, ingredient_id)
    VALUES
        (@newRecipeId, @checkIngredient)
    );

SET @checkIngredientFalse = 
    (INSERT INTO 
        ingredients (ingredient) 
    VALUES 
        (LOWER(?))
    
    //MISSING CODE HERE//

    );

IF(@checkIngredient, @checkIngredientTrue, @checkIngredientFalse);`

const [_queryRows, _queryFields] = await db.promise().query(ingredientQuery, [newRecipeId, _ingredientName, _unitId, _amount]);

module.exports = { getRecipe, getRecipeComments, getRecipePhotos, getUserRecipeCommentLikes, createRecipe, insertRecipePhoto, createRecipeIngredient };

routerRecipes.js

router.post('/recipes/new', cloudinary.upload.single('photo'), async (req, res, _next) => {
    await recipeQueries.createRecipe();
    await recipeQueries.insertRecipePhoto(newRecipeId, req.user, req.file.path);
    await recipeQueries.createRecipeIngredient(newRecipeId);

    res.redirect('/recipes');
});

Answer 1

For each ingredient in the recipe:

• SELECT id FROM ingredients WHERE ingredient = ?
• If the above query is successful, ingredient_id is set to the returned id
• If unsuccessful, execute INSERT INTO ingredients (ingredient) VALUES (?) and set ingredient_id to the assigned auto-increment ID
• Use the ingredient_id value found in the above steps to INSERT INTO recipes

ingredients.forEach(async (ingredient) => {
    let ingredient_id;
    const [ing_ids] = await db.promise().query("SELECT id FROM ingredients WHERE ingredient = ?", [ingredient.toLowerCase()]);
    if (ing_ids.length > 0) {
        ingredient_id = ing_ids[0][0];
    } else {
        const res = await db.promise().query("INSERT INTO ingredients (ingredient) VALUES (?)", [ingredient]);
        ingredient_id = res.insertId;
    }
    await db.promise().query("INSERT INTO recipeIngredients (recipe_id, ingredient_id) VALUES (?, ?)", [recipe_id, ingredient_id]);
});