Jump to content

insert multiple rows into db, using switch statement


stevieontario
Go to solution Solved by mac_gyver,

Recommended Posts

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?

Link to comment
Share on other sites

  • Solution

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 by mac_gyver
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.