insert multiple rows into db, using switch statement

[stevieontario]

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?

[mac_gyver]

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

[stevieontario]

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.