PDO Insert OR Update

[bradba]

I'm having a problem with determining if the values should be inserted or updated.  There's a lot of information needed so I'll try to explain in the quickest way.  

 

I'm creating a grocery shopping list.  There are many variables which I have slimmed it down because there's a lot of code.  

 

The shopping list will have 3 meals per day. 

Meal 1, Meal 2, Meal 3

 

A form for each day of the week - 

Sunday - Saturday

 

I'm only showing code for 2 foods for Meal 1 -  Reason there's a lot of code.  Please keep in mind 3 foods per meal, 3 meals per day for each day of the week. 

 

The problem: 

• I want to update only if 3 values = the values in the db - userID, Day of the week, and Meal.
• If the values don't exist for the userID, Day of the week, and Meal then insert the data.
• At this time I'm using ON DUPLICATE KEY UPDATE.  It works with the unique key being the userID but the userID will be used many times based on meal and day of week. The userID will be used a total of 3 times per day, 21 times per week as you an see I can't use ON DUPLICATE KEY UPDATE.  
• I would like to compare userID, Day of the week, and Meal and either insert or update based on the comparison.

 

I'm not sure how to do the comparison as explained above using PDO.  Here's what I have so far -

<form method="post" action="">
              
<input type="hidden" name="dayofweek" id="dayofweek" value="Sunday">
            
 <h6><strong>Meal 1</strong></h6>
               
<select name="meatSundayMealOne">

<option value="<?php if(isset($errorsmealone)){ echo $_POST['meatSundayMealOne']; } ?>"></option>
       <?php foreach ((array)$data as $row) :
    
      <option value="<?php echo $themeal = '1' ?>,<?php echo $row['meatID'] ?>,<?php echo $row['meat'] ?>,<?php echo $row['meatAmount'] ?>,
      <?php echo $row['meatAmountLabel'] ?>"><?php echo $row['meat'] ?></option>
      <?php endforeach ?>
</select>

<select name="fruitSundayMealOne">

<option value="<?php if(isset($errorsmealone)){ echo $_POST['fruitSundayMealOne']; } ?>"></option>
       <?php foreach ((array)$data as $row) :
    
      <option value="<?php echo $themeal = '1' ?>,<?php echo $row['fruitID'] ?>,<?php echo $row['fruit'] ?>,<?php echo $row['fruitAmount'] ?>,
      <?php echo $row['fruitAmountLabel'] ?>"><?php echo $row['meat'] ?></option>
      <?php endforeach ?>
</select>
               
 <input type="submit" name="sunday_meals" value="Save">
</form>

if(isset($_POST['sunday_meals'])){
     
           
     if(empty($_POST['meatSundayMealOne'])){
            $errorsmealone[] = 'Select a Meat for Meal One';
        
            
        } else {
        
    
        }
        
         if(empty($_POST['fruitSundayMealOne'])){
            $errorsmealone[] = 'Select a Fruit for Meal One';
            
            
        } else {
    
        }
        
    
    
    $userID = $_SESSION['userID'];
    $dayofweek =  $_POST['dayofweek'];
        

   if(!isset($errorsmealone)){
   
 try {
    
   $msmo=explode(",",$_POST['meatSundayMealOne']);
 
   $themeal = trim($msmo[0]);
   $meatID = trim($msmo[1]); 
   $meat = trim($msmo[2]);   
   $meatAmount = trim($msmo[3]);
   $meatAmountLabel = trim($msmo[4]);
   
   
  $fsmo=explode(",",$_POST['fruitSundayMealOne']);
  $themeal = trim($fsmo[0]);
  $fruitID = trim($fsmo[1]); 
  $fruit = trim($fsmo[2]);
  $fruitAmount = trim($fsmo[3]);
  $fruitAmountLabel = trim($fsmo[4]);
 
 
$stmt = $db->prepare('INSERT INTO grocerylist (themeal,dayofweek,meatID,meat,meatAmount,meatAmountLabel,fruitID,fruit,fruitAmount,
fruitAmountLabel,userID,creatDate) VALUES (:themeal, :dayofweek, :meatID, :meat, :meatAmount, :meatAmountLabel, :fruitID, :fruit, :fruitAmount, 
:fruitAmountLabel, :userID, now()) ON DUPLICATE KEY UPDATE themeal=:themeal,dayofweek=:dayofweek,meatID=:meatID,meat=:meat,meatAmount=:meatAmount,
meatAmountLabel=:meatAmountLabel,fruitID=:fruitID,fruit=:fruit,fruitAmount=:fruitAmount,fruitAmountLabel=:fruitAmountLabel,updateDate=now()');
      
               
 $stmt->bindParam(':themeal', $themeal, PDO::PARAM_INT);   
 $stmt->bindParam(':dayofweek', $dayofweek, PDO::PARAM_STR,20);            
 $stmt->bindParam(':meatID', $meatID, PDO::PARAM_INT);   
 $stmt->bindParam(':meat', $meat, PDO::PARAM_STR,20);
 $stmt->bindParam(':meatAmount', $meatAmount, PDO::PARAM_INT);
 $stmt->bindParam(':meatAmountLabel', $meatAmountLabel, PDO::PARAM_STR,20);
 $stmt->bindParam(':fruitID', $fruitID, PDO::PARAM_INT);   
 $stmt->bindParam(':fruit', $fruit, PDO::PARAM_STR,20);
 $stmt->bindParam(':fruitAmount', $fruitAmount, PDO::PARAM_INT);
 $stmt->bindParam(':fruitAmountLabel', $fruitAmountLabel, PDO::PARAM_STR,20);
 $stmt->bindParam(':userID', $userID, PDO::PARAM_INT);
 $stmt->execute();
 } catch(PDOException $e) {
   $errorsmealone[] = $e->getMessage();
   exit();
 }
     
   }
    
}

Edited September 28, 2015 by bradba

[mac_gyver]

I would like to compare userID, Day of the week, and Meal and either insert or update based on the comparison

 

 

you would add a unique composite key to your database table consisting of those three columns.

[mac_gyver]

as to having a huge amount of code, you should be dynamically producing the forms and dynamically processing the form data.

 

for the form you posted above, you have a hidden field that indicates that day of the week. you will know that all the form fields correspond to that day. you don't need to include the day as part of the form field name. you have two (or more) types of items being selected - meat, fruit. the form field name should just be that type. you are doing this for three meals a day. if you use an array name for the select name, you can use the meal number as the array index, i.e. name='meat[1]', name='meat[2]', name='meat[3]', same for fruit[1], fruit[2], fruit[3] and any other types. if you have a list of the types, you would loop over that list as well, rather than hand-coding all of this out. the submit button doesn't need to be named because you have the hidden field with the name.

 

see this example code that produces 7 days of forms, with 3 meals per day, with whatever different types you have -

$days = array('Sunday','Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ,'Saturday');
$num_meals = 3;
$types = array('meat','fruit');

foreach($days as $day){
    echo "<h4>$day</h4>";
    echo "<form method='post' action=''>";
    echo "<input type='hidden' name='dayofweek' value='$day'>";
    foreach(range(1,$num_meals) as $meal_number){
        echo "<h5>Meal $meal_number</h5>";
        foreach($types as $type){
            echo "$type: ";
            echo "<select name='{$type}[$meal_number]'>";
            
            echo "<option>build your option list in a loop using the variables present - \$day, \$type, \$meal_number to select which data to use</option>";

            echo "</select><br>";
        }
    }
    echo "<input type='submit' value='Save'>";
    echo "</form>";
}

if you in fact want only ONE form around all the days, you wouldn't use the hidden field with the day in it. you would add a second dimension to the form field array names. the first dimension would hold the day name (the day number would be better). the second dimension would be the meal number.

Edited September 28, 2015 by mac_gyver

[bradba]

Thanks mac_gyver for all the code.  That was going to be my next post on how to code this better.  I'll give it a try.... Thank you. 

 

 

you would add a unique composite key to your database table consisting of those three columns.

 

I was hoping that would work but since Day of the Week and Meal id are not unique I get an error < everyone will have a userID which is unique, Day of the Week which is not unique, and meal id which is not unique ....

adding unique to Day of the Week and Meal ID ... I get > Error in processing request #1062 - Duplicate entry 'Sunday' for key 'dayofweek' .... because each id will have Sunday - Saturday.  Same for Meal ID. 

 

Maybe you have a different way to do this? 

[iarp]

You need to create a unique constraint on all 3 columns at the same time. That would prevent you from adding a row such as userid=1, day=2, meal=1 twice. It would throw a constraint type error if you tried to.

 

http://stackoverflow.com/a/635943

mysql> ALTER TABLE bradba ADD UNIQUE `uq_userdatemeal_ids`(`userid`, `dateid`, `mealid`);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO bradba (userid, dateid, mealid) VALUES (1, 2, 1);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO bradba (userid, dateid, mealid) VALUES (1, 2, 1);
ERROR 1062 (23000): Duplicate entry '1-2-1' for key 'uq_userdatemeal_ids'

mysql> INSERT INTO bradba (userid, dateid, mealid) VALUES (1, 2, 2);
Query OK, 1 row affected (0.05 sec)

Edited September 28, 2015 by iarp

[Jacques1]

mac_gyver meant a composite unique key spanning all three columns, not three individual unique keys. As in:

ALTER TABLE yourtable ADD CONSTRAINT UNIQUE (yourfirstcolumn, yoursecondcolumn, yourthirdcolumn);

[bradba]

Thanks.  I'm using phpmyadmin and I created tables manually rather than using SQL.... I'm a beginner here ... plus the tables are created already.   Do I need to recreate the table or can I do this manually in phpmyadmin? 

[Jacques1]

You can add unqiue constraints at any time. Both iarp and I gave you the exact query syntax, so all you have to do is fill in your actual column names and run the query.

 

Since you didn't check for unique combinations in the past, you may have garbage data which prevents the constraint from being installed. In that case you need to repair your data first.

[bradba]

Yes I used the query and it wasn't working.  But I cleared  the table and ran the query and now it works.  You guys are the best.  Thanks for the help.   I wish I could give all three of you guys the credit for the best answer. 

[bradba]

as to having a huge amount of code, you should be dynamically producing the forms and dynamically processing the form data.

 

for the form you posted above, you have a hidden field that indicates that day of the week. you will know that all the form fields correspond to that day. you don't need to include the day as part of the form field name. you have two (or more) types of items being selected - meat, fruit. the form field name should just be that type. you are doing this for three meals a day. if you use an array name for the select name, you can use the meal number as the array index, i.e. name='meat[1]', name='meat[2]', name='meat[3]', same for fruit[1], fruit[2], fruit[3] and any other types. if you have a list of the types, you would loop over that list as well, rather than hand-coding all of this out. the submit button doesn't need to be named because you have the hidden field with the name.

 

see this example code that produces 7 days of forms, with 3 meals per day, with whatever different types you have -

$days = array('Sunday','Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' ,'Saturday');
$num_meals = 3;
$types = array('meat','fruit');

foreach($days as $day){
    echo "<h4>$day</h4>";
    echo "<form method='post' action=''>";
    echo "<input type='hidden' name='dayofweek' value='$day'>";
    foreach(range(1,$num_meals) as $meal_number){
        echo "<h5>Meal $meal_number</h5>";
        foreach($types as $type){
            echo "$type: ";
            echo "<select name='{$type}[$meal_number]'>";
            
            echo "<option>build your option list in a loop using the variables present - \$day, \$type, \$meal_number to select which data to use</option>";

            echo "</select><br>";
        }
    }
    echo "<input type='submit' value='Save'>";
    echo "</form>";
}

if you in fact want only ONE form around all the days, you wouldn't use the hidden field with the day in it. you would add a second dimension to the form field array names. the first dimension would hold the day name (the day number would be better). the second dimension would be the meal number.

 

 

This code works except I can't figure out how to use the code below with your code.  I've added and messed with it for hours but can't seem to get it work.  Basically it's not populating the comboboxes with the values coming from the db.  I can't even get it to do that.  Plus I need to tell it to print the values for the meat for the meat combobox and fruit for the fruit combobox. The orginal code -

<?php foreach ((array)$data as $row) :
    
      <option value="<?php echo $themeal = '1' ?>,<?php echo $row['fruitID'] ?>,<?php echo $row['fruit'] ?>,<?php echo $row['fruitAmount'] ?>,
<?php echo $row['fruitAmountLabel'] ?>"><?php echo $row['meat'] ?></option>
      <?php endforeach ?>

I've tried many ways but the latest - 

$data = array($meat,$fruit,$vegs);

and tried a nested foreach

      foreach($types as $type){
            echo "$type: ";
            echo "<select name='{$type}[$meal]'>";
            foreach($data as $row) 
            echo "<option value='$day,$type,$meal_number'>$row </option>";

            echo "</select><br>";
        }

Edited September 28, 2015 by bradba

[mac_gyver]

rather than to try and make your existing code 'work' (the comma separated list you are showing as a value makes no sense - the type and the meal number is known from the select name and don't belong in each option value), just define what data you have and what result you want to produce from that data.

 

i gather that $data is an array that somehow contains the possible choices for meat, fruit, and vegs? your goal would be to produce the <option></option> list for for each type, using the $type variable to select what part of the $data to use.  the value='...' attribute that you produce should be just be an identifier for the choice. the display label should indicate to the user what each choice means.

 

if the option list is always the same for any type, all three meals each day and all 7 days of the week are the same, you would produce the option choices ONCE, store the result in a php array variable, using the type as the array index, then simply output them when needed, using the $type variable to select which one you output.

 

so, what does the $data array look like?

[bradba]

 

i gather that $data is an array that somehow contains the possible choices for meat, fruit, and vegs?

 

Yes meat - hamburger, pork chops,....... fruit - oranges, bananas, ....... vegs -  all the vegetables.... 

 

 

if the option list is always the same for any type, all three meals each day and all 7 days of the week are the same, you would produce the option choices ONCE, store the result in a php array variable, using the type as the array index, then simply output them when needed, using the $type variable to select which one you output.

 

Yes the options for meat, fruit, and vegetables will be the same for all meals and days of the week. 

 

 

so, what does the $data array look like?

 

If you're asking what did it look like before adding your code it was like this (look below) -  I had it like this because I had meat, fruit, vegs in separate combos and populated them like that but as you know that takes a lot of code for all these meals and days.....

Meat and vegs combos would be the same but with $row['meatID'], $row['meat']etc etc.  Located in each combo.  

<?php foreach ((array)$data as $row) :
    
      <option value="<?php echo $themeal = '1' ?>,<?php echo $row['fruitID'] ?>,<?php echo $row['fruit'] ?>,<?php echo $row['fruitAmount'] ?>,
<?php echo $row['fruitAmountLabel'] ?>"><?php echo $row['meat'] ?></option>
      <?php endforeach ?>

I tried to make it like your foreach code removing (array) and adding it like this -  $data = array($meat,$fruit,$vegs); like you did for the other values but it's not populating the combos.  Arrays really confuse me but looking at your code it simplified it just not sure how to add it to populate the combos from the db.  I also tried $row['fruitID'] etc...... but doesn't populate...

Edited September 29, 2015 by bradba

[bradba]

Got the combo to populate .... a step closer....   As you can see I inserted the variable meat....  Of course don't  want to do that but wanted to populate it which was the goal.  I'm stuck on how to use $type to select the correct $data variable.   And that is the many different variables I need for the value.  Meaning I need meatID, meat, meatAmount, meatAmountLabel.....  and the same for fruit etc.   to insert or update the db table.

foreach($data as $row)
 echo "<option>$row[meat]</option>";

Edited September 29, 2015 by bradba