Best way to capture data from a query

Question

I have presented in a table of a site information of a database this way:

<?
    $sql = mysql_query("SELECT id, nome FROM pessoas");
    //---------------DADOS------------------------
    while($row = mysql_fetch_array($sql))
    {
    echo "<TABLE>";
    echo "<TR>";
    echo "<TD>".$row['id']."</TD>";
    echo "<TD>".$row['nome']."</TD>";
    echo "<TD> 
              <i class='fa fa-eye'></i>
              <i class='fa fa-pencil'></i>
              <i class='fa fa-trash'></i>  ";
    echo "</TR>";
    echo "</TABLE>";
    }

        ?>

I wanted to know somehow to pass the data when clicking on the tag icons <i> to do a query. For example the third <i> is for when you click there, do DELETE to a record but I think with the tag <form> can’t.

Answer 1

I don’t know if it’s the best way, but it’s possible to use the form tag for that. You should create a form for each option you want, such as deletion or editing. The difference is that you need an input type Submit for this, instead of the text in i.

For example:

<?php
echo "<form method='post' action='deleta.php'>";
echo "<input type='hidden' value='".$row['id']."'> ";
echo "<input type='submit' value='Deletar'>";
echo "</form>";
?>

Note that I have put ID value as a hidden input field. This way you can access it in the PHP page where the form is sent. The extra work would be in styling the input button for the way you need it.

I hope it helped.

Answer 2

There is no best way, what exists is the way that most meets your purpose, in the case below, is a simple example, using PDO:

class Database {

        private static $servidor = 'localhost'; // Servidor, no caso poderia ser também localhost
        private static $usuario = 'root'; // usuário do banco de dados
        private static $senha = 'senha'; // senha do banco de dados
        private static $banco = 'banco'; // nome do banco de dados
        private static $instance = null;

        public static function getConnection() {
            if (!self::$instance instanceof PDO) {
                try {
                    self::$instance = new PDO('mysql:host=' .
                    self::$servidor . ';dbname=' . 
                    self::$banco, 
                    self::$usuario, 
                    self::$senha);
                } catch (PDOException $exc) {
                    echo "Erro ao conectar :: {$exc->getMessage()}";
                }
            }
            return self::$instance;
        }

        public function fetchAll($query) {
            $con = self::getConnection();

            $stmt = $con->prepare($query);

            $this->execute($stmt);

            if ($stmt->rowCount()) {
                return $stmt->fetchAll(PDO::FETCH_OBJ);
            } else {
                return false;
            }
        }

        public function execute(PDOStatement $stmt, array $data = null) {
            try {
                if (isset($data)) {
                    $stmt->execute($data);
                } else {
                    $stmt->execute();
                }
            } catch (PDOException $exc) {
                echo $exc->getTraceAsString();
            }
        }

        public function action($sql, array $data) {
            $con = self::getConnection();
            $stmt = $con->prepare($sql);
            $this->execute($stmt, $data);
            if ($stmt->rowCount()) {
                return true;
            } else {
                return false;
            }
        }
}

$conexao = new Database();

//listando...
$collection = $conexao->fetchAll("SELECT * FROM tabela");

foreach ($collection as $data) {
      echo $data['campo'] . '<br>';
}

//deletando...
 $deletou = $conexao->action('DELETE FROM tabela WHERE id=:id',array('id' => (int) $id));
 if ($deletou) {
   echo "apagou!";
 }

//inserindo
$insert = $conexao->action("INSERT INTO tabela (campo1, campo2) values (:campo1,:campo2)",array('campo1'=>'valor1','campo2'=>'valor2'));
 if ($insert) {
   echo "gravou!";
 }
//atualizando
$update = $conexao->action("UPDATE tabela SET campo1=:campo1, campo2=:campo2 where id=:id",array('campo1'=>'valor1','campo2'=>'valor2','id' => (int) $id));
 if ($update) {
   echo "atualizou!";
 }