One request at a time with PHP and MySQL

68

I would like to use a MySQL database to store transactions with a PHP script. For that, I am using ID, Name and other columns names. Because of legal issues in my country, the transaction name needs to follow a specific pattern. For example, for ID “25”, the Name would be “Inv. 25”. For that, I would use “lastInsertId();” and sum 1 to get the next number for the Name, to match with the ID column. It may sound strange to store the same thing in two columns but again, because of legal issues, I need to hash this string with a public and a private key and also, the string shown is just an example, because it can become something like "Inv. 2018/24". To sum this up, here’s a diagram table to help visualize the problem.

Database Diagram Table

With this question I would like to make sure that this really happens when having multiple users adding data to the database via a PHP script, otherwise the Name will differ from ID column. Since my PHP script has a “get” request (for the “lastInsertId()”) and a “post” request (for the rest of the info in the table row), do I need to lock or block the user to make changes while someone is running this PHP script? I’m afraid I’ll run into performance problems if I do this. On the other hand, I'm not sure if two different users, when executing the same script, can get the same "lastInsertId();" and post them in the database with different ID's and the same Name.

Thanks,
Miguel

EDIT: Explanation for the purpose of Name column and eventual execution problems.

481

Answer

Solution:

PDO's lastInsertId() won't be affected by other requests or connections so you can rely on that.

However, do you really need to store ID twice?

If the name is alwaysInv. {id} then it seems to me it makes more sense just to format that with php:Inv. <?= $row['id'] ?> or using CONCAT with SQL:select CONCAT('Inv. ', id)).

8

Answer

Solution:

You can only rely on last insert id if the lastInsertId() function is executed immediately following the insert statement.

Note that if the name always has the same characters followed by the identifier, then you should not save the same data in two different columns. Instead you should have a domain object with a getter for the legal name of the transaction:

class Transaction {
    private $id;

    public function getId() {
         return $this->id;
    }

    public function getLegalName() {
        return 'Inv. ' . $this->id;
    }
}

Having said that; if you are developing a financial system, you might not actually want to reinvent the wheel. There are many PHP solutions out there, including open source ones.

People are also looking for solutions to the problem: php - Fatal error: Allowed memory size of ... bytes exhausted

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.