How to fix insert error in table genres?

Asked

Viewed 89 times

-1

How can I fix the code below?

It takes the spaces between the commas of the genres or if I have these genres "action, adventure, romance" it puts so "action, adventure, romance" right? Only if I do the reverse order (like: "romance, adventure, action") or place with more spaces between the commas in an altered or equal way (plus the less so: "action,adventure, romance" or "action, romance, adventure") keeps giving error of inserting the same item in Mysql table genres.

How can I do in case no matter the order I put the genders or spaces between commas make the single addition of a single gender and if you have already registered in Mysql do not add it again.

How can I fix this?

<?php $generoMinusculo = strtolower(trim($_POST["genero"]));
    if ($generoMinusculo) { 
    $generoMinusculo = str_replace(' ,',',',$generoMinusculo);  
    $generoMinusculo = str_replace(' , ',',',$generoMinusculo); 
    $generoMinusculo = str_replace('  ,  ',',',$generoMinusculo);   
    $generoMinusculo = str_replace(' ,  ',',',$generoMinusculo);    
    $generoMinusculo = str_replace('  , ',',',$generoMinusculo);    
    $generos = explode("," , $generoMinusculo); 

foreach ($generos as $item) {
    $Generos = mysql_query("SELECT * FROM `generos`  WHERE `genero`='".$item."'");
    $verificar = mysql_fetch_array($Generos);
    $verificarGenero =  $verificar["genero"];
    $generoPostado = $item; 
    if ($generoPostado != $verificarGenero){
    mysql_query("INSERT INTO `generos` SET `genero`='".$item."'");
    }elseif ($generoPostado == $verificarGenero){ }
    else { } } ?>
php mysql

  • If possible, add the current table data to the question to see how it is.

  • If you have recorded romance, aventura, ação and will give an insight into aventura, you will need to select this table and pass the data to an array with explode. Then just check if aventura is in the array to insert or not.

1 answer

3

First, we can simplify your handling of the input. It is always recommended to clear the input to prevent injections:

$generoMinusculo = strtolower(trim($_POST["genero"]));
if ($generoMinusculo) { 

    // Use array_map aplicar as funções de limpeza em todos os itens  
    $generos = array_map('mysql_real_escape_string', array_map('trim', explode("," , $generoMinusculo))); 

    foreach ($generos as $item) {
        // ...
    }
}

As to insert only if the item does not exist, I know of some options:

  1. INSERT ... SELECT ... WHERE NOT EXISTS

    INSERT INTO GENEROS (genero)
SELECT '$item' AS genero
WHERE NOT EXISTS (SELECT genero FROM generos WHERE genero = '$item')

  2. INSERT ... ON DUPLICATE KEY UPDATE

    INSERT INTO GENEROS (genero) VALUES ('$item')
ON DUPLICATE KEY UPDATE genero = genero

  3. INSERT IGNORE

    INSERT IGNORE INTO GENEROS (genero) VALUES ('$item')

    Note: THE IGNORE serves to suppress errors, and not to ignore the INSERT as it may seem

  • Thanks for the help.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.