Insert data from one table into another

Question

Guys, I’ve been racking my brain with this problem for a while now and I wanted your help. I have a code that inserts data into the table venda:

<?php
        if(isset($_POST['send'])){
        $venda = $_POST['num_venda'];
        $data = $_POST['data_venda'];
        $placa = $_POST['placa'];
        $km = $_POST['km'];
        $produtos = $_POST['produtos'];
        $servicos = $_POST['servicos'];

        include ('banco.php');


        mysql_query("INSERT INTO venda(id_venda, num_venda, data_venda, placa, km, produtos, servicos)
        values(
            NULL,
            '{$venda}',
            '{$data}',
            '{$placa}',
            '{$km}',
            '{$produtos}',
            '{$servicos}'

                        )
        ");



        header("location:listadevendas.php");


    }





    ?>

And I have other tables venda_produto(id, id_venda, produtos) and venda_servico(id, id_venda, servicos). I wanted to enter the table data venda in them, that would be (id_sale and products or services). I read in some places to use triggers more I could not and others to use Inner Join but also could not. Will someone help me?

Answer 1

You have already tried using Insert.. select?

INSERT INTO venda_produto (id, id_venda, produtos)
  SELECT venda.id, venda.id_venda, venda.produtos
  FROM venda;

Answer 2

Your question was not clear enough, but come on. Make a related table as follows:

In the model above, a line will be created in the sales tables, sales_product and sales_service whenever something is registered. This model was made following your instructions. If the products and services are already pre-defined in the system, it would be interesting another model to register a new line only in the sales table and make its relationship with the pre-defined products.

SQL code to generate these tables (change according to your needs):

-- MySQL Script generated by MySQL Workbench
-- 12/01/15 15:16:18
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Table `venda_produto`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `venda_produto` (
  `id` INT NOT NULL,
  `produtos` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `venda_servico`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `venda_servico` (
  `id` INT NOT NULL,
  `servicos` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `venda`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `venda` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `numero` INT NOT NULL,
  `data` DATETIME NOT NULL,
  `placa` VARCHAR(8) NOT NULL,
  `km` INT NOT NULL,
  `produtos` INT NULL,
  `servicos` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_venda_venda_produto_idx` (`produtos` ASC),
  INDEX `fk_venda_venda_servico1_idx` (`servicos` ASC))
ENGINE = MyISAM;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Connect to the database using Mysqli (the Mysql function is obsolete)

$link = mysqli_connect($host, $username, $password, $database) or die(mysqli_connect_error());

mysqli_set_charset($link, 'utf8') or die(mysqli_connect_error($link));

Before you mount INSERT, exhaust all data passed by $_POST to make SQL Injection attacks more difficult.

foreach ($_POST as $key => $value) {
    $key    = mysqli_real_escape_string($link, $key);
    $value  = mysqli_real_escape_string($link, $value);
    $data[$key] = $value;
}

Insert into the sales table and take the generated ID

$query = "INSERT INTO `venda`(`numero`, `data`, `placa`, `km`, `produtos`, `servicos`)
    VALUES ('" . $data['num_venda'] . "', '" . $data['data_venda'] . "', '" . $data['placa'] . "', '" . $data['km'] . "');";

mysqli_query($link, $query) or die(mysqli_error($link));

//Pega o ID

$insertID = mysqli_insert_id($link);

Insert into the tables of sales_product and sales_service and UPDATE in the table of sale with the ID to the other tables.

$query = "INSERT INTO `venda_produto`(`id`, `produtos`) VALUES (" . $insertID . ", '" . $data['produtos'] . "'); INSERT INTO `venda_servico`(`servicos`) VALUES (" . $insertID . ", '" . $data['servicos'] . "'); UPDATE `venda` SET `produtos`=[" . $insertID . "],`servicos`=[" . $insertID . "] WHERE id = " . $insertID . ";";

mysqli_query($link, $query) or die(mysqli_error($link));

That’s it.