  <?php

  //================================================================================================

  class MySQL {

    //================================================================================================
    // Function Constructor()
    //================================================================================================

    public function __construct() {}

    //================================================================================================
    // Function selectQuery()
    //================================================================================================

    public function selectQuery( $sql ) {
    
      $tableRecordset = [];
      
      // Enable error reporting
      mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

      try {
        
        // Database configuration
        $config = ['host' => 'localhost', 'username' => 'c2659384_c2659384', 'password' => 'J6HiRr4S', 'database' => 'c2659384_ai_test'];

        // Create connection
        $conn = new mysqli( $config['host'], $config['username'], $config['password'], $config['database'] );

        $result = $conn->query($sql);
          
        if (!$result) { throw new Exception("Query failed: " . $conn->error); }

        // Fetch all rows as associative array
        $all_rows = $result->fetch_all(MYSQLI_ASSOC);

        // Create the table recordset
        foreach ($all_rows as $row) {
          $tableRecordset[] = $row;
        }

        // Free result set
        $result->free();
          
      } catch (Exception $e) {
          echo "Database error: " . $e->getMessage();
          echo "Sql: " . $sql;
      } finally {
          // Close connection
          if (isset($conn)) {
              $conn->close();
          }
      }

      return $tableRecordset;
    }

    //================================================================================================
    // Function updateQuery()
    //================================================================================================

    public function updateQuery( $updateStatement, $bindParameterData, $bindParameterFormatString ) {
          
      // Enable error reporting
      mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

      try {
        
        // Database configuration
        $config = ['host' => 'localhost', 'username' => 'c2659384_c2659384', 'password' => 'J6HiRr4S', 'database' => 'c2659384_ai_test'];

        // Create connection
        $conn = new mysqli( $config['host'], $config['username'], $config['password'], $config['database'] );

        // Prepare statement
        $statement = $conn->prepare($updateStatement);
        if (!$statement)  { throw new Exception("Prepare failed: " . $conn->error); }
        
        // Bind parameters
        if (!$statement->bind_param($bindParameterFormatString, ...$bindParameterData)) {
          throw new Exception("Bind parameter failed: " . $statement->error);
        }

        // Execute the statement
        if (!$statement->execute()) {
          throw new Exception("Execute failed: " . $statement->error);
        }
      } catch (Exception $e) {
          echo "Database error: " . $e->getMessage();
          echo "bindParameterFormatString: " . print_r($bindParameterData, true);
      } finally {
          // Close connection
          if (isset($conn)) {
              $conn->close();
          }
      }
        
      // Return the number of affected rows 
      return $statement->affected_rows;
    }

    //================================================================================================
    // Function insertRecord()
    //================================================================================================

    public function insertRecord($recordStructure, $recordRecordset) {
      
      // Initialise the number of affected rows
      $affectedRows = 0;
      
      // Prepare the sql insert statement
      $insertStatement = $this->prepareInsertStatement($recordRecordset);
      
      if ( $insertStatement !== '' ) {
        
        // Prepare the sql bind parameter data
        $bindParameterData = $this->prepareInsertBindParameterData($recordRecordset);

        // Prepare the sql bind parameter format string
        $bindParameterFormatString = $this->prepareInsertBindParameterFormatString($recordStructure, $recordRecordset);
      
        // Prepare the insert statement, bind the paramters and execute  
        $mySQL = new MySQL();
        $affectedRows = $mySQL->updateQuery( $insertStatement, $bindParameterData, $bindParameterFormatString );
      }

      // Return the number of affected rows
      return $affectedRows;
    }

    //================================================================================================
    // Function updateRecord()
    //================================================================================================

    public function updateRecord($recordStructure, $recordRecordset) {
      
      // Initialise the number of affected rows
      $affectedRows = 0;
      
      // Prepare the sql update statement
      $updateStatement = $this->prepareUpdateStatement($recordRecordset);

      if ( $updateStatement !== '' ) {
        
        // Prepare the sql bind parameter data
        $bindParameterData = $this->prepareUpdateBindParameterData($recordRecordset);

        // Prepare the sql bind parameter format string
        $bindParameterFormatString = $this->prepareUpdateBindParameterFormatString($recordStructure, $recordRecordset);

        // Prepare the update statement, bind the paramters and execute  
        $mySQL = new MySQL();
        $affectedRows = $mySQL->updateQuery( $updateStatement, $bindParameterData, $bindParameterFormatString );
      }

      // Return the number of affected rows
      return $affectedRows;
    }

    //================================================================================================
    // Function prepareInsertStatement()
    //================================================================================================

    public function prepareInsertStatement($recordRecordset) {

      // Initialise the insert statement
      $insertStatement = $recordRecordset[0][$this->sectionTablePrimaryKey];


      // If a valid record id and record data have been set
      if (( $recordRecordset[0][$this->sectionTablePrimaryKey] === '0' ) && ( count($recordRecordset) === 1 )) {

        // Initialise the insert statement
        $insertStatement = "INSERT INTO %s ( %s ) VALUES ( %s )";

        // Append the name and value to the statement for each field
        $insertStatementFields = '';
        $insertStatementValues = '';
        foreach ($recordRecordset[0] as $key => $value) {
          if ( $key != $this->sectionTablePrimaryKey) {
            $insertStatementFields .= sprintf(", %s", $key);
            $insertStatementValues .= sprintf(", ?", $value);
          }
        }
        $insertStatement = sprintf($insertStatement, $this->sectionTableName, substr($insertStatementFields, 2), substr($insertStatementValues, 2));
      }

      // Return the insert statement
      return $insertStatement;
    }

    //================================================================================================
    // Function prepareInsertBindParameterData()
    //================================================================================================

    public function prepareInsertBindParameterData($recordRecordset) {

      // Initialise the data
      $data = [];

      // Create bind parameter data
      foreach ($recordRecordset[0] as $key => $value) {
        if ( $key != $this->sectionTablePrimaryKey) { $data[] = $value; }
      }

      // Return the data
      return $data;
    }

    //================================================================================================
    // Function prepareUpdateStatement()
    //================================================================================================

    public function prepareUpdateStatement($recordRecordset) {

      // Initialise the update statement
      $updateStatement = "";

      // If a valid record id has been set and one or more fields have been updated
      if (( $recordRecordset[0][$this->sectionTablePrimaryKey] > 0 ) && ( count($recordRecordset[0]) > 1 )) {

        // Initialise the update statement
        $updateStatement = "UPDATE %s SET %s WHERE %s = ?";

        // Append the update value to the statement for each updated field
        $updateStatementSetValues = '';
        foreach ($recordRecordset[0] as $key => $value) {
          if ( $key != $this->sectionTablePrimaryKey) {
            $updateStatementSetValues .= sprintf(", %s = ?", $key);
          }
        }
        $updateStatement = sprintf($updateStatement, $this->sectionTableName, substr($updateStatementSetValues, 2), $this->sectionTablePrimaryKey);
      }

      // Return the update statement
      return $updateStatement;
    }

    //================================================================================================
    // Function prepareUpdateBindParameterData()
    //================================================================================================

    public function prepareUpdateBindParameterData($recordRecordset) {

      // Initialise the data
      $data = [];
      $primaryKey = sprintf();

      // Create bind parameter data
      $recordId = 0;
      foreach ($recordRecordset[0] as $key => $value) {
        if ( $key === $this->sectionTablePrimaryKey) { $recordId = $value; } 
        if ( $key !== $this->sectionTablePrimaryKey) { $data[] = $value; }
      }
      $data[] = $recordId;

      // Return the data
      return $data;
    }

    //================================================================================================
    // Function prepareInsertBindParameterFormatString()
    //================================================================================================

    public function prepareInsertBindParameterFormatString($recordStructure, $recordRecordset) {

      // Initialise the format string
      $bindParameterFormatString = "";

      // Create the bind parameter format string for the insert statement
      $bindParameterFormatString = $this->prepareBindParameterFormatString($recordStructure, $recordRecordset);

      // Remove the format character for the primary key
      $bindParameterFormatString = substr($bindParameterFormatString, 1);

      // Return the format string
      return $bindParameterFormatString;
    }

    //================================================================================================
    // Function prepareUpdateBindParameterFormatString()
    //================================================================================================

    public function prepareUpdateBindParameterFormatString($recordStructure, $recordRecordset) {

      // Initialise the format string
      $bindParameterFormatString = "";

      // Create the bind parameter format string for the update statement
      $bindParameterFormatString = $this->prepareBindParameterFormatString($recordStructure, $recordRecordset);

      // Remove the format character for the primary key and add to the end
      $bindParameterFormatString = substr($bindParameterFormatString, 1);
      $bindParameterFormatString .= sprintf("i");

      // Return the format string
      return $bindParameterFormatString;
    }

    //================================================================================================
    // Function prepareBindParameterFormatString()
    //================================================================================================

    public function prepareBindParameterFormatString($recordStructure, $recordRecordset) {

      // Initialise the format string
      $bindParameterFormatString = "";

      // Create bind parameter format string
      foreach ($recordRecordset[0] as $key => $value) {
        switch($recordStructure[$key]['type']) {
          case 'varchar': $bindParameterFormatString .= sprintf("s"); break;
          case 'int': $bindParameterFormatString .= sprintf("i"); break;
          case 'double': $bindParameterFormatString .= sprintf("d"); break;
          default: $bindParameterFormatString .= sprintf("*"); break;
        }
      }

      // Return the format string
      return $bindParameterFormatString;
    }
  }

  //================================================================================================

?>