How to get the last inserted id from database and send it to the component?

Question

i want to get the last id from database from fetching api,

the problem is in the table MaterialUnit materialID is showing 0

console.log works fine in the server works fine!

here is server.js

app.get('/AddProducts', function (req, res) {
    const { materialName, description, categoryID, manufactureID, currencyID} = req.query;
    const INSERT_MATERIAL = `INSERT INTO material (materialName, description, categoryID, manufactureID, currencyID) VALUES 
                                ('${materialName}','${description}','${categoryID}','${manufactureID}','${currencyID}')`;
    connection.query(INSERT_MATERIAL, (error, result) => {
        if(error) {
            return res.send(error)
        }
        else {
            console.log(result.insertId);
            return res.json({id: result.insertId})
        }
    });
});

app.get('/AddMaterialUnits', function (req, res) {
    const { materialID, unitID, def, rate, price, vat, bar} = req.query;
    const INSERT_MATERIAL = `INSERT INTO materialunit (materialID, unitID, barcodeNo, salePrice, vatValue, isdefault, rate) VALUES 
                                ('${materialID}','${unitID}','${bar}','${price}','${vat}','${def}' ,'${rate}')`;
    connection.query(INSERT_MATERIAL, (error, result) => {
        if(error) {
            return res.send(error)
        }
        else {
            return res.send(result)
        }
    });
});

and then in the component:

 AddProduct() {
        let url1 = `http://localhost:3000/AddProducts?materialName=${this.state.name}&description=${this.state.description}&categoryID=${this.state.catID}&manufactureID=${this.state.manID}&currencyID=${this.state.curID}`;
        fetch(url1)
            .catch(error => console.error(error))
            .then(alert("Added successfully"))
            .then((result) => this.setState({materialID: result.id}))
            .then(alert(this.state.materialID));


        this.state.materialUnits.map(obj =>
            fetch(`http://localhost:3000/AddMaterialUnits?materialID=${this.state.materialID}&unitID=${obj.unit.id}&def=${obj.default.value}&rate=${obj.rate}&price=${obj.price}&vat=${obj.vat}&bar=${obj.barcodeNo}`)
                .catch(error => console.error(error))
                .then(alert("Added successfully"))
            )
    }

Can you please give more detail as to what's going wrong, because according to [this duplicate](https://stackoverflow.com/questions/31371079/retrieve-last-inserted-id-with-mysql) you're doing it correctly. — Sterling Archer, May 02 '19 at 15:50
you should be able to get the inserted id the way you've done, does your query insert a record and still gives you 0 or doesn't insert any record and gives id 0? — sid, May 02 '19 at 15:57
just materialID is 0 , the rest are gd. i made an alert in the fetch .then(alert(this.state.materialID)) it shows null — isso kd, May 02 '19 at 15:59
no not that, im asking once the script runs, do you see any new record in the db? — sid, May 02 '19 at 16:00

Junius L · Answer 1 · 2019-05-02T19:02:09.873

The problem is the id is not set to AUTO_INCREMENT, it is always 0. To fix this you need to add it manually in your sql statement, which I don't recommend. for exmple INSERT INTO MATERIAL (id, ...) VALUES(12, ...).

Let MySQL do the increment for you. Modify your table and set id to AUTO_INCREMENT by running the following in your phpmyadmin.

ALTER TABLE `material` ADD INDEX(`id`);
ALTER TABLE `material` MODIFY COLUMN id INT AUTO_INCREMENT

Note: you need to empty the table, before running those queries.

If you get errors, you might need to recreate the table. drop the table and create a new one, using the following. change the length/values of the columns to your liking.

CREATE TABLE `material` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `materialName` varchar(500) NOT NULL,
  `categoryID` varchar(500) NOT NULL,
  `description` varchar(500) NOT NULL,
  `manufactureID` varchar(500) NOT NULL,
  `currencyId` varchar(500) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How to get the last inserted id from database and send it to the component?

1 Answers1