stevieontario Posted December 6, 2015 Share Posted December 6, 2015 Morning freaks, I have pulled multiple rows out of a mysql db and now I'm in a while loop, trying to add a couple values to each result and insert those into another table. It's not working. My current code snippet looks like so: $sql1 = "SELECT date, hour FROM table1 where date = '$somedate'"; $result1 = mysqli_query($cxn, $sql1) or die ("sql1 transpo in test failed: " . mysqli_error($cxn)); while ($row1 = mysqli_fetch_assoc($result1)){ extract($row1); $weekday = date("w", strtotime($date)); if ($weekday ==0 ) { //create variables to add to hour/date variables, and insert each corresponding set of values into another table switch($hour){ case $hour =="01": $eggs_price = $eggs_cost*0.567*$sunday_hour_01/62; $bread_price = $bread_cost*0.567*$sunday_hour_01/62; break; case $hour =="02": $eggs_price = $eggs_cost*0.567*$sunday_hour_02/62; $bread_price = $bread_cost*0.567*$sunday_hour_02/62; break; case $hour =="03": $eggs_price = $eggs_cost*0.567*$sunday_hour_03/62; $bread_price = $bread_cost*0.567*$sunday_hour_03/62; break; case $hour =="04": $eggs_price = $eggs_cost*0.567*$sunday_hour_04/62; $bread_price = $bread_cost*0.567*$sunday_hour_04/62; break; /////etc., up to hour = 24///// } //////// put query results into food table $sql2 = "INSERT IGNORE into food (date, hour, eggs_price, bread_price) VALUES ('$date', '$hour', '$eggs_price', '$bread_price')"; $result2 = mysqli_multi_query($cxn, $sql2) or die ("sql2 failed, for this reason: " . mysqli_error($cxn)); echo "<pre>hour: "; echo $hour." ".$eggs_price; echo "</pre>"; } The echoes at the end output the proper number of rows for whatever date/time I put in, which I thought would ensure that the insert would iterate through the same loop in the same way. But the insert only puts one row (the first hour) into the food table, and that's it. What am I doing wrong? Quote Link to comment Share on other sites More sharing options...
Solution mac_gyver Posted December 6, 2015 Solution Share Posted December 6, 2015 (edited) because you are using the IGNORE keyword in the INSERT query, you are probably triggering some unique index/key error. you would only use the IGNORE keyword when you want to silently ignore duplicate data and the index/key errors they cause. however, just about everything your code is doing is has a problem. the biggest problem is your food database table should NOT have columns like - eggs_price, bread_price. the data for each item, eggs, bread, ... should be a separate row in the table, with an item_id column that identifies which item the price is for. another BIG problem is the massive amount of logic you have (or are going to write) that only differs in the the values being tested/used. if you use an array to hold the weekday/hour data, all that $weekday/switch($hour) logic can be replaced with a few lines of code. lastly, what is the overall goal of doing this? you are apparently calculating prices for each hour for each day of the week a date falls on. this is derived data and you will end up with a massive amount of stored data as the number of dates increases. in general, you would not do it this way, but store the weekday/hour data in a data structure (database table, array) and calculate the price as needed. Edited December 6, 2015 by mac_gyver Quote Link to comment Share on other sites More sharing options...
stevieontario Posted December 6, 2015 Author Share Posted December 6, 2015 good answer, thanks. I was hoping I wouldn't have to revisit the whole approach, but I think you've persuaded me to do that. You are right -- why put zillions of rows into a database when I can just store a small amount of data (i.e., the constants that define the $eggs_price and $bread_price) and apply them to date/hour. thanks for your time and advice. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.