Read data from MySQL view, Edit and write to another table

Read data from MySQL view, Edit and write to another table

Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

I am new to Editor and wanted to understand what options I might have to solve a problem in one of my PHP applications. I have a View that contains line item data for orders. I know that a View cannot be edited, but I was hoping to be able to open the editor and when changes are made the changes are written to another table on the database (delivery) . Basically, the user would open the order form and would change the quantity to be delivered based on availability. I primarily work in PHP with limited expertise in JS or JQuery

This question has accepted answers - jump to:

Answers

  • mguinnessmguinness Posts: 85Questions: 12Answers: 1

    I know that a View cannot be edited

    MySQL does allow for Updatable and Insertable Views, but only under certain conditions.

    I was hoping to be able to open the editor and when changes are made the changes are written to another table on the database

    What download are you using PHP library or just JS+CSS? With the latter you have complete control with Client / server data.

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    I have the PHP library downloaded and am trying to understand how it might be used. I am very hopeful that I can solve this issue as it is a blocker at this point. I will research the link you provided and work on gaining a better understanding of how Client / Server data operates.

    Thanks very much for the advice and help[.

    Ed

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    You can indeed use the PHP libraries to read from a VIEW and then write back to a specific table - there is an example of that here.

    Allan

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    Thank you Allan. I look forward to employing this powerful utility.

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    I have successfully used the readTable() method to retrieve data from a MySQL view, populate the form and insert the data into my new table. How closely structured do the view and table have to be? I am able to edit fields and push the data to the target table. I am looking use two additional fields in my target table (a boolean field to indicate the item was processed and or a date field to be entered to group the fields that are selected from the view and placed into the target table.) My view would be filtered to not show the updated records on refresh. This will keep the interface less confusing.

    Also can I pass a parameter to the form to filter on a value from a field using Select? I would like to have the view results filtered to a single customer. In other words can I use a WHERE method with readTable?

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    I have the page working now and it is successfully updating the delivery table after loading data from the vwItemsOrder view. I am not attempting to make the form more clear by using the Customer's company name (customerCoName) in place of the customer Id. I have tried to follow the documentation but I am getting a JSON error.

    I am thinking because I am using a readtable, that is the tabke to which I would have to join the customers table to get the name value. I know I can rewrite the view to include the name, but wanted to see if anyone could tell me where I went off track? Thanks in advance.

    <?php
    
    /*
     * Editor server script for DB table delivery
     * Created by http://editor.datatables.net/generator
     */
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    // The following statement can be removed after the first run (i.e. the database
    // table has been created). It is a good idea to do this to help improve
    // performance.
    $db->sql( "CREATE TABLE IF NOT EXISTS `delivery` (
        `DetailId` int(10) NOT NULL auto_increment,
        `reqdate` date,
        `quantity` numeric(9,2),
        `customerid` varchar(255),
        `product` varchar(255),
        `locdesc` varchar(255),
        `ordernum` numeric(9,2),
        `cropid` varchar(255),
        `orderdate` date,
        PRIMARY KEY( `DetailId` )
    );" );
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'delivery', 'DetailId' )
        ->readTable('vwItemsOrder')
        ->fields(
            Field::inst( 'reqdate' )
                ->validator( Validate::dateFormat( 'm/d/y' ) )
                ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
                ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) ),
            Field::inst( 'quantity' ),
            
            Field::inst( 'vwItemsOrder.customerId' )
                ->options(Options::inst()
                    ->table('customers')
                    ->value('customerId')
                    ->label('customerCoName')
                    ),
            Field::inst('customers.customerCoName'),
            
            
            Field::inst( 'product' ),
            Field::inst( 'locdesc' ),
            Field::inst( 'ordernum' ),
            Field::inst( 'cropid' ),
            Field::inst( 'orderdate' )
                ->validator( Validate::dateFormat( 'm/d/y' ) )
                ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
                ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) )
        )
        
        ->leftJoin('customers', 'customers.customerId', 'vwItemsOrder.customerId' )
        
        ->process( $_POST )
        ->json();
    
  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    I see a couple of errors now that I have read more. I am missing the '=', at line 67 and because I am doing a join I must be explicit in the field names.

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    After correcting issues, I am not getting an error:

    DataTables warning: table id=delivery - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delivery.DetailId' in 'field list'

    DetailId is the primary key in the delivery table and matches to the same field in vwItemsOrder. If I remove the explicit call in:

    Editor::inst( $db, 'delivery','DetailId' )

    The error changes to ...Unknown column 'id' in 'field list'

    I am hoping this is something simple to correct. I am joining the customers table to get the company names in the form, but I want to get the form working before doing the name replacement for customerId.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Could you add ->debug(true) just before you call ->process(...) please? Then send me the JSON that is returned from the server when you load the data. That will then contain the SQL that was used.

    Thanks,
    Allan

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    Here is the JSON. I tested the query and it indeed does fail when I run it in MySQL.

    {"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'delivery.id' in 'field list'","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT `delivery`.`id` as 'delivery.id', `delivery`.`reqdate` as 'delivery.reqdate', `delivery`.`quantity` as 'delivery.quantity', `delivery`.`customerid` as 'delivery.customerid', `delivery`.`product` as 'delivery.product', `delivery`.`locdesc` as 'delivery.locdesc', `delivery`.`ordernum` as 'delivery.ordernum', `delivery`.`cropid` as 'delivery.cropid', `delivery`.`orderdate` as 'delivery.orderdate' FROM `vwItemsOrder` LEFT JOIN `customers` ON `customers`.`customerId` = `vwItemsOrder`.`customerId` ","bindings":[]}]}
    
  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    I see the issue now, the query is calling for the delivery table fields from the VIEW instead of the delivery table. I must have the server script configured incorrectly

    <?php
    
    /*
     * Editor server script for DB table delivery
     * Created by http://editor.datatables.net/generator
     */
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Upload,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;
    
    // The following statement can be removed after the first run (i.e. the database
    // table has been created). It is a good idea to do this to help improve
    // performance.
    $db->sql( "CREATE TABLE IF NOT EXISTS `delivery` (
        `DetailId` int(10) NOT NULL auto_increment,
        `reqdate` date,
        `quantity` numeric(9,2),
        `customerid` numeric(9,2),
        `product` varchar(255),
        `locdesc` varchar(255),
        `ordernum` numeric(9,2),
        `cropid` varchar(255),
        `orderdate` date,
        PRIMARY KEY( `DetailId` )
    );" );
    
    // Build our Editor instance and process the data coming from _POST
    Editor::inst( $db, 'delivery' )
        ->readTable('vwItemsOrder')
        ->fields(
            Field::inst( 'delivery.reqdate' )
                ->validator( Validate::dateFormat( 'm/d/y' ) )
                ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
                ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) ),
            Field::inst( 'delivery.quantity' ),
            Field::inst( 'delivery.customerid' ),
            
            
            
            
            Field::inst( 'delivery.product' ),
            Field::inst( 'delivery.locdesc' ),
            Field::inst( 'delivery.ordernum' ),
            Field::inst( 'delivery.cropid' ),
            Field::inst( 'delivery.orderdate' )
                ->validator( Validate::dateFormat( 'm/d/y' ) )
                ->getFormatter( Format::dateSqlToFormat( 'm/d/y' ) )
                ->setFormatter( Format::dateFormatToSql( 'm/d/y' ) )
        )
        ->leftJoin('customers', 'customers.customerId', '=', 'vwItemsOrder.customerId')
        ->debug(true)
        ->process( $_POST )
        ->json();
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    If you remove delivery. from each of the Fields, and also remove the leftJoin, that should work.

    When it comes to using the left join (it doesn't appear you are in the code above), include that in your VIEW rather than in the Editor code, so you can just pick it out from the VIEW as you would any other column / field (i.e. no need for a table prefix).

    Allan

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    Thanks, Allan, that did work for me. I am reading as much as I can in the forums and hopefully are becoming better at understanding the functionality. Where would I learn more about the inserting of records to another table. I am specifically trying to understand how to omit certain fields from updating. In the form above, I pull the outstanding orders from a VIEW which updates when the delivery table is updated. However, I was using the PK from the view to populate the PK in delivery which works, but does not allow me to have multiple delivery entries. I am thinking I would need to make the field that receives the PK readonly and then have the delivery table autoincrement the value in its place.

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    One more question, I see the use of 'get' and 'set' in the example for readTable.

    Do the tables have to have a common primary key?

    Do I have to list all of the fields in the readTable and declare if I do not want them?

    Ideally, I would like to pull in the fields from the view which would be a subset of those in the delivery (target) table. I would also like to be able to add fields in the form to insert into the target table.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Do the tables have to have a common primary key?

    Yes - it is a good point that. The row being edited needs to have an id that matches the row that the server will edit.

    Do I have to list all of the fields in the readTable and declare if I do not want them?

    You would define all the field that you want from the VIEW. Any you don't want, just don't declare them. There isn't an equivalent to a wildcard * selector though I'm afraid.

    Typically the closer the names of the viewed from the VIEW and the table being written to the better.

    Allan

  • Ed MartinEd Martin Posts: 23Questions: 5Answers: 0

    So to wrap up this area of discussion, It appears that there is no way to read values from another View or TABLE unless the table being read into the from has a linked primary key. Which means I cannot insert "new" rows into the target table since the read data is going to be linked to the primary key.

    In the application I am building I need to read orders and place them in a form for the delivery team to pick and build a delivery manifest. This works perfectly if all orders are filled at once time. But if there is not enough stock to fulfill the order, the amount could be changed from say 8 to 5. The record in the delivery (target) table would be updated to 5 and the delivery can be processed.

    The VIEW of orders now shows 3 remaining on order. To this point it is working perfectly. But the remaining order for 3 when selected, will update the previous record in delivery (which is already at 5) and will now replace the 5 with a 3 the in the quantity since there can only be one row for that primary key. Now the on order will show 5.

    So Editor will work in the scenario where the entire order is fulfilled every time, but I cannot do partial orders unless I devise another routine.

    Thanks for your time on this, it has been quite educational.

This discussion has been closed.