Making multiple SQL inserts, multiple Inserts

Asked

Viewed 333 times

0

Good evening to all, once again I live my Alans heel with the blessed sql...

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

I take the sql below, fill the variables and run the sql in phpmyadmin and it works... then I take the sql that worked and put in my PHP and nothing... no error and nor insert...

I searched something related to mysql_insert_id(), but I don’t 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)";
php mysql syntax

2 answers

2


The problem lies in the execution of multiple inserts and the wrong mode as mounted the queries, mixing variables out of scope.

By default, SGDB (Mysql) does not allow multiple queries in a single run, except for transaction use cases (BEGIN... COMMIT).

For your specific case, run the queries individually to solve 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 observations

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

  2. There are logical problems in the frameworks of the tables. Try to study data modeling.

  3. Multiple queries is different from multiple inserts, as it is possible to apply multiple inserts if the data structure of the queries are identical, but it is not the case to explain this resource to the present issue.

1

You need to call the mysql query inside php to work, the way you made 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 $number variable is not being inserted inside quotation marks because I am guessing that in the table the number field is an int

One nice thing for you to see errors in sql is to add an or die at the end of 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

Browser other questions tagged php mysql syntax

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