Usarjjaco Posted August 3, 2012 Share Posted August 3, 2012 Hello Everyone, I just wanted to drop in here and ask a couple questions of the PHP & SQL experts here. I've done work with the basics of SQL / PHP and ChronJob integration. However, I'm now on to trying to perform something a little more complex, and I'm banging my head a little bit on how exactly to get this done. In this I'm going to be using 2 tables, one we'll call Supply, another we'll call Distribution. A user (this is a browser based game), needs to elect to give X amount of supply to X member(s). The supply table contains the amount of supplies in each "Supply Depot". Basically using a ChronJob I need to do the following: 1: Select each individual Supply Depot, detect how much supply is requested (There is a column for the amount of supplies a location has, and a column for the level of supply they've requested to distribute), and then if the Supply Depot has enough supplies, release the supplies to the individual member(s). The problem I'm running into is that 1) there are multiple depots all distributing to different member(s), and 2) that the different depots may be releasing a different number of supplies. Tables/Columns In Use Table: Supply DepotColumns Within Table: TotalSupplies(int), SupplyLevel(varchar) <--- Supply level corrosponds to X amount of supplies per day, held elsewhere Table: MembersColumns Within Table: TotalSupplies(int), The index for both of these is a simple ID# column. The script will run once every 24 hours. My only guess on this was something along the lines of doing a different pull in the script for each supply level, something like: SELECT * FROM SupplyDepot WHERE SupplyLevel = 'Level1' Then some kind of For Each loop. But the exact method is elluding me, and I'm not sure if this is even the most effective method (I'm trying to do this with as little server load as possible because I expect traffic to be high. Thanks in advance guys! Link to comment Share on other sites More sharing options...
birbal Posted August 3, 2012 Share Posted August 3, 2012 querying database in loop is never a good idea. there should be other way around. you can use join to combine tablesdev.mysql.com/doc/refman/5.0/en/join.html Link to comment Share on other sites More sharing options...
Usarjjaco Posted August 3, 2012 Author Share Posted August 3, 2012 birbal, Thanks for the timely response. I just am not sure how JOIN would perform the task I need to do. I can see it for putting the information together, but the fact remains that I need to do something like supplydepot1 gives 10 supplies to id 1 2 and 10supplydepot2 gives 15 supplies to id 18 11 and 4supply depot3 gives 50 supplies to id 4 8 and 20 Since none of them are giving the same amount to the same ID's, I'm not seeing how join would work here, maybe I'm missing something? Thanks! Link to comment Share on other sites More sharing options...
justsomeguy Posted August 3, 2012 Share Posted August 3, 2012 I'm not quite sure what the problem is. Why is this a problem: The problem I'm running into is that 1) there are multiple depots all distributing to different member(s), and 2) that the different depots may be releasing a different number of supplies. Link to comment Share on other sites More sharing options...
Usarjjaco Posted August 7, 2012 Author Share Posted August 7, 2012 I guess what I'm missing is that I don't know how to run through one row at a time, and update based on the settings for that individual row? Link to comment Share on other sites More sharing options...
justsomeguy Posted August 7, 2012 Share Posted August 7, 2012 You can use a function like this one to loop through a result set to get each row one at a time: http://www.php.net/manual/en/mysqli-result.fetch-assoc.php Link to comment Share on other sites More sharing options...
Usarjjaco Posted August 8, 2012 Author Share Posted August 8, 2012 justsomeguy, I took a stab at it in hopes it would better show you guys what I'm looking for, so here it is, and obviously, it's not working, which means I'm missing somehting (I'm thinking it has something to do with looping through each result, though I'm not sure how to implement it here...) //Select All Supply Depots$HQR = mysql_query("SELECT * FROM buildingso");//While We Are Fetching The Rows Associated With THe HQ's....while($HQROW = mysql_fetch_assoc($HQR)) { //Getting Quantities Per Person Based On Setting (This is the amount of supplies to distribute to each person based on the setting for that HQ) if($HQROW[foodlevel] == 'Minimal') { $Food = '12'; } if($HQROW[foodlevel] == 'Moderate') { $Food = '24'; } if($HQROW[foodlevel] == 'Full') {$Food = '48'; } if($HQROW[foodlevel] == 'Abundant') { $Food = '60'; } //Get The Number Of People, and therefore, number of supplies that need to be distributed. $TPPL = mysql_query("SELECT * FROM npcs WHERE hq = $HQROW[id]"); $TotalPeople = mysql_num_rows($TPPL); // Total supplies to subtract is the number of people stationed there * the food amount. $TSN = $TotalPeople * $Food; if($TSN < $HQROW[foodstored]) { } // If they have enough food at the Supply Depot, let's subtract the food and give it to the members assigned to that HQ if($TSN >= $HQROW[foodstored]) { mysql_query("UPDATE buildingso SET foodstored = foodstored - $TSN WHERE id = '$HQROW[id]'"); mysql_query("UPDATE npcs SET nourishment = nourishment + $HQROW[foodlevel] WHERE hq = $HQROW[id]"); } Link to comment Share on other sites More sharing options...
justsomeguy Posted August 8, 2012 Share Posted August 8, 2012 When you have an array in a string you need to put it in curly brackets, and you should always quote the index for an associative array: $TPPL = mysql_query("SELECT * FROM npcs WHERE hq = {$HQROW['id']}"); You should quote the other array indexes you use also (foodlevel, foodstored, etc). When you set $Food, you should not quote the number. There's no reason to quote a number if you want to use it as a number (i.e., mathematically). Other than that, add some echo statements to have it tell you what it's doing. Print out the number of building so you know how many times it should loop, print out the food level, food amount, number of people, etc, just to verify what it's doing. Link to comment Share on other sites More sharing options...
Usarjjaco Posted August 13, 2012 Author Share Posted August 13, 2012 Followed your few pointers, and got everything working. I didn't quote the (foodstored) because that was a int value. Other than that, all good, so thank you! Link to comment Share on other sites More sharing options...
Usarjjaco Posted August 13, 2012 Author Share Posted August 13, 2012 To prevent from opening a second topic, is it possible to fetch an array with an array value? EG: A "While" statement queries array #1 We use one of the array values from Array #1 to select the group for array # 2 in a select statement A "While" statement queries array #2 basically I'm trying to updating two tables with one script, and I thought this would be possible, but I'm not getting the values from array # 2 to come through. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 13, 2012 Share Posted August 13, 2012 There aren't any rules in programming that limit how you can nest control structure like loops or where a given value has to come from. Link to comment Share on other sites More sharing options...
Usarjjaco Posted August 15, 2012 Author Share Posted August 15, 2012 (edited) Justsome, So theoretically, this should work? $FirstVariable = mysql_query("SELECT * FROM blah"); while($FirstWhile = mysql_fetch_array($FirstVariable)) { $SecondVariable = mysql_query("SELECT * FROM blah2 WHERE somecolumn = {$FirstWhile[othercolumn]}"); while($SecondVariable = mysql_fetch_array($SecondVariable)) { //More Code } //End Of Original While } I have to revisit my script today and get it working, but just wanted to verify that this should be completely feasible. Edited August 15, 2012 by usarjjaco Link to comment Share on other sites More sharing options...
justsomeguy Posted August 15, 2012 Share Posted August 15, 2012 Basically yeah, but you need to quote array key names: $SecondVariable = mysql_query("SELECT * FROM blah2 WHERE somecolumn = {$FirstWhile['othercolumn']}"); Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now