Mysql Insert, Update, Delete multiple tables

Question

I'm pretty new to Mysql and PHP and must admit my brain defers to ms access. I'm trying to update an old ms access app to a web app with a mysql db. Many things are going well and I'm masochistically enjoying the learning curve.

Except for:-

In the msaccess app I can base a form on a query that contains multiple tables and therefore insert, update and delete info in one or more of the tables. msaccess query

SELECT 
    merlinuc_ucd.tblsuppliers.SupplierID,
    merlinuc_ucd.tblsuppliers.PurchasedFrom,
    merlinuc_ucd.tblsuppliers.Street,
    merlinuc_ucd.tblsuppliers.City,
    merlinuc_ucd.tblsuppliers.State,
    merlinuc_ucd.tblsuppliers.Zip,
    merlinuc_ucd.tblsuppliers.Telephone,
    merlinuc_ucd.tblvehicles.ID,
    merlinuc_ucd.tblvehicles.LienHolderName,
    merlinuc_ucd.tblvehicles.Stock,
    merlinuc_ucd.tblvehicles.DateOfPurchase,
    merlinuc_ucd.tblvehicles.SupplierID,
    merlinuc_ucd.tblvehicles.Year,
    merlinuc_ucd.tblvehicles.Make,
    merlinuc_ucd.tblvehicles.Model,
    merlinuc_ucd.tblvehicles.Mileage,
    merlinuc_ucd.tblvehicles.BodyType,
    merlinuc_ucd.tblvehicles.Color,
    merlinuc_ucd.tblvehicles.Transmission,
    merlinuc_ucd.tblvehicles.StateOfOrigin,
    merlinuc_ucd.tblvehicles.PreviousOwnersName,
    merlinuc_ucd.tblvehicles.PreviousOwnersAddress,
    merlinuc_ucd.tblvehicles.PreviousOwnerCity,
    merlinuc_ucd.tblvehicles.PreviousOwnersState,
    merlinuc_ucd.tblvehicles.PreviousOwnersZip,
    merlinuc_ucd.tblvehicles.PreviousOwnersPrincipalUse,
    merlinuc_ucd.tblvehicles.PreviousOwnersBodyDamage,
    merlinuc_ucd.tblvehicles.PreviousOwnersMechanicalDefects,
    merlinuc_ucd.tblvehicles.MSRP,
    merlinuc_ucd.tblvehicles.RetailPrice,
    merlinuc_ucd.tblvehicles.Notes,
    merlinuc_ucd.tblvehicles.PurchasePrice,
    merlinuc_ucd.tblvehicles.Transportation,
    merlinuc_ucd.tblvehicles.Cleaning,
    merlinuc_ucd.tblvehicles.TotalRO,
    merlinuc_ucd.tblvehicles.EngineType
FROM
    merlinuc_ucd.tblsuppliers
        INNER JOIN
    merlinuc_ucd.tblvehicles ON merlinuc_ucd.tblsuppliers.SupplierID = merlinuc_ucd.tblvehicles.SupplierID
ORDER BY merlinuc_ucd.tblvehicles.Make;

I've tried this query in MySQL Workbench and it gives me read only results (the query contains the PK from both tables). I've searched on and off for a few months on this but if I'm finding any information (and I'm not sure I am) it's above my head. The workaround I've been using is to base my php forms on one table at a time, and this works OK, but is not my favorite solution as it makes it more confusing for the end user.

Answer 1

MS Access and MySQL are two completely different products. I guess you want to use something that is comparable to the MS Access forms, were you connect a table with the form and you can update insert and so on.

That is not how MySQL and the Workbench are working at all. Especially the workbench is not a front end tool like MS Access is (partially). In Access you have a lot of automation which creates the insert and update routines in the background when you connect a table to the a form.

In MySQL you need to do it on yourself and you should develop a front end which helps you. MySQL is a mighty tool and at one point you need to leave Access behind, if you need a faster and bigger Database you can setup on a very deep level.

Here you can find all then manipulations statements you need: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-manipulation.html

You also could use phpmyadmin: https://www.phpmyadmin.net/

UPDATE:

You could do it with transaction. Further readings here: SQL Update,Delete And Insert In Same Time

I use an other approach with doing it all in a stored procedure. I just load all data into one table (i call it write_table) and then let the database decide what to do with transferring the data to another table (called read_table)

Here is the code:

CREATE DEFINER=`user`@`%` PROCEDURE `validateData`()
BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS temp_write_data AS (SELECT * FROM write_data);

UPDATE read_data AS r
    LEFT JOIN
    temp_write_data as w
    ON (r.id = w.id)
    Set r.somecolumn = w.somecolumn
    WHERE w.othercolumn is not null;

INSERT INTO read_data
    SELECT *
    FROM temp_write_data AS w
        LEFT JOIN read_data AS r
        ON (r.id = w.id)
    WHERE r.othercolumn IS NULL;

DELETE write_data.* FROM write_data
    LEFT JOIN temp_write_data
    using(guid)
    WHERE temp_write_data.date is not null;

DROP TEMPORARY TABLE temp_write_data;
END

That is for sure a little bit complicated, but it works for me and i can let my fronted be very slim. I have to say, that my solution is a excel spreadsheet which is directly connected to the mysql server, so there is no script in-between. Either the Database or the spreadsheet has to do the logic. I decided for letting the front end be just a front end and let the database do the job. The pro is, that I don't need to transact so much data between both. The con is, that I think it is not the best idea storing logic into the database.

But with a script you surely could find a third (and better) way.

Best Regards Martin