PHP Looping script to update table data

Beginners - Learning PHP
#1

Hi all
I have a MySQL database which holds the records for an annual Horticulture and Craft exhibition. The database holds user details with entrant details along with the divisions and classes each entrant wishes to enter for the coming year.
We like to change the contestant number each year to avoid the possibility of cheating. We usually have between 150 and 200 entrants over 190 classes in 12 divisions with around 1100 entries.
Against each entrant we need to create a unique entrant number as their contestant number for the coming year.
The online system I have built works well apart from two things I an stuck on and need some help please.
At the moment each new entrant contestant number is set to 0. However if they have entered the previous year they will still have the contestant number from the previous year.

  1. I am looking at creating a PHP/MySQL script which I can run which will set ALL entrants entry number (contestant number) to 0.

  2. I am looking for a PHP/MySQL script I can use to insert a new contestant number which creates an incrementing number for each contestant who has entries this year. I need to be able to set the starting number and then assign the number to each active entrant as this years contestant number.

Any help and guidance in how I can achieve this will be appreciated.
Thanks in advance
Mike

#2

I’m going to assume you have a key on the users table that is separate from the contestant number?

A basic, approach, update without a where clause to update all the values to the same thing. This is normally frowned upon, but if you are doing it for a specific reason, it’s different.

However, the auto increment, will have to be done in the script itself, or by creating a trigger.

#3

Hi thanks astonecipher for the reply. Yes the table with the entrants has its own key which is used to relate to the family and to the actual classes entered. The entrant (contestant number) is a separate field against the Entrant Details.
As you mention, conducting an update without a where statement is usually taboo and this is probably where I cannot find much reference material on how I can achieve this. At the moment we conduct this operation manually however I am sure there is some way of doing but I am at a loss as where to start. I have a script which resets all of the contestant numbers to 0 but how to get it to increment from a start number and update each entrant is where I am really stuck and I am looking for advice on the best way to do it.
I believe a looping array which looks for each entrant with valid entries and then inserts the next number in the sequence to the entrant number. It would loop until no entrants without an entry number is reached but how to construct this sort of looping array is where I need some advice and guidance. If anyone can help please?

#4

Both tasks are fairly simple. Since this is apparently web based, do you currently have a user authentication/login system so that you can control who can cause these activities to be executed?

To reset all the values to zero, just execute an UPDATE query setting the entry number column to a zero.

To assign new numbers, you would either a) have a form that submits the starting number, or b) if continuing the numbering for newly added entrants after you have already assigned some numbers, get the current MAX() value + 1, to be used and incremented in a loop where an UPDATE query, with an ORDER BY term, if you want the numbing to be applied to the rows in some particular order (date of registration, alphabetically by name, by class/division, …), and a LIMIT 1 term, assigns the current value to active entrants that have a zero entry number. You can end the loop when the number of affected rows is zero, meaning that the WHERE clause finding active entrants that have a zero entry number no longer matched any row(s).

The only piece missing is how do you know who the active entrants are?

#5

Thanks phdr.
I have a full login system which allows for users to login and manage their respective family members who are entering the show.
At the moment I have an update statement which can be run by the Admin to reset all previous entrant contestant numbers from the previous years, I agree this is quite straight forward and consists of
---------------------------code-------------------------------------------
require_once(’…/includes//**.php’);
$sqlzero = “UPDATE TblEntrants SET TblEntrantst.FdEntrantNo = 0”;
if ($conn->query($sqlzero) === TRUE) {
$msg = “All Entrants entry numbers set to zero”;
etc.
-------------------------code end--------------------------------------------
I also agree that we could assign an incremental contestant number on entrant creation however this is NOT what we need.
As stated in my original post we do not want the entrants to see their respective entrant number until the day before the show. At the moment the admin can run a process as follows.
PHP script to create a temp table with three fields, index (auto increment), exhibitor Id(int) and exhibitor name(text)(the last field is not really needed but we have it their for easy referral if things go wrong) we manually set the start point of the increment field so we can generate the contestant number.
PHP update query copies all entrants that have entries into the temp table which creates the contestant number.
PHP update query now updates the TblEntrants.FdExhibitorNo with the Index number from the temp table where TblEntrants.FdEntrantNumber = TmpTable.ExhibitorId. We then delete the tmp table.

All I am trying to get advice on is surely there is an easier way of doing it just with an incrementing script and possibly a looping array but I am stuck on how to do it and I am looking for pointers and or examples on how to create it please?

#6

This is redundant. if is by default a truthy check.

Should just be

if ($conn->query($sqlzero))

#7

Nothing I wrote is suggesting that.

#8

Thanks benanamen I missed that!
phdr Sorry that is what I understood you were suggesting at “continuing the numbering for newly added entrants after you have already assigned some numbers, get the current MAX() value + 1”. The entries will be closed when the admin needs to assign the contestant numbers.
Can you point me at an example of assigning a value and inserting it for each exhibitor who has exhibits or where a query with a count > 0 is looped through to provide the results for the update. At the moment the query returns the exhibitors in alphabetical order to update the temp table.

Sponsor our Newsletter | Privacy Policy | Terms of Service