Multiple record update php PDO

I am staring to use a new MVC framework (custom, not one of the big boys) and it some things that worked before don't work anymore. One example that I am stuck with is updating multiple database records. For example, I have the following in my DB.

id   season_name

1          Summer
2           Winter
3           Spring

In my form I have 2 inputs, a text field and a hidden field. The text field shows these records in 3 separate text fields and the hidden fields contains the id's.

<input type="text" class="form-control" name="season_name[]" value="<?php echo htmlspecialchars($rate['season_name']); ?>">
<input type="hidden" name="id[]" value="<?php echo$rate['id']; ?>">

Open in new window


Controller:

 
   public function updateAction()
    {

       // POST data to the model
        if($this->rates->updateRates($_POST)) {
            
           // if success, create a flash message and show the page again 
            Flash::addMessage('Success');
            $this->redirect(URLROOT . '/adminrates/edit');
            
        } else {
            
          // transaction failed and the form is redisplayed with the initial data from the database
            View::render('AdminReates/edit.php', [
                'rates' => $this->rates
            ]);
        }
    }

Open in new window


Model:

class Rates extends \Core\Model {
    
    public $errors = [];
    
    public function __construct($data = []) {
        
        foreach($data as $key=>$value) {
            $this->$key = $value;
        }
    }
    
    public static function getRates()
    {
        $db = static::getDB();
        
        $stmt = $db->prepare("SELECT `id`, `season_name`, `season_dates`, `rates`, `icon` FROM `seasons`");
        
        $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $stmt->execute();
        
        return $stmt->fetchAll();
        
    }
    
    public function updateRates($data)
    {
        $this->season_name = $data['season_name'];
        $this->id = $data['id'];
        
        $db = static::getDB();
        
        $stmt = $db->prepare("UPDATE `seasons` SET `season_name` = :season_name WHERE `id` = :id");
        
        $stmt->bindValue(":season_name", $this->season_name, PDO::PARAM_STR);
        $stmt->bindValue(":id", $this->id, PDO::PARAM_INT);
        
        return $stmt->execute();
    }
}

Open in new window


updateRates($data) is where I would want to create a foreach loop to update all the records but not sure how.

If I print_r($_POST) in the controller it gives me back an array of all the text field values so the data is there.

The error message I get when trying to submit the form is:

Call to a member function updateRates() on array
LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
You probably need to edit your updateRates method so that it loops through an array. Currently it expects a single array of key/value pairs so that you can extract the season_name and id from it ($_POST['season_name'], $_POST['id']). You're actually passing in the whole POST array and the sesson_name and id are arrays within that. You may also find it easier to change your HTML form, to include the ID as the season_name key. It keeps them together plus you wouldn't need a hidden input:

<input type="text" class="form-control" name="season_name[<?= $rate['id'] ?>]" value="<?php echo htmlspecialchars($rate['season_name']); ?>">

Open in new window


public function updateRates($data)
{
    $db = static::getDB();

    $stmt = $db->prepare("UPDATE `seasons` SET `season_name` = :season_name WHERE `id` = :id");
    $stmt->bindParam("season_name", $season_name, PDO::PARAM_STR);
    $stmt->bindParam("id", $id, PDO::PARAM_INT);

    try {
        foreach  ($data as $id => $season_name):
            $stmt->execute();
        endforeach;

        return true;

    } catch(Exception $e) {
        // deal with the error / log it etc.
        return false;
    }
}

Open in new window


You'd then call your method like so:

if($this->rates->updateRates($_POST['season_name'])) {
0
Black SulfurAuthor Commented:
Thank you Chris,

I have changed the input to:

<input type="text" class="form-control" name="season_name[<?= $rate['id'] ?>]" value="<?php echo htmlspecialchars($rate['season_name']); ?>">

Open in new window


Changed the controller to:

 
   public function updateAction()
    {
        if($this->rates->updateRates($_POST['season_name'])) {
            
            Flash::addMessage('Success');
            $this->redirect(URLROOT . '/adminrates/edit');
            
        } else {
            
            View::render('AdminReates/edit.php', [
                'rates' => $this->rates
            ]);
        }
    }

Open in new window


And changed the model:

            public function updateRates($data)
        {
            $db = static::getDB();

            $stmt = $db->prepare("UPDATE `seasons` SET `season_name` = :season_name WHERE `id` = :id");
            $stmt->bindParam("season_name", $season_name, PDO::PARAM_STR);
            $stmt->bindParam("id", $id, PDO::PARAM_INT);

            try {
                foreach  ($data as $id => $season_name):
                    return $stmt->execute();
                endforeach;

                return true;

            } catch(Exception $e) {
                // deal with the error / log it etc.
                return false;
            }
        }

Open in new window


But I still get an error:

Call to a member function updateRates() on array

It is referring to this line:

 if($this->rates->updateRates($_POST['season_name'])) {
0
Chris StanyonWebDevCommented:
Ahh right. Sorry - missed that. That's implying that $this->rates in your controller is actually an array of Rates instances. That makes it a little more complicated.

You'll need to loop through that array and see if a matching ID has been submitted. If it has, you'll need to call updateRates() on the single matching Rates instance, not on the array of $rates.

Leave your HTML changes as they are, but revert you model's updateRates() method back to how you had it. Now in your controller, you'll want something like this:

foreach ($this->rates as $rate):
    if key_exists( $rate->id, $_POST['season_name] ):
        $rate->updateRates([
            'id' => $rate->id,
            'season_name' => $_POST['season_name][$rate_id],
        ]);
    endif;
endforeach;

Open in new window

That should now fire the updateRates() method for each matching ID in the POST data. It is assuming you have a id property on your Rates class. Can't tell from your ctor, but I'm guessing you do.

A quick suggestion - when naming your Models, name them in the singular sense, as they tend to deal with a single instance, so Rate instead of Rates, Customer instead of Customers, Order instead of Orders etc. It justs adds a little more clarity to your code. Also name your methods accordingly - Rate->updateRate()
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Black SulfurAuthor Commented:
Thanks again, Chris. I was getting some errors from your code and I tried to fix it.

 foreach ($this->rates as $rate):
    if(key_exists($rate->id, $_POST['season_name'])):
        $rate->updateRates([
            'id' => $rate->id,
            'season_name' => $_POST['season_name'][$rate_id],
        ]);
    endif;
endforeach;

Open in new window


The error I get now is:

Trying to get property of non-object

Thanks for your suggestion of singular names in models, it is noted.
0
Chris StanyonWebDevCommented:
Hmmm. OK. That's now telling me that $rate isn't an object. I can't see from your Controller method what $this->rates actually is. We know it's an array, and I'd assumed it was as array of Objects, but that error implies that maybe it's not. Can you just var_dump out the $rate and see what it is:

foreach ($this->rates as $rate):
    var_dump($rate); 
    ...

Open in new window

I would have expected it to show that it's an instance of the Rates class, but I could be wrong.

There's also a slight typo ... it should be $_POST['season_name'][$rate->id], but only if $rate actually is an object.
0
Black SulfurAuthor Commented:
Here is the var dump

array(10) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["season_name"]=> string(11) "High Season" [1]=> string(11) "High Season" ["season_dates"]=> string(15) "Dec 18 - Jan 19" [2]=> string(15) "Dec 18 - Jan 19" ["rates"]=> string(18) "R499 pps per night" [3]=> string(18) "R499 pps per night" ["icon"]=> string(951) " " [4]=> string(951) " " } array(10) { ["id"]=> string(1) "2" [0]=> string(1) "2" ["season_name"]=> string(10) "Mid Season" [1]=> string(10) "Mid Season" ["season_dates"]=> string(15) "Feb 19 - Apr 19" [2]=> string(15) "Feb 19 - Apr 19" ["rates"]=> string(18) "R499 pps per night" [3]=> string(18) "R499 pps per night" ["icon"]=> string(1049) " " [4]=> string(1049) " " } array(10) { ["id"]=> string(1) "3" [0]=> string(1) "3" ["season_name"]=> string(10) "Low season" [1]=> string(10) "Low season" ["season_dates"]=> string(15) "May 19 - Aug 19" [2]=> string(15) "May 19 - Aug 19" ["rates"]=> string(16) "R499 pps sharing" [3]=> string(16) "R499 pps sharing" ["icon"]=> string(1165) " " [4]=> string(1165) " " }
0
Chris StanyonWebDevCommented:
Right - that's the code for the abstract Base controller, not the concrete one you're actually using.

The var_dump you've posted indicates that you don't actually have an array of Rate objects - you just have an array of arrays.

When working in an OOP way, you should really work with the classes. As it stands each $rate is nothing more than an associated array, so you can't access the properties of call the methods on that instance. I'd need to see the code for your concrete controller (the one that stores $rates and contains the updateAction() method).

My guess here is that you've simply assigned the result of Rates::getRates() to the $rates variable, rather than instantiating the Rates classes
0
Black SulfurAuthor Commented:
Have sent you a PM with the framework I am using in case it will help in any way.
0
Black SulfurAuthor Commented:
Sorry, deleted my controller by accident

namespace App\Controllers;

use App\Models\Rates;
use \Core\View;
use \App\Flash;

class AdminRates extends Authenticated {
    
    protected function before()
    {
        $this->rates = Rates::getRates();
        
    }
    
    
    public function editAction()
    {
        
        View::render('AdminRates/edit.php', [
            'rates' => $this->rates
        ]);
    }
    
    public function updateAction()
    {
        if($this->rates->updateRates($_POST)) {
            
            Flash::addMessage('Success');
            $this->redirect(URLROOT . '/adminrates/edit');
            
        } else {
            
            View::render('AdminReates/edit.php', [
                'rates' => $this->rates
            ]);
        }

Open in new window

0
Chris StanyonWebDevCommented:
Right OK. As I said, when working with OOP, it makes sense to work with Objects - that's where all the power comes in. You've written a Model for Rates, but you don't really seem to be using it. If you look at your before() method, you have this:

$this->rates = Rates::getRates();

And the static getRates() is simply querying the Database and returning an array of records - each represented as an associated array. This means that each entry in $this->rates is just an array - it has none of the power of being an Object. To benefit, the $rates property should be an array of Rates objects. It's easy enough to do. In your Model, remove the constructor and change your getRates() method to the following:

public static function getRates()
{
    $stmt = static::getDB()->prepare("SELECT `id`, `season_name`, `season_dates`, `rates`, `icon` FROM `seasons`");
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_CLASS, __CLASS__);
}

Open in new window

Now when you call getRates() you will get an array of Rates objects back. This means that when you loop through the array, you're dealing with objects and properties (->), rather than arrays and keys ([""]), so you would do:

foreach ($rates as $rate):
     echo $rate->season_name;
     echo $rate->id;
endforeach;

Open in new window

Also, because you're now working with Objects, you can also call any method from the class:

foreach ($rates as $rate):
    $rate->DoSomething();
    echo $rate->GetSomeHtml();
endforeach;

Open in new window

You can start to build all your Rates specific logic into the Rates class. You can change your updateRates method to something like the following:

public function UpdateSeason($season) {
    $this->season_name = $season;
    $stmt = static::getDB()->prepare("UPDATE `seasons` SET `season_name` = :season_name WHERE `id` = :id");
    $stmt->bindParam("season_name", $this->season_name, PDO::PARAM_STR);
    $stmt->bindParam("id", $this->id, PDO::PARAM_INT);
    return $stmt->execute();
}

Open in new window

And your Controller code to:

foreach ($this->rates as $rate):
    if key_exists( $rate->id, $_POST['season_name'] ):
        $rate->UpdateSeason( $_POST['season_name'][$rate->id] );
    endif;
endforeach;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
Thank you Chris, if you wouldn't mind, could we just break this into bits because I am a bit slow. I like what you said about using objects instead of arrays.

So, first off I changed my database query to not return an array ie: FETCH_ASSOC

    public static function getRates()
{
             
      $db = static::getDB();
      $stmt = $db->prepare("SELECT `id`, `season_name`, `season_dates`, `rates`, `icon` FROM `seasons`");
      $stmt->execute();
      return $stmt->fetchAll(PDO::FETCH_CLASS, __CLASS__);
}

Open in new window


In my view I am now instead of doing this $rate['season_name'], I am doing $rate->season_name

and that is working.

But, I still have this in my before method in the controller:

$this->rates = Rates::getRates();

and the method that displays the database records in the controller is still:

    public function editAction()
    {

        View::render('AdminRates/edit.php', [
            'rates' => $this->rates
        ]);
    }

Open in new window


So, before I go on,  is it okay to leave this as is or should that also change? The reason I like having $this->rates = Rates::getRates(); in my before() method is that I can access all methods in the Rates class by doing that. But if it is wrong I will change it.
0
Chris StanyonWebDevCommented:
Hey Black Sulphur,

When you call this:

return $stmt->fetchAll(PDO::FETCH_CLASS, __CLASS__);

instead of :

return $stmt->fetchAll(PDO::FETCH_ASSOC);

Your telling the PDO database to retrieve a collection of the current class instances instead of a collection of associated arrays, so when you call this:

$this->rates = Rates::getRates();

You are setting $this->rates to a collection of Rates instead of a collection of associated arrays. This is good, because you now have full access to all the class members.

And then in your editAction method, this code:

View::render('AdminRates/edit.php', [
            'rates' => $this->rates
]);

passes that collection of objects to the view, instead of passing the previous collection of arrays to the view. This means in the view, you're working with objects instead of arrays, so you access object properties instead of array keys : $rate->season_name instead of $rate['season_name']. The added benefit of this is that you can also access any methods on the Rate class from within your view

$rate->SomeMethod();
0
Black SulfurAuthor Commented:
Thank you Chris, I am on the same page as you now  and the code works and all records update at once! :)

I just have 2 more questions if you don't mind. Firstly, in the foreach loop in the controller I want to still set my flash message and show validation errors if there are any. I usually have an if statement for the line where I pass the data to the model. It is a bit more tricky now because I want to validate that all the fields pass validation in the loop. I tried this because I didn't want to put the flash message and redirect in the loop itself because that redirects after the first record is updated so it needs to go outside the loop but I still need the else block to handle the failed validation and reshow the view.

     
      foreach ($this->rates as $rate) {
                if (key_exists( $rate->id, $_POST['season_name'] )) {
                    $rate->updateRates( $_POST['season_name'][$rate->id] );
                }
            }
        
            Flash::addMessage('Success');
            $this->redirect(URLROOT . '/adminrates/edit');

Open in new window


So, would normally just be something like:

        if($this->rates->updateRates($_POST)) {
            
            Flash::addMessage('Success');
           $this->redirect(URLROOT . '/adminrates/edit');

        } else {
            
            View::render('AdminReates/edit.php', [
                'rates' => $this->rates
           ]);
        }

Open in new window

0
Chris StanyonWebDevCommented:
OK. There's a couple of things you can do:

$valid = true;

foreach ($this->rates as $rate) {
    if (key_exists( $rate->id, $_POST['season_name'] )) {
        if ( ! $rate->updateRates( $_POST['season_name'][$rate->id] )) {
            $valid = false;
        };
    }
}

if ($valid) {
    ...
} else {
    ...
}

Open in new window

Now your if statement will get fired based on the boolean $valid variable. Something else to look at though is the UpdateSeason() method. Currently, we set the $this->season_name at the start of the method, so even if the query fails, you've already set the property. This should be moved to the end of the method (and the bindParam will need to change):

public function UpdateSeason($season) {
    $stmt = static::getDB()->prepare("UPDATE `seasons` SET `season_name` = :season_name WHERE `id` = :id");
    $stmt->bindParam("season_name", $season, PDO::PARAM_STR);
    $stmt->bindParam("id", $this->id, PDO::PARAM_INT);
    
    if ($result = $stmt->execute()) {
        $this->season_name = $season;
    }
  
    return $result;
}

Open in new window

Now the season_name property of the class will only be updated if the query succeeded
0
Black SulfurAuthor Commented:
Thanks Chris, that works brilliantly! The second question I had was what if I need to update another field? I tried this but it updates both fields with exactly the same data:

        foreach ($this->rates as $rate) {
            if (key_exists( $rate->id, $_POST['season_name'] ) && key_exists( $rate->id, $_POST['season_dates'] )) {
                if ( ! $rate->updateRates( $_POST['season_name'][$rate->id], $_POST['season_dates'][$rate->id] )) {
                    $valid = false;
                };
            }
        }

Open in new window


I guess that is because this code in the model can't differentiate between season_name and season_dates ?

    if ($result = $stmt->execute()) {
        $this->season_name = $season;
    }

Open in new window

0
Chris StanyonWebDevCommented:
If you want to pass in two values, then you'll need to update your UpdateSeason() method to take 2 parameters and edit your query slightly:

public function UpdateSeason($season, $dates) {
    $stmt = static::getDB()->prepare("UPDATE seasons SET season_name = :season_name, season_dates = :season_dates WHERE id = :id");
    $stmt->bindParam("season_name", $season, PDO::PARAM_STR);
    $stmt->bindParam("season_dates", $dates, PDO::PARAM_STR);
    $stmt->bindParam("id", $this->id, PDO::PARAM_INT);
    
    if ($result = $stmt->execute()) {
        $this->season_name = $season;
        $this->season_dates = $dates;
    }
  
    return $result;
}

Open in new window

0
Black SulfurAuthor Commented:
Legend, that works. Thanks so much Chris, it is much appreciated. I can close this out now :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PDO

From novice to tech pro — start learning today.