Difficulty in the methodology of programming and migration from mysqli to PDO

Question

Hello, everybody.

I always programmed in the Back-End part (with PHP) using mysqli, but after reading some articles and comments, I came to the conclusion that I need to change my method of programming to PDO (For security reasons and organization of codes) and in the future to use the Object Orientation paradigm, instead of my already traditional structured pattern.

My question is the following, I always made the code with mysqli of the form below:

<?php

  //SELECT QUE RETORNA OS DADOS PARA O AJAX
				$select_empresas = "SELECT * from tbl_lista_empresas order by EMPRESA";
				$lista_empresas = mysqli_query($conecta, $select_empresas);
						
				if(!$lista_empresas)
				{
					die("Erro no Banco - Erro no Select na tabela lista_empresas");
					exit;
				}
				
				
				$retorno_empresas = array();
				//PASSANDO OS DADOS DO SELECT PARA UM ARRAY
				while($linha_empresas = mysqli_fetch_object($lista_empresas))
				{					
					$retorno_empresas[] = $linha_empresas;					
				}
        
        echo json_encode($retorno_empresas);	



?>

• I usually receive a request from AJAX, and (almost always this way), I perform a query in PHP with mysqli, step the result of the query to an ARRAY and return the result of the query to AJAX in JSON format.

And so I get the data in AJAX

//Função que faz a alteração dos dados do funcionário
function alterar_empresas(alterarempresas) {
  $.ajax({
    url: "../banco/banco-sistema/pagina-cadastrar-empresas/alterar-empresas.php",
    type: "post",
    data: alterarempresas.serialize(),
    cache: false

  }).done(function(retornoempresas) {
    $.each($.parseJSON(retornoempresas), function(chave, valor) {

      $("input#cod").val(valor.COD);
      $("input#empresa").val(valor.EMPRESA);
      $("select#tributacao").val(valor.TRIBUTACAO);


    });


  }).fail(function() {
    console.log("Erro ao atualizar");
  }).always(function(retornoempresas) {
    console.log(retornoempresas);
  });
}

• I basically always use this programming pattern.

• My question may be simple for you, as I would do this same process in PHP using PDO and how I would get this data in the return of AJAX (I assume it is the same way in AJAX).

• My question is mainly on the While part in PHP.

• Thank you!

Answer 1

There’s not much mystery.

//Abre a conexão
$PDO = db_connect();

//SQL para contar o total de registros
$sql_count = "SELECT COUNT(*) AS total FROM users ORDER BY name ASC";

//SQL para selecionar os registros
$sql = "SELECT id, name, email, gender, birthdate FROM users ORDER BY name ASC";

//Conta o total de registros.
$stmt_count = $PDO->prepare($sql_count);
$stmt_count->execute();
$total = $stmt_count->fetchColumn();

//Seleciona os registros
$stmt = $PDO->prepare($sql);
$stmt->execute();

//Lista os usuário
while ($user = $stmt->fetch(PDO::FETCH_ASSOC)){
 echo $user['email'] 
}

Credits: https://github.com/marciellioliveira/crud-php-pdo/blob/master/index.php