PDO Update Multiple Rows

Not too sure where I'm going wrong with it:

<form method="post" action="#">
<th>Story ID</th><th>Title</th>
<?php
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr><td><input type=\"text\" name=\"story_id[]\" value=\"" . $row['story_id'] . " \"size=4></td><td><input type=\"text\" name=\"title[]\" value=\"" . $row['title'] . "\" size=50><input type=\"hidden\" name=\"id[]\" value=\"" . $row['id'] . "\"><input type=\"hidden\" name=\"siteid[]\" value=\"" . $row['siteid'] . "\"></td></tr>";
}
?>

<input type="submit" value="Update Slider" name="submit" id="submit">
</form>

PHP:

print_r($_POST):

Array
(
[story_id] => Array
(
[0] => 2362
[1] => 2394
[2] => 2370
)

[title] => Array
(
[0] => SHOW YOUR METTLE WITH THE IRONKIDS
[1] => SCHOOL DONATIONS BOOST CHARITY
[2] => IN SAFE HANDS...
)

[id] => Array
(
[0] => 4
[1] => 3
[2] => 2
)

[siteid] => Array
(
[0] => 3
[1] => 3
[2] => 3
)

[submit] => Update Slider
)

PHP:

print_r($statement):

PDOStatement Object ( [queryString] => UPDATE slider SET title = :title[0], story_id[0] = :story_id[0] WHERE id = :id[0] AND siteid = :siteid[0] ) PDOStatement Object ( [queryString] => UPDATE slider SET title = :title[1], story_id[1] = :story_id[1] WHERE id = :id[1] AND siteid = :siteid[1] ) PDOStatement Object ( [queryString] => UPDATE slider SET title = :title[2], story_id[2] = :story_id[2] WHERE id = :id[2] AND siteid = :siteid[2] )

PHP:

Method:

$count = 3;
echo "<pre>";
print_r($_POST);
echo "</pre>";
//Check if form submitted
if
    (isset($_POST['submit']) && ($_POST['submit']) &&
    (isset($_POST['title']) && ($_POST['title']) &&
    (isset($_POST['story_id']) && ($_POST['story_id']) &&
    (isset($_POST['id']) && ($_POST['id']) &&
    (isset($_POST['siteid']) && ($_POST['siteid']))))))
    {
    for($i=0;$i<$count;$i++){
    //Update the slider
    $statement = $dbconn->prepare("
        UPDATE slider
        SET title = :title[$i], story_id[$i] = :story_id[$i]
        WHERE id = :id[$i]
        AND siteid = :siteid[$i]
    ");

    $statement->execute([
        ":title"    =>    $_POST['title'][$i],
        ":story_id"    =>    $_POST['story_id'][$i],
        ":id"    =>    $_POST['id'][$i],
        ":siteid"    =>    $_POST['siteid'][$i]
    ]);

    print_r($statement);
}
}

PHP:

 

The Update-statement shouldn't have the array-numbers - ie, it should be like this:

$statement = $dbconn->prepare("UPDATE slider SET title = :title, story_id[$i] = :story_id WHERE id = :id AND siteid = :siteid");

PHP:

You're assigning the values to the :-statements in the execute-bit, and the values you're assigning are the array-values - the assigned replacements are just the plain :-placeholders - I'm also a little unsure as to why your db-scheme has a column-setting with several columns for story-id - why not just one column, story-id, and just add the proper value to that column for each row inserted?

 

Hi thanks for your reply.

Sorry, I don't follow what you mean on this bit:

Also, I don't follow what you've done with the update statement... is story_id[$i] a mistake or did you forget to put it on the others? Either way, I've tried both and both don't work...

 

Since you didn't show us your db-setup, it's hard to follow - the point was either way to not use the $i-variable in the actual statement, so if you have a column named story_id, just remove it.
However - the best way is often to just use the actual $variables in the query when you're having problems, and echo out the query - then, if you don't get any visible errors, try the echoed query in a management console, like phpmyadmin. That will show you any errors that might prevent insertions in the database from happening.

 

Ah right maybe you're misunderstanding what this table is for... It's a slider table with a relationship to the story table using slider.story_id = stories.id so I need that in there.

Never mind, got it working.

 

You're also missing one of the entire reasons to use PREPARE... that being to prepare ONCE! Ever heard of optimizing inside the loop? That's something prepare does REALLY WELL! Also... why are you checking the same values twice in that IF statement? They're all bool too, so you don't need that many parenthesis...

echo '<pre>', print_r($_POST), '</pre>';

if (
	isset($_POST['submit'])  &&
	isset($_POST['title'])  &&
	isset($_POST['story_id'])  &&
	isset($_POST['id']) &&
	isset($_POST['siteid'])
) {

	$statement = $dbconn->prepare('
		UPDATE slider
		SET
			title = :title,
			story_id = :story_id
		WHERE id = :id
		AND siteid = :siteid
	');

	for ($i = 0; $i < count($_POST['id']); $i++) {

		$statement->execute([
			':title'    => $_POST['title'][$i],
			':story_id' => $_POST['story_id'][$i],
			':id'       => $_POST['id'][$i],
			':siteid"   => $_POST['siteid'][$i]
		]);
		print_r($statement);

	}

}

Code (markup):

Is how that should be done using prepare. Prepared queries are in fact designed for doing this -- you prepare once, execute multiple times. There is no reason to be passing $i inside the query, it doesn't work that way. Also notice I count ID to figure out how many are submitted, making the method a bit more automated/versatile.

Though I'd consider making the query data formatted a bit different. If you made them an array of arrays, count then value thus:

<input type="text" name="slider[0][:title]" />
<input type="text" name="slider[0][:story_id]" />
<input type="text" name="slider[0][:id]" />
<input type="text" name="slider[0][:siteId]" />

Code (markup):

You could simply check if $_POST['slider'] is set, and then send $_POST['slider'][$i] to the query thus:

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

	$statement = $dbconn->prepare('
		UPDATE slider
		SET
			title = :title,
			story_id = :story_id
		WHERE id = :id
		AND siteid = :siteid
	');

	for ($i = 0; $i < count($_POST['slider']); $i++) {
		$statement->execute($_POST['slider'][$i]);
		print_r($statement);
	}

}

Code (markup):

Far, FAR simpler. That's why we have prepare/execute in the first place.

 

This is what I ended up with:

//Check if admin is logged in
if(check_login() == false) {
    header("Location: /$papername/");
}
$count = 3; //How many we're updating
$name = 0; 
//Check if form submitted
if
    (isset($_POST['submit']) &&
    (isset($_POST['title']) &&
    (isset($_POST['story_id']) &&
    (isset($_POST['id']) &&
    (isset($_POST['siteid']) &&
    (!empty($_FILES['image']['name']))
    )))))
    {
    //Rename and upload the images

    for($i=0;$i<$count;$i++){
    $name++;
    //Update the slider
    $statement = $dbconn->prepare("
        UPDATE slider
        SET title = :title, story_id = :story_id
        WHERE id = :id
        AND siteid = :siteid
    ");
    foreach ($_FILES AS $file){

        //Move files
        //move_uploaded_file($file["tmp_name"][0], "" . $_SERVER['DOCUMENT_ROOT'] . "/test/" . $file["image"][0]["name"]);
        move_uploaded_file($file["tmp_name"][$i], "" . $_SERVER['DOCUMENT_ROOT'] . "/$papername/images/$name.jpg");
    }

    if ($statement->execute([
        ":title"    =>    $_POST['title'][$i],
        ":story_id"    =>    $_POST['story_id'][$i],
        ":id"        =>    $_POST['id'][$i],
        ":siteid"    =>    $_POST['siteid'][$i]
    ])){


        successMsg("Slide $i updated succesfully.");
    }else {
        errorMsg("There was a problem, please contact support.");
    }

  }
}

PHP:

But your last example is by far better so I'll change it.

 

You still really don't need all those parenthesis on your 'if' statement.... is there a reason you're doing that? usually if you are closing that many in a row like that, you're doing something wrong.

 

The above example is what I ended up with before your earlier reply. I changed it to the one you wrote. Originally, I put it in an if statement thinking that it would be easier to debug and show an error message if it didn't insert the record correctly. Probably should have returned an insert ID to check if it did it, but I thought that would be an extra step.