Insert data from one table into another

Navigation
2

Well, I'm breaking my heart with this problem, I've had some time and I wanted your help. I have a code that inserts data into the venda table: 

<?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 insert the data of the venda table into them, which would be (sales_id and products or services). I read in some places to use more triggers I could not and others to use inner join but also I could not. Can anyone help me?

    
asked by anonymous 01.12.2015 / 17:41

2 answers

3

Have you tried using insert..select? 

INSERT INTO venda_produto (id, id_venda, produtos)
  SELECT venda.id, venda.id_venda, venda.produtos
  FROM venda;
    
01.12.2015 / 18:01
2

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

Intheabovemodel,arowwillbecreatedinthesales,product_product,andsale_ervicetableswheneversomethingisregistered.Thismodelwasmadefollowinghisinstructions.Iftheproductsandservicesarealreadypre-definedinthesystem,itwouldbeinterestinganothermodelthatwouldregisteranewlineonlyinthesalestableandmaketherelationofitwiththepre-definedproducts.

SQLcodetogeneratethesetables(changeaccordingtoyourneeds):

--MySQLScriptgeneratedbyMySQLWorkbench--12/01/1515:16:18--Model:NewModelVersion:1.0--MySQLWorkbenchForwardEngineeringSET@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'-------------------------------------------------------CREATETABLEIFNOTEXISTS'venda_produto'('id'INTNOTNULL,'produtos'VARCHAR(255)NOTNULL,PRIMARYKEY('id'))ENGINE=MyISAM;---------------------------------------------------------Table'venda_servico'-------------------------------------------------------CREATETABLEIFNOTEXISTS'venda_servico'('id'INTNOTNULL,'servicos'VARCHAR(255)NOTNULL,PRIMARYKEY('id'))ENGINE=MyISAM;---------------------------------------------------------Table'venda'-------------------------------------------------------CREATETABLEIFNOTEXISTS'venda'('id'INTNOTNULLAUTO_INCREMENT,'numero'INTNOTNULL,'data'DATETIMENOTNULL,'placa'VARCHAR(8)NOTNULL,'km'INTNOTNULL,'produtos'INTNULL,'servicos'INTNULL,PRIMARYKEY('id'),INDEX'fk_venda_venda_produto_idx'('produtos'ASC),INDEX'fk_venda_venda_servico1_idx'('servicos'ASC))ENGINE=MyISAM;SETSQL_MODE=@OLD_SQL_MODE;SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

MakeconnectiontothedatabaseusingMySQLi(theMySQLfunctionisdeprecated)

$link=mysqli_connect($host,$username,$password,$database)ordie(mysqli_connect_error());mysqli_set_charset($link,'utf8')ordie(mysqli_connect_error($link));

BeforemountingtheINSERT,escapealldatapassedby$_POSTtomakeSQLInjectionattacksdifficult.

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

MakeINSERTinthesalestableandgetthegeneratedID

$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);

Make INSERT in the sales_product and retail_service and UPDATE tables in the sales table with the ID for 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.

    
01.12.2015 / 19:00
How to round values of type Double? How to sort the date?