How do I use prepared statements with functions and classes

[Stoffer]

Hi, how do I use prepared statements when working with classes and functions? I need to do perform multiple identical inserts/updates to mySQL. I have been trying to implement into my connection class, based on what I have seen in examples I found on google. I keep getting fatal errors.

My code is (save.php)

<?php
//Class extends DB_connect to access database
class saveWorkout extends DB_Connect {
  // $stml->close();
  public function get_jQueryData($planned_workout_id,$array_input) {
    // loop through workout progress, and check if input already exists in database
    for ($i = 0; $i < count($array_input['field_id']); $i++) {
      if(empty($array_input['field_id'][$i])) {
        $planned_workout_id = $array_input['planned_id'][$i];
        $exercise_id = $array_input['exercise_id'][$i];
        $set_id = $array_input['set_id'][$i];
        $weight = $array_input['weight'][$i];
        $reps = $array_input['reps'][$i];
        $this->insertNewEntry->$stmt->execute();
      } // closing else
      else {
        // Do something
      } //closing else
    } // Closing for statement
  }

  protected function insertNewEntry() {
    $stmt = $this->$connect()->prepare("INSERT INTO Workout_Log (planned_workout_id, exercise_id, set_id, weight, reps, entry_date) VALUES ( ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param($planned_workout_id, $exercise_id, $set_id, $weight, $reps, date("Y-m-d H:i:s"));
  }
} // closing class save_workout
?>

my connection file is looks like this (connect.php)

class DB_Connect {
  private $servername;
  private $username;
  private $password;
  private $dbname;

  protected function connect() {
    $this->servername = "localhost";
    $this->username = "root";
    $this->password = "XXXXXX";
    $this->dbname = "NNNNNNN";
    $conn = new mysqli($this->servername,$this->username,$this->password,$this->dbname);
    return $conn;
  }
};

 

Edited March 25, 2020 by Stoffer

[Barand]

Your saveWorkout class has no database connection

[Stoffer]

But my saveWorkout class extends my connection class, shouldn't that be enough to do a referral? 

Furthermore, is this the appropriate way to use prepared statements? I feel there is not a lot gained from this if I create a prepared statement for every update..

[gizmola]

As to your generalized question, yes you should use prepared statements for all DML.

The issue is that you don't understand PHP classes adequately so you are missing out on some essential stuff and writing code that can't run.  For example:

 

$this->insertNewEntry->$stmt->execute();

You are trying to access a class method  'insertNewEntry' as if it was an instance variable.  It's not.  If you had a fluent design you might be able to do that, but I'm not going to get into that at present.  You should move the $stmt->execute call into the insertNewEntry() method where it belongs, and then your method call would simple be:

$this->insertNewEntry($planned_workout_id, $exercise_id, $set_id, $weight, $reps);

 Correspondingly, insertNewEntry has no parameter list, so there's no way for it to get access to all the variables it needs.  Those parameters need to be added to the method definition.  

An even bigger issue with the same method is this:

$stmt = $this->$connect()->prepare(

Again you have a number of mistakes.  This is trying to run some unknown method name stored in a non-existant $connect variable.  What you actually want is:

$stmt = $this->connect()->prepare(

This has a good chance to work, however, it's going to be pretty wasteful if you are constantly making new database connections for every query.  You would be better off, having a class variable that stores the connection, and then simply using that in all of your DML oriented methods.  

I don't think that inheritance is a great way of doing this as all your parent db class does is make a database connection with hardwired parameters.  There is no big win there.  Having a db connection class is fine, but you would be better off designing it to accept the database credentials from a configuration file.  

Your saveWorkout class would be better off using dependency injection instead, and having connection class instance injected into the class at construction.    I've recommended this series by Fabien Potencier who is the founder of the Symfony project many times over the years.  It talks about the Dependency injection design pattern and explains what it is and why it's a good way to design your classes.   Read it here:  http://fabien.potencier.org/what-is-dependency-injection.html

More likely what I would see you moving towards is an implementation of ActiveRecord which is a model/orm design pattern used by many MVC frameworks including Ruby on Rails, and in PHP frameworks like CakePHP and the very popular and modern Laravel framework. 

Your base class would then generalize select, save, update, delete methods, and you would have a derived model class for every table you deal with.   I would expect that you would have a class named 'workoutLog' that would mimic the structure of your workout_log table, with attributes that get/set all the individual properties that match your database.   You can then have generalized code in the model base class that understands how to construct the SQL needed based on the model.  Typically you have getters and setters for each column, but these can also be generalized in the base class using PHP's magic methods __get, __set and __call.  See https://www.php.net/manual/en/language.oop5.magic.php

This would allow you to quickly develop a base class that didn't require each derived model class to have all the properties enumerated for the your tables,  if you wanted to avoid that.  If you only have a handfull of tables, it might be easier just to author each model class so that it matches the structure of database table.

Browse the Laravel Eloquent documentation to get an idea how this type of thing should be structured:  https://laravel.com/docs/5.8/eloquent, and look at some of the examples to see the type of code that you write to do database manipulation with an ActiveRecord style implementation.

I realize this is a lot of material and suggestions, but then again, you could start with Symfony or Laravel and not reinvent the wheel as you are doing currently.