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();
}
}
}
