PHP prepared statement insert from a loop of data

Question

Any ideas as to why my data isn't being updated? Is their something fundamentally wrong with how I'm writing my prepared statement?

The form:

while($log_dates = mysqli_fetch_assoc($q_log_dates_result)) {   
 echo "<tr>";
 echo "<input type='hidden' name='data[][log_dates_ID]' value='" . $log_dates['log_dates_ID'] . "'/>";
 echo "<td><input type='text' name='data[][week_date]' value='" . $log_dates['week_date'] . "' /></td>";
 echo "<td><input type='text' name='data[][crew_chief]' value='" . $log_dates['crew_chief'] . "' readonly /></td>";
 echo "<td><input type='text' name='data[][monday_crew]' value='". $log_dates["monday_crew"] ."'/></td>";
 echo "</tr>";
} // end while loop

PHP:

if (isset($_POST['submit'])) {

$stmt = $connection->stmt_init();
if($stmt->prepare("UPDATE log_dates SET (week_date, crew_chief, monday_crew) VALUES (?, ?, ?) WHERE log_dates_ID = ?")) {

// Bind your variables to replace the ?s
$stmt->bind_param('sssi', $week_date, $crew_chief, $monday_crew, $log_dates_ID);


$returnedData = $_POST['data'];



for($i=0;$i<count($returnedData);$i+=4){
    $log_dates_ID = $returnedData[$i]['log_dates_ID'];
    $week_date = $returnedData[$i+1]['week_date'];
    $crew_chief = $returnedData[$i+2]['crew_chief'];
    $monday_crew = $returnedData[$i+3]['monday_crew'];
    $stmt->execute();
}


    // Close statement object
    $stmt->close();
}


}

Barmar · Accepted Answer · 2013-07-10 17:46:22Z

2

Your UPDATE syntax is not correct. It should be:

UPDATE log_dates
SET week_date = ?, crew_chief = ?, monday_crew = ?
WHERE log_dates_ID = ?

You're trying to user INSERT syntax in an UPDATE statement. They're not similar at all.

Documentation

edited Jul 10, 2013 at 17:46

answered Jul 10, 2013 at 17:25

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1040259 Over a year ago

Thanks for your reply, however I don't think that's correct because I'm using the same method for inserting data into my db and it works great. Just not updating (code above)

user1040259 Over a year ago

Thank you! close.. now "null" is being updated to my field from a POST. Need to figure that out now :(

Kylie · Accepted Answer · 2013-07-10 17:28:06Z

1

I would do it like this...

Inside a function...

function updatedata($week_date, $crew_chief, $monday_crew, $log_dates_ID){
 $stmt = $connection->stmt_init();
 $stmt->prepare("UPDATE log_dates SET (week_date, crew_chief, monday_crew) VALUES (?, ?, ?) WHERE log_dates_ID = ?")) 
 $stmt->bind_param($week_date, $crew_chief, $monday_crew, $log_dates_ID);
 $stmt->execute();
}

Then in your loop...

for($i=0;$i<count($returnedData);$i+=4){
$log_dates_ID = $returnedData['log_dates_ID'][$i];
$week_date = $returnedData['week_date'][$i];
$crew_chief = $returnedData['crew_chief'][$i];
$monday_crew = $returnedData['monday_crew'][$i];
updatedata($week_date, $crew_chief,$monday_crew, $log_dates_ID);
}

edited Jul 10, 2013 at 17:28

answered Jul 10, 2013 at 16:16

Kylie

11.8k11 gold badges51 silver badges81 bronze badges

6 Comments

Kylie Over a year ago

Well I noticed you have 'sssi' in your update, thats an extra value to bind. You only have 4 ? marks though

Kylie Over a year ago

This bind_param('sssi', $week_date, $crew_chief, $monday_crew, $log_dates_ID); should be bind_param($week_date, $crew_chief, $monday_crew, $log_dates_ID);

Kylie Over a year ago

Well you are binding 5 values, to 4 question marks, thats your problem right there.....

user1040259 Over a year ago

Thanks KyleK for your time. Sorry I'm slow to catch on. I have 4 POST values and SSSI also totals 4. No? Where's the extra 5th value?

Kylie Over a year ago

Well $week_date binds to week_date, $crew_chief binds to crew_chief, $monday_crew binds to monday_crew, $log_dates_ID binds to log_dates_ID, but 'ssssi'?? whats that binding to?? It should be removed. Cuz as you have it now, your update will bind 'sssi' to week_date, and all the others are being offset. ($week_date is being bound to crew_chief, $crew_chief is bound to monday_crew etc etc)

|

Collectives™ on Stack Overflow

PHP prepared statement insert from a loop of data

2 Answers 2

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related