keep a different foliate in mysql php when two records are inserted at the same time

Question

keep a different foliate in mysql php when two records are inserted at the same time

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

2

I have the following problem: Two or more users on different computers are filling out a form and that form assigns them a sheet.

The folio is consulted according to the last one registered in the BD and returns the next folio to be used.

Example if the 00001 is already present and I am currently filling in a form assigns me the 00002 but the other person in another machine also assigns the 00002 before I save the information. When it saves the insertion to the BD the first to save it generates a folio 00002 and to me the 00003 but when I consult my folio 00003 the data of the person who was filling the form appears first.

Is there a way to book that folio while I do not click save to the record in the DB with PHP?

I clarify that I do not use AutoIncrement in my BD. What I do is the following when consulting if that page already exists:

error_reporting(E_ERROR | E_WARNING | E_PARSE);
$link = mysql_connect('localhost', 'root', '');
mysql_select_db('mb', $link);
$tmp1      = ("SELECT * FROM rprh06 ORDER BY FOLIO DESC");
$consulta1 = mysql_query($tmp1, $link);
$folio     = mysql_num_rows($consulta1);
mysql_free_result($consulta1);
if ($folio == 0) {
    $folio = $folio + 1;
} else {
    $folio = $folio + 1;
}
if (strlen($folio) == 1) {
    $folio = "0000" . $folio;
}
if (strlen($folio) == 2) {
    $folio = "000" . $folio;
}
if (strlen($folio) == 3) {
    $folio = "00" . $folio;
}
if (strlen($folio) == 4) {
    $folio = "0" . $folio;
}

After this he shows me the next folio to use.

mysql php

asked by Juan Pablo Bustamante Luna 23.11.2016 в 18:43

source

3 answers

1

I would do it in the following way:

I would assign the number of folio at the time you save it in the database but not pass it through the form.

If you do not use auto increment you have to know what is the last folio before saving the data in the database.

Scheme:

Receive the $datos_del_usario

Ask the database which is the last page

Increase folio + 1

Save everything together in the database: $datos_del_usario + folio + 1

Note apart : Do not use mysql_ !! Use mysqli or PDO with prepared statements to avoid attacks SQL injections

answered by 23.11.2016 в 19:01

1

I agree that it is best that the folio you add at the time of insertion, not at the time you access the form.

Then according to my example, omit everything related to the folio in your code and look at the following options:

You can delegate that task to mysql if your folio field is of the numeric type (to make a +1 in the insert):

INSERT INTO mi_tabla (folio, dato1, datoN)
 VALUES ( SUM( ( SELECT folio FROM mi_tabla) + 1), valor1, valorN )

But if it's not numerical, you can not do mathematical operations, unless you do a Cast:

INSERT INTO mi_tabla (folio, dato1, datoN)
     VALUES ( SUM( ( CAST ( SELECT folio FROM mi_tabla) AS integer) + 1), valor1, valorN )

You only add it in your original query.

I think it's best because you do not complex the php code.

answered by 23.11.2016 в 22:56

Call an attribute from one class to another to make a ValidationResult Android: Stop the cycle, visualize the result and return to the cycle

score 1 · Accepted Answer

We go by parties. And I will be agnostic of the language with your problem.

You should not reserve a folio number in advance, unless you do not mind losing folios in between.

If that is a problem, then you should save the folio at the same time you save the data. To avoid major problems you could have a table with the maximum folio saved, and assign the new one within a transaction to all the tables at the same time, with exclusive lock to avoid concurrency problems.

If losing folios in the middle is not a problem, then your application itself should not have major problems, and you probably have a problem when saving and that's why you see data that does not correspond to you. I would also recommend checking that the data in the database is correct before continuing.

Apart from this, I see a couple of problems in the algorithm (which does not mean that it's even good, only problems in that algorithm).

Notice that the line

"SELECT * FROM rprh06 ORDER BY FOLIO DESC"

causes that to look for the folio that follows you have to do a full scan of the table .. for few records it's fine .. but in a serious application, this is very bad.

Also, googling this: "padding zeros php"

I found this:

str_pad

(PHP 4 > = 4.0.1, PHP 5, PHP 7) str_pad - Fill a string up to one   determined length with another string

string str_pad (string $ input, int $ pad_length [ string $ pad_string   = "" [ int $ pad_type = STR_PAD_RIGHT]])

This function returns the input string filled in on the left, the   right, or on both sides to the specified length. If he   Optional argument pad_string is not supplied, the input is filled   with spaces, otherwise it is filled with the characters of   pad_string up to the limit.

which would solve the part of filling with zeros.