1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

PDO Update Multiple Rows

Discussion in 'PHP' started by scottlpool2003, Feb 25, 2014.

  1. #1
    Not too sure where I'm going wrong with it:
    SEMrush
    
    <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:
     
    Solved! View solution.
    scottlpool2003, Feb 25, 2014 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    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?
     
    PoPSiCLe, Feb 25, 2014 IP
  3. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #3
    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...
     
    scottlpool2003, Feb 25, 2014 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    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.
     
    PoPSiCLe, Feb 25, 2014 IP
  5. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #5
    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.
     
    Last edited: Feb 25, 2014
    scottlpool2003, Feb 25, 2014 IP
  6. #6
    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.
     
    deathshadow, Feb 28, 2014 IP
    scottlpool2003 likes this.
  7. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #7
    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.
     
    scottlpool2003, Feb 28, 2014 IP
  8. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,464
    Likes Received:
    1,917
    Best Answers:
    246
    Trophy Points:
    515
    #8
    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.
     
    deathshadow, Feb 28, 2014 IP
  9. scottlpool2003

    scottlpool2003 Well-Known Member

    Messages:
    1,708
    Likes Received:
    49
    Best Answers:
    9
    Trophy Points:
    150
    #9
    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.
     
    scottlpool2003, Mar 3, 2014 IP