• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Increment number (not ID)

jo-ka

Member
Hello.

I'm trying to find the best way to achieve this:

I have a table with players, where I have a field with license number. I need to automatically give the player a number, when the record is saved, avoiding this way that if more than one user is adding players, the number never get repeated.

I'll use later some list plugins like is unique or so, but now I'm just trying to find how to do this.

My approach is to have a calc element (only calc on save), selecting the latest record on the table and increment the value by one, then use upsert to update the licence field.

I have this calc:

$db = JFactory::getDBO();
$sql="SELECT MAX(license)+1 AS next_number FROM tests";
$db->setQuery($sql);
return $db->loadResult();

With this I can get the last number and generate a new one, which I think it's ok, but then I'm missing something on upsert.

The question here is that the license number must be only inserted on new records, and not on updated ones.

Am I thinking correctly with this approach? Is there a better way? What can probably be wrong on my upsert?

BR
 
Much better way is to use a form PHP submission plugin, running onAfterProcess, and manually update your license field, based on the PK value that got assigned to that row. And set the plugin to only run on "new".

Something like ...

Code:
// get the rowid (PK value)
$myRowid = $formModel->formData['rowid'];
// sanity check it's not empty
if (!empty($myRowid)) {
   // construct your license string using the row id ...
   $myLicense = "FOO-" . $myRowid;
   $myDb = JFactory::getDbo();
   $myQuery = $myDb->getQuery(true);
   // update the license field in this record
   $myQuery->update('yourtable')->set('license = ' . $myDb->quote($myLicense))->where('id = ' . $myRowid);
   $myDb->setQuery($myQuery);
   $myDb->execute();
}

Obviously modify the table/field names in the query, and adjust your license string to suit, so if you wanted the PK value to be 5 digits with leading 0s, like FOO-00001, or FOO-00123

Code:
$myLicense = "FOO-" . sprintf('%05d', $myRowid);

-- hugh
 
Dear Hugh.

OK, I understand this query, although of course I need some small changes.

The question here is I can't use the record ID generate the license, but a field that's holding the last number, because if someone save and delete some players (by mistake or so), the ID's will increase and I'll have "holes" in the numbering sequence.

So, following your excellent PHP code, probably I need to use the $myLicense variable with my SQL query:

Something like:

$lastLicense = (SELECT MAX(license)+1 AS next_number FROM players);
$myLicense = "FOO-" . $lastLicense;

Is this the correct way to use it?
 
You can do it this way.
But creating your own ID without relying on the record id (PK) may give you duplicated ones if two users submit at the "same" time.
And if a player is deleted you'll get "holes" anyway if it's not the one with the max number.
 
OK, good point. Anyway, I can' t really rely on the PK id auto increment, because I have a lot of old numbers that I need to keep.

That's why I'm thinking in creating a new field like this, that I can prepend with "2017", like "2017" . $myLicense.

And, question. Once the PHP script will run onAfterProcess, doens't it get allways a different number, even if the users submit the form at the "same" time?

But, I've tried my proposed method and it didn't worked, I assume my query mode is not correctly adapted to the script.

Can you help me identifying the error.

Any other comments or suggestions are welcome.

BR
 
Are your existing numbers larger than your current pk (rowid) value? If so, it is possible to manually set the auto increment value in MySQL. So if (say) your current largest rowid is 100, but you have existing serial numbers in the 200s, to avoid clashes you can manually raise your autoinc pointer to (say) 300, so your rowids will skip the 200s.

You've got 2 people strongly advising you to use the PK as the basis of your serial, and so far I don't see any real reason you can't.

Sent from my HTC6545LVW using Tapatalk
 
OK, good point. Anyway, I can' t really rely on the PK id auto increment, because I have a lot of old numbers that I need to keep.
"Relying" on the PK doesn't mean "take it 1:1"

If you have an extra field for your license you even don't need to set the autoincrement manually.
You can just do something like

$lino = $myRowid +$x; (x = the max of your "old" license numbers)
$myLicense = "2017-" . $lino;
 
You've got 2 people strongly advising you to use the PK as the basis of your serial, and so far I don't see any real reason you can't.

Dear Hugh.
Believe me, I'm taking yours and troester's advices in consideration, and I fully understand it. My problem is that I've inherited an completely anarchic manual player licensing system, which is composed by a year with 2 digits prepending the license number. As you can see on my screenshot, I have the id field, the year_classified field, the license itself field, and to put this on a system, I had to create a new field, concatenating year_classified + license on card_license field.

upload_2017-5-26_17-2-7.png

The problem here is that the license field is holding duplicated numbers, depending on the year classified. And this I cant change because I have 2500 players registered.

You can see it here...

upload_2017-5-26_17-5-56.png

Now I'm trying to regulate this a little bit, starting in 2017 year, where the licenses will be prepended by some string I'll have to choose, like "17-" plus the number that will be automatically generated with this new system, starting on 0000, like 17-0000.

That's why I'm insisting on this, otherwise I'll have to start on 2501 and start counting from there.

I now it doesn't make much sense, but it's my limitation.

Based on this info, do you have any other solution?

Thanks in advance.
 

Attachments

  • upload_2017-5-26_16-59-21.png
    upload_2017-5-26_16-59-21.png
    16.8 KB · Views: 28
"You can just do something like

$lino = $myRowid +$x; (x = the max of your "old" license numbers)
$myLicense = "2017-" . $lino;

Good idea also, but as I've explained before, my old license number is a complete anarchic field, with repeated values. That's why I'm trying a different approach.

Any other suggestion? I'm open to it. Thank's for your precious help.
BR
 
I still don't understand your problem.
What are your prerequisits? Number must start with the year, it must be unique, what else (must be an integer?, ...)?

What about starting with something like 173000 (i.e. '17' and rowid (+3000 - your max rowid at the moment)
(3000 because I'm not sure if MySQL may use "free" rowids if a record is deleted, so the difference would always be >0)
 
I'm not sure if MySQL may use "free" rowids if a record is deleted

Nope, it never re-uses a PK. Basically each table has an auto-inc pointer, and it get's incremented each time a new row is created, and never decreases (unless you do that by hand). You can manually change the auto-inc pointer, with "ALTER TABLE yourtable auto_increment = 123". After that, the next PK assigned would be 124. Which was what I was suggesting in a previous post, that he raises his auto-inc to greater than whatever the current serial is, to avoid any clashes. Although if he is prepending the year, that probably won't be an issue.

@jo-ka - I still don't get why you can't use the PK like we are suggesting. Yes, you have existing messed up license numbers. But the main thing here is making sure that they never get messed up again, and BELIEVE ME the only way to do that without going to an awful lot of completely unnecessary trouble is to use the PK value. Your suggestion of having a query that get the MAX of previous numbers can still wind up with duplicates, if two forms are submitted very close together, unless you go to the trouble of writing transactional queries that can be rolled back. And even if you get that working, all you've done is duplicate what you have for free with the PK value. You are trying to re-invent the wheel. Really. I've been doing this stuff for three decades. I'm trying to save you a lot of time and trouble, based on that experience.

So, my next question, are you going to try and fix those existing duplicates in the old numbers, or are you stuck with them?

-- hugh
 
I still don't understand your problem.
What are your prerequisits? Number must start with the year, it must be unique, what else (must be an integer?, ...)?

Sorry if you misunderstood me. So, the prerequisits are, value must be an integer and must start on 0000, and I can't use another auto_increment because ID is already an auto_increment field.
 
So, my next question, are you going to try and fix those existing duplicates in the old numbers, or are you stuck with them?

-- hugh

I'm stuck with them...

Thank you very much. I have no more arguments except accept your suggestions. Just have to think how to do it.

Thank you both guys.
 
and I can't use another auto_increment because ID is already an auto_increment field.
You don't need to.
Use your card_license as a "pseudo autoincrement" by doing some calculations/string handling with the (really autoincremented) rowid.
E.g. subtract the max. rowid of your "old" license numbers and prepend the year.

Then your first "new" card_license would be 17-0000 and you can leave the old numbers untouched.
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top