Here’s the problem: I am creating a virtual herd of American bison. At the moment the herd (created on a MySql database) consists of 20 adult cows, 1 adult bull, and 11 yearlings and newborn calves. The calves are “born” to a select number of the cows, seven so far this “season.” The calves need to be programmed to put on between 1.4 and 1.9 pounds of weight each day from Spring to Fall and < 1lbs in Winter. This requires that once a day, we run a CRON job that runs a PHP script.
The script should (1) SELECT (animal code #), weight FROM herd WHERE age < 1
(2) The amount of weight gain depends on month of the year (S = Summer, W=Winter)
(4) Each calf’s record is UPDATE(d) herd SET weight = $NewWt WHERE (animal code#) = $animalCode
Seems straight forward enough, but I have not been able to come up with the PHP code to make this happen. I thought all I needed to do was run the update inside the SELECT while code, but I can’t make it work, nor can I find examples online to use.
Forgive me if I have underestimated your level of knowledge.
Presumably you have a database table where each row is one bison? And one of the columns is the weight? And another column indicates whether this bison is one that will gain weight or not (ie adult or not)? Or maybe, instead, a date of birth?
So you need a loop to work through adding the required weight to the relevant column in each row of the bison that are not adults?
So you could run a SELECT query to obtain the animalCode# of the animals that would gain weight. You could then set up a PHP WHILE loop working through the results to run a SQL UPDATE query on one animal at a time.
Apologies if you knew all this already.
It occurs to me that they may be an object oriented programming approach to this but I don’t have the experience/knowledge to advise about that.
This is an interesting idea.
I have just been setting up a database for the herd and am going to give it a go.
I have a column for is-female which will be a true/false boolean, because, presumably, these will go on to have their own calves in the future.
It may be a way that I can teach myself some OOP (I’m not sure yet whether that would be appropriate or not). I will let you know when I get some PHP going.
I don’t want all the calves gaining the same amount of weight each day, so I am using a rand() function to select between 1.4 and 1.9 lbs per day. This should more closely match a real life herd. As the algorithm progresses, I want to add in the element of not only seasonal variation but also grazing conditions as determined by local precipitation - drought conditions in particular. The “Alpha” herd’s grazing range at the moment is what I am calling the “Bison Triangle” formed by the juncture of the Niobrara and Missouri Rivers in NE Nebraska and SE So. Dakota.
So if you run the following SQL query UPDATE animals SET weight=weight+((RAND()*0.4)+1.5) WHERE bdate = '2021-05-01';
this will add the weight that you want to any bison matching the WHERE condition (you will want to change the condition of course).
I think this is the query you want to run daily: UPDATE animals SET weight=weight+((RAND()*0.4)+1.5) WHERE DATEDIFF(CURDATE(), bdate) <366
It adds a weight between 1.5 and 1.9 to the value of weight to all the animals whose date of birth was less than 366 days ago.
This is so cool! How do I adjust this for season of the year: Summer vs. Winter?
Also while this works for the calves, I need to do something similar for the “yearlings”. 1-2 yrs.
I haven’t yet learned what the weight gain/loss is for adult bison on a year-round basis. Obviously, once they reach their maximum growth, they can’t go on added weight, other than males, which can easily weigh twice as much as a female.
Looks like the way you wrote the UPDATE, I don’t need to loop through the list. Right?
No worries. Thank you feeding back to me. It is frustrating to put work into something and then people solve it themselves or get bored and never let you know how things went.
I am by no means any kind of expert on this.
That’s correct. I did wonder if it would use the same random number for each calf but on testing it appears to create a new random number for each calf.
I had a thought about the rate of growth. This will result in all calves reaching a similar weight - after 365 individual random weight gains when the weight gain range is the same for all. For a start this would not address the fact that adult males are heavier than adult females - maybe they should have diffferent growth rates. Or do the males just gain weight for longer?
Also, it doesn’t allow for individual adult variation - presumably some adults will attain a greater weight than others? You could factor in some kind of adjustment to reflect that some will grow either faster or for longer than others. For example, by adding a “adult size coefficient” - eg bison_id 3 will have an “adult size coefficient” of 1.2 which means his/her daily weight gains should be multiplied by 1.2.
Off the top of my head, this could be done by using another query, altering the WHERE condition to reflect the new age range and altering the RAND part to reflect the new growth rate.
I have been wanting to create a “name” column so if people want, they can give names to their bison. #34 is now “Wake689” in your honor!
As for the differences in weight gain and growth, I think the easiest way is just to create and run different Cron scripts based appropriate WHERE criteria.
And somewhere in there we have to account for the rut this Fall.
So much of this can be done via the sql query as you see. But for more complex changes, with dependencies on various variables, like weight gain, dependant on age, time or year, etc, you could consider making a “Bison” class, so each animal is an object with its own properties and methods (functions).
These methods can be made to handle more complex logic as the animal ages, then feed back to the database.
I don’t know whether you know any OOP, but if you just grasp the basics of classes, objects and methods, you may see the potential for an application like this.
I feel honoured
(my spelling giving away my nationality).
Good luck with your project.
Is it just for fun and self teaching or is it part of formal training?
Well, the PHP part is self-teaching. For nearly 20 years, I programmed for ColdFusion. In ‘retirement’ I decided it was way past time to learn PHP. The ‘end game’ is creation of a prototype AR-based mobile game where you capture, raise and trade digital bison/buffalo, each of which are to become cryptocoin NFTs. Think Decentraland for bison.
