PDO paging - Problems with prepare(); select();

Asked

Viewed 776 times

1

Hello, I’m having trouble printing table data.

Updating: I inserted the following lines in the file php connection.:

public function select(){   
  $sth = $this->prepare("SELECT id_prato, titulo, descricao, preco FROM prato");    
  $this->execute();     
  $result = $sth->fetchAll();   
  return $result; 
}

php connection.

// conexao banco de dados
<?php 
    class Conexao {
        private $data = array();
        //variavel da classe Base
        protected $pdo = null;

        public function __set($name, $value){
            $this->data[$name] = $value;
        }

        public function __get($name){
            if (array_key_exists($name, $this->data)) {
                return $this->data[$name];
            }

            $trace = debug_backtrace();
            trigger_error(
                'Undefined property via __get(): ' . $name .
                ' in ' . $trace[0]['file'] .
                ' on line ' . $trace[0]['line'],
                E_USER_NOTICE);
            return null;
        }

        //método que retorna a variável $pdo
        public function getPdo() {
            return $this->pdo;
        }

        //método construtor da classe
        function __construct($pdo = null) {
            $this->pdo = $pdo;
            if ($this->pdo == null)
                $this->conectar();
        }

        //método que conecta com o banco de dados
        public function conectar() {            
            $local = "localhost";
            $user = "root";
            $pass = "";
            $basename = "diner";

            try {
                $this->pdo = new PDO("mysql:host=$local;dbname=$basename",
                                "$user",
                                "$pass",
                                array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
            } catch (PDOException $e) {
                print "Error!: " . $e->getMessage() . "<br/>";
                die();
            }
        }

        //método que desconecta
        public function desconectar() {
            $this->pdo = null;
        }  

        public function select(){
            $pdo = $this->getPdo();  
            $sth = $pdo->prepare("SELECT id_prato, titulo, descricao, preco FROM prato");    
            $sth->execute();    
            $result = $sth->fetchAll();  
            return $result;
        }
    }
?>

list-dish.php

//onde será imprimido os dados
<?php 
    //inclui as bibliotecas
    require_once('conexao.php');
    //faz a canexão 
    $pdo = new Conexao();

    // determina o numero de registros que serão visualisados
    $maximo = 20;
    // armazenamos o valor da pagina atual
    $pagina = isset($_GET['pagina']) ? ($_GET['pagina']) : '1';
    // subtraimos 1, por que os registros sempre começam do 0
    $inicio = $pagina - 1;
    //multiplicamos a quantidade de registros pelo valor da pagina atual
    $inicio = $maximo * $inicio;

    $strCount = $pdo->select("SELECT COUNT(*) AS 'prato_id' FROM prato");
    $total = 0;
    if(count($strCount)){
        foreach ($strCoun as $row)  {
            // armazeno total de registros da tabela para fazer paginação
            $total = $row["id_prato"];
        }
?>

<!DOCTYPE HTML>
<html lang="pt-BR">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width" />
        <title>Pagina&ccedil;&atilde;o com PHP</title>
        <link rel="stylesheet" type="text/css" href="css/estilo.css" />
        <link rel="stylesheet" type="text/css" href="css/reset.css" />
    </head>
    <body>
        <table class="tabela1">
            <colgroup>
                <col class="coluna1"/>
                <col class="coluna2"/>
                <col class="coluna3"/>
            </colgroup>
            <caption>Pagina&ccedil;&atilde;o com PHP</caption>          
            <thead>
                <tr>
                    <th>Codigo</th>
                    <th>Municipio</th>
                    <th>UF</th>
                </tr>
            </thead>
            <tbody>
            <?php
                //se a tabela nao estiver vazia, percorremos linha por linha pegando os valores
                if(count($result)){
                    foreach ($result as $res) {
                        echo "<tr>";
                        echo "  <td>".$res['titulo']."</td>";
                        echo "  <td>".$res['descricao']."</td>";
                        echo "  <td>".$res['preco']."</td>";
                        echo "</tr>";

                    }
                }
            ?>
            </tbody>          
        </table>
        <div id="paginação">
            <?
                //determina quantos links serão adicionados e removidos
            $max_links = 6;
            // dados para os botões
            $previous = $pagina - 1;
            $next = $pagina + 1;
            // usa função "ceil" para arredondar o numero
            $pgs = ceil($total / $maximo);
            //se a tabela nao for vazia, adicionar botões
            if($pgs > 1){
                echo "<br/>";
                //botao anterior
                if($previous > 0){
                    echo "<div id='botaoprox'><a href=".$_SERVER['PHP_SELF']."?pagina=$previous><input type='submit' name='bt-enviar' id='bt-enviar' value='Anterior' class='button' /></a></div>";
                }else{
                    echo "<div id='botaoanteriorDis'><a href=".$_SERVER['PHP_SELF']."?pagina=$previous><input type='submit'  name='bt-enviar' id='bt-enviar' value='Anterior' class='button' disabled='disabled'/></a></div>";
                }
            }
            echo "div id='numpag'>";
                for($i=$pagina-$max_links; $i <= $pgs-1; $i++) {
                    if ($i <= 0){
                        //enquanto for negativo, não faz nada
                    }else{
                        //senão adiciona o link para a outra página
                        if($i == $pgs){
                        //se for o final da pagina, coloca ...
                            echo "<a href=".$_SERVER['PHP_SELF']."?pagina=".($i).">$i</a> ..."; 
                        }
                    }
                }

            ?>
        </div>
    </body>
    </html>

Internal page where the result will be displayed internal php.:

<?php 
require_once 'usuario.php';
require_once 'sessao.php';
require_once 'autenticador.php';

$aut = Autenticador::instanciar();

$usuario = null;
if ($aut->esta_logado()) {
    $usuario = $aut->pegar_usuario();
}
else {
    $aut->expulsar();
}

?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Pagina interna</title>
    </head>
    <body>
        <h1>Página interna do sistema</h1>
        <p>Você está logado como 
            <strong><?php print $usuario->getNome(); ?></strong>.
        </p>
        <p><a href="controle.php?acao=sair">Sair</a></p>

<?php include 'form-insert.php' ?>


<?php include 'lista-prato.php' ?>

</form>

</body>
</html>

Error (already solved in the answers):

Parse error: syntax error, Unexpected end of file in C: wamp www login lista-prato.php on line 1021

Update 2: It is returning the following error (already fixed in the comments):

( ! ) Fatal error: Call to Undefined method Connexion::prepare() in C: wamp www login connexion.php on line 58

Update 3:

Notice: Undefined variable: result in C:\wamp\www\PROJETOS\210DINER\site-definitivo\newdiner\login\lista-prato.php on line 54
Call Stack
#   Time    Memory  Function    Location
1   0.0529  245376  {main}( )   ..\interno.php:0
2   0.0597  315008  include( 'C:\wamp\www\login\lista-prato.php' )  ..\interno.php:34

Does anyone know where I am going wrong and how I should proceed? I have to create the method prepare()? in a function? as I did with select? or need not prepare, I can execute the select at once?

php pdo select paging

  • Prepare is a PDO method. On the line 58 leave it so: $sth = $this->$pdo->prepare("SELECT id_prato, titulo, descricao, preco FROM prato"); $this->$pdo->execute(); and not only $this. Something else in the file list-dish.php you call this method so: $pdo->select("SELECT COUNT(*) AS 'prato_id0' FROM prato");, Are you sure that’s right?

  • really, there was a 0 that shouldn’t. I changed the code to what you suggested. public function select(){ &#xA; $sth = $this->pdo->prepare("SELECT id_prato, titulo, descricao, preco FROM prato"); &#xA; $this->pdo->execute(); &#xA; $result = $sth->fetchAll(); &#xA; return $result; }&#xA; If so, it was a mistake. ( ! ) Fatal error: Call to Undefined method PDO::execute() in C: wamp www login conexao.php on line 59

  • Ops, leave this function as: http://pastebin.com/TVnECSg3 also note that in the file list-dish.php you call the method select with arguments, and in php connection. she doesn’t get.

  • errors: ( ! ) Notice: Undefined variable: Pdo in C: wamp www login conexao.php on line 58 Notice: Undefined Property via __get(): in C: wamp www login conexao.php on line 58 in C: wamp www login conexao.php on line 21 _ Fatal error: Call to a Member Function prepare() on a non-object in C: wamp www login conexao.php on line 58

  • You’re talking about COUNT ? So I got really confused..

  • Note that you call it with arguments, but you do not use parameters in the function. About the error, you have a method that already returns the variable pdo, the function getPdo use it. http://pastebin.com/kCQmHn8z

  • Okay, I’ve updated the question. Error returned: ( ! ) Notice: Undefined variable: resultado in C:\wamp\www\PROJETOS\210DINER\site-definitivo\newdiner\login\lista-prato.php on line 54

  • This variable is not defined, it was not meant to be strCount?

  • Fine, they stopped the bugs. but it’s not printing the database data!

  • I left so: http://pastebin.com/Pd0rczsK he prints: Notice: Array to string Conversion in C: wamp www PROJECTS 210DINER site-definitive newdiner login lista-prato.php on line 27 and below one of the error prints on the screen 'Array'

  • Do so: echo "<pre>" . print_r($resultados, 1) . "<pre>";

  • printed: Array ( )

  • Regardless of how you call the method select, to query who will perform will always be SELECT id_prato, titulo, descricao, preco FROM prato which is defined in the body of the function.

  • Then.. it prints an empty Array, I changed in the body of the function all select of the code call the data. ("SELECT id_prato, titulo, descricao, preco FROM prato"); http://pastebin.com/K4eeQsGg

Show 9 more comments

3 answers

3


In addition to the mistakes of syntax in the if, there is also an error in your function select:

public function select(){   
  $sth = $this->prepare("SELECT id_prato, titulo, descricao, preco FROM prato");    
  $this->execute();     
  $result = $sth->fetchAll();   
  return $result;
}

You are calling the method PDO::prepare from the $this, which causes the error Call to undefined method, it is necessary to call this method from the PDO object, in your code it is returned in the function getPdo(). Therefore, the function select should look like this:

public function select($statement){
  $pdo = $this->getPdo();  
  $sth = $pdo->prepare($statement);    
  $sth->execute();    
  $result = $sth->fetchAll();  
  return $result;
}

  • $statement that would be: ("SELECT id_prato, titulo, descricao, preco FROM prato"); when you call I declare $statement = ("SELECT id_prato, titulo, descricao, preco FROM prato"); ?

  • @Manzetti.Nis No.. let the php connection. thus: http://pastebin.com/kXK2zTwW and the list-dish.php: http://pastebin.com/EFdjnBny. I haven’t tested.

  • I think I did. http://pastebin.com/QudNWmXA - but he prints the figures as follows: http://pastebin.com/4jqNyxzx . How do I handle this data and print fields by lines? I should open a new question?

  • @Thank you. I think it would be better to open a new question yes, because this one is already quite extensive with so many "updates". = ) if possible mark one of the answers as well.

2

It is a syntax error, spoke close a key in if(count($strCount)){, change your code to:

if(count($strCount)){
    foreach ($strCoun as $row)  {
        // armazeno total de registros da tabela para fazer paginação
        $total = $row["id_prato"];
    }
}// <---- chave que faltava.

  • I did what you said and gave: Fatal error: Call to undefined method Conexao::select() in C:\wamp\www\login\lista-prato.php on line 17;

  • In the connection class there is no such method select, it must be in another, or you must add this method. @Manzetti.Denis

  • how would I do that? public function select();{&#xA;$pdo = $pdo->prepare("SELECT id_prato, titulo, descricao, preco FROM prato");&#xA;$pdo->execute();&#xA;print("Fetch all of the remaining rows in the result set:\n");&#xA;$result = $sth->fetchAll();&#xA;print_r($result);&#xA;} I’m starting php, I didn’t get the functions right.

  • More or less that the code, at the end add a return $result;, what class was he in? @Manzetti.Denis

  • i just created.. I’ll add it inside class Conexão()

  • The code is this: public function select(){ &#xA; $sth = $pdo->prepare("SELECT id_prato, titulo, descricao, preco FROM prato");&#xA; $pdo->execute();&#xA; $result = $sth->fetchAll();&#xA; return $result;&#xA;} @Manzetti.Denis.

  • Right @rray, I edited the question with the new bug.. it returned now to prepare it(); I have to create how to select it? how would I do it? in the summer.. it is necessary to prepare it?

Show 2 more comments

2

You didn’t properly close this if

if(count($strCount)){
    foreach ($strCoun as $row  {
        // armazeno total de registros da tabela para fazer paginação
        $total = $row["id_prato"]
    });

it must be this way:

if(count($strCount)){
    foreach ($strCoun as $row) {
        // armazeno total de registros da tabela para fazer paginação
        $total = $row["id_prato"]
    };
}

Browser other questions tagged php pdo select paging

You are not signed in. Login or sign up in order to post.