Object Orientation Is my idea of connection correct?

Question

I want to register something in the database using object-oriented PHP (I’m actually learning) and came across a code duplication problem (every function I would have to create a new mysqli, and see if I can connect.

Then, I created a function to connect that returns a mysqli object.

class banco{
private const host = "host";
private const banco = "nome_do_banco";
private const user = "meu_usuario";
private const password = "minha_senha";

public function conectar(){
 $conectar-> new mysqli(self::host,self::user,self:password);
 if($conectar->connect_error){
  die('Falha na conexão:'.$conectar->connect_error);
 }
 return $conectar;
}

public function cadastrar($produto, $preco){
 $conectar = this->conectar();
 $sql = 'insert into '.self::banco.'.produto(produto,preco) value("'.$produto.'","'.$preco.'");';
 if($conectar->query($sql) === TRUE){
   return 'cadastrado';
 }else{
   return 'Erro: '.$conectar->error;
 }
}
}

Answer 1

Yeah, but you can get better,

1. Every time you call this->conectar() to get a new connection, you create a new instance of the connection object with the database, then at a certain point in the application, you need to run 3 querys and 3 connections will be created with the same database

2. By instantiating the class mysqli, you can pass the database name in the fourth parameter, so you don’t need to add the database to each query (INSERT INTO my_db.my_table ...)

3. In this class you have two functions, one that connects and another that performs the registration of a product, but what about the registration of users? You would have to mix the functions of products and users or create a new class and repeat the connection function

Code with some more improvement ideas:

class Banco {
    private const host = "host";
    private const banco = "nome_do_banco";
    private const user = "meu_usuario";
    private const password = "minha_senha";

    private static $conexao = null;

    public static function conectar(){
        if (self::$conexao != null) {
            return self::$conexao;
        }

        self::$conexao = new mysqli(self::host,self::user,self::password, self::banco);

        if(self::$conexao->connect_error){
            die('Falha na conexão:'.self::$conexao->connect_error);
        }

        return self::$conexao;
    }
}

class Produto extends Banco {
    public function cadastrar($produto, $preco){
        $conectar = self::conectar();

        $sql = 'insert into produto(produto,preco) value("'.$produto.'","'.$preco.'");';

        if($conectar->query($sql) === TRUE){
            return 'cadastrado';
        }else{
            return 'Erro: '.$conectar->error;
        }
    }
}

class Usuario extends Banco {
    public function cadastrar($nome, $idade){
        $conectar = self::conectar();

        $sql = 'insert into usuario(nome,idade) value("'.$nome.'","'.$idade.'");';

        if($conectar->query($sql) === TRUE){
            return 'cadastrado';
        }else{
            return 'Erro: '.$conectar->error;
        }
    }
}

I imagine you are starting programming in PHP, so I recommend you take a look at PSR-2 which is a coding style guide and that Sopt question about SQL Injection