• PHP Function for update or insert data from one to another mysqli table

    CatheriBurbank Member July 28, 2017 at 1:11 pm

    I need help with creating php function, procedural php, MySQLi

    I have 3 tables. Table PRODUCTS contain all the information that i want to transfer into other two tables.

    Table PRODUCTS: id, name, serial, description
    Table List: id, name, serial
    Table View: id, name, description

    I need script that read table PRODUCTS and data from this table inesert or update values in the other two tables.

    1. After call function
    2. Read Table PRODUCTS
    3. Read table LIST, if in “list” table have record with same serial as in table PRODUCTS update name with that serial from table products and if don’t have insert id, name and serial with data from products
    4. Read table View, if have record with same name as in table PRODUCTS update description with data from table PRODUCTS or insert id, name, description

    In table products I have 25 records

    #12529
  • codewithc
    CWC Keymaster July 28, 2017 at 1:11 pm

    it may be easier to create SQL trigger and then just recreate PRODUCT table rather than making it with procedure, the trigger will enable automatically after every insert into product

    #12533
  • codewithc
    CWC Keymaster July 28, 2017 at 1:12 pm

    If the PRODUCTS table is atomic, and the List and View tables will always match the PRODUCTS table, there is no reason to make copies. Just query the columns you need (when you use them wherever else), instead of “SELECT *”.

    If you’re copying them to change them later, or for whatever reason you want to copy. You can run these queries:
    TRUNCATE List; — this will empty the List table
    TRUNCATE View; — this will empty the View table
    INSERT INTO List ( id, name, serial ) SELECT (id, name, serial ) FROM PRODUCTS;
    INSERT INTO View ( id, name, description ) SELECT (id, name, description ) FROM PRODUCTS;

    Every time you run those 4 simple queries, it will clear List and View and copy the relevant information from PRODUCTS.

    #12534
Viewing 2 reply threads
  • You must be logged in to reply to this topic.
en_USEnglish
en_USEnglish