Make multiple inserts in SQL, multiple inserts

Navigation
0

Good night to all, once again live my Achilles heel with the blessed sql ...

I want to insert data into two tables ... client table and address.

I get the sql below, fill in the variables and execute the sql in phpmyadmin and it works ... then I get the sql that worked and I put it in my PHP and nothing ... it does not give error and does not insert ...

I searched for something related to mysql_insert_id () , but I do not know if I did it right.

Could you help me with this syntax? 

$sql = "INSERT INTO 'endereco' ('id', 'rua', 'numero', 'bairro', 'cidade', 'cep') 
    VALUES (NULL, $logradouro, $numero, $bairro, $cidade, $cep);
       $xy = mysql_insert_id();
    INSERT INTO 'cliente' ('id', 'nome', 'profissao', 'endereco', 'email') 
    VALUES (NULL, $nome , $profissao, $xy,$email)";
    
asked by anonymous 20.05.2015 / 00:03

2 answers

2

The problem is in the execution of multiple inserts and the erroneous way of assembling the queries, mixing variables out of scope.

By default, SGDB (MySQL) does not allow multiple queries in a single execution, except for cases of transactions (BEGIN ... COMMIT).

For your specific case, execute the queries individually to resolve the problem.

Example: 

// executa a primeira query
mysql_query("INSERT INTO 'endereco' ('id', 'rua', 'numero', 'bairro', 'cidade', 'cep') VALUES (NULL, $logradouro, $numero, $bairro, $cidade, $cep);");

// obtém o último id, o mais recente
$xy = mysql_insert_id();

// executa a segunda query
mysql_query("INSERT INTO 'cliente' ('id', 'nome', 'profissao', 'endereco', 'email') 
    VALUES (NULL, $nome , $profissao, $xy,$email);");

Relevant comments

  • Avoid using mysql_ * functions because they are obsolete. Try using PDO, MySQL or some popular library.

  • There are logical problems in the schema structures of the tables. Look into studying data modeling.

  • Multiple queries are different from multiple inserts, since it is possible to apply multiple inserts if the query data structure is identical, but it is not possible to explain this feature to the present question. >

    • 20.05.2015 / 02:28
    1

    You need to call the mysql query within php to work, the way you did the $ sql variable is understanding your code as a string.

    Come on. 

        $sql = mysql_query("INSERT INTO endereco(id, rua, numero, bairro, cidade, cep)VALUES(0,'$rua', $numero, '$bairro', '$cidade', '$cep'");

    Note that the variable $ number is not being enclosed within quotation marks since I am assuming that in the table the field number is an int

    A cool thing for you to see errors in sql is to add an or die at the end of the query see: 

       $sql = mysql_query("INSERT INTO endereco(id, rua, numero, bairro, cidade, cep)VALUES(0,'$rua', $numero, '$bairro', '$cidade', '$cep'") or die(mysql_error());
    // se houver algum problema de sintaxe, o sql vai indicar para você

    Good luck

        
    20.05.2015 / 00:26
    List returns only the last element map in C works?