Concatenate data and insert in a field

Question

Concatenate data and insert in a field

Navigation

#1 by (0 votes)

0

I have part of this code php , where you are doing insert to the table inventory_list , but what I need is that it depends on that you have selected in fkCategory , fkLocation and fkSubCat that refer to id of the data.

Select name_category of the table category , name_sub of the table sub_category and numFile which is the new id autoincrementable that was generated when the insert was made, obtain these data and concatenate them in this way:

c.name_category+ "00"+ idSub + "00"+ numFile

It would stay like this:

CON-006-003

And that is inserted in the field id_item

This is the codigo php :

    $query = 'INSERT INTO inventory_list 
    (id_item,description_item,price_item,
    manufacturer,model_item,
    reorder_Level,target_Stock,image,registerDate,commentt,
    fkEngineer,fkCategory,fkLocation,fkSupplier,fkUnit,fkSubCat)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);';

 $query2="SELECT c.name_category,sc.idSub,i.numFile 
    from inventory_list
    inner join category as c on id_category =fkCategory
    inner join sub_category as sc on idSub =fkSubCat where numFile = ?"
    //guardar datos
    //insert en campo id_item -> c.name_category+ "00"+ idSub + "00"+ numFile


    $result = $connection->executeNonQuery($query,array($_POST['id_item'], $_POST['description_item'], 
    $_POST['price_item'] ,$_POST['manufacturer'],
    $_POST['model_item'], $_POST['reorder_Level'],
    $_POST['target_Stock'], $_POST['imageName'],
    $hoy, $_POST['commentt'] ,$_POST['fkEngineer'],
    $_POST['fkCategory'],$_POST['fkLocation'],
    $_POST['fkSupplier'],$_POST['fkUnit'],
    $_POST['fkSubCat']));
    if ($result > 0)
     {....}

How could I do it?

php query

asked by Pato 13.12.2018 в 19:00

source

1 answer

Get of Entry does not work (python (tkinter)) VUE - Generate dynamic components

score 0 · Answer 1

Let's see if I understand correctly. What you need is to insert a "calculated" data in which part of the concatenation uses the "ID" of the record you are inserting, is that correct?.

If that is the need, you can use "computed columns" whose definition depends on the BD engine you are using, for example, if it were Sql Server (they also exist in MySql, and other handlers), you can use:

CREATE TABLE Datos   
(  
    Id int IDENTITY (1,1) NOT NULL  
  , Valor smallint  
  , Codigo AS 'DAT-' + CONVERT(VARCHAR(10),Id)  
);  

INSERT INTO dbo.Datos (Valor)  
VALUES (10), (20);  

SELECT * FROM Datos


Id     Valor     Codigo    
 -----  --------  --------- 
 1      10        DAT-1     
 2      20        DAT-2

I hope that's it, if not, I think you should clarify your question a bit more.