Error: SQLSTATE [42000]: Syntax error or access violation: 1064

Question

Error: SQLSTATE [42000]: Syntax error or access violation: 1064

Navigation

#1 by (1 votes)

1

I get an error when wanting to generate an action like modify or save .

Error:

SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE code =' 1002 '' at line 6

Code:

<?php
require_once 'producto.php';
require_once 'productoalta.php';

// Logica
$alm = new productos();
$model = new productosalta();

if(isset($_REQUEST['action']))
{
    switch($_REQUEST['action'])
    {
        case 'actualizar':
            $alm->__SET('codigo',              $_REQUEST['codigo']);
            $alm->__SET('producto',          $_REQUEST['producto']);
            $alm->__SET('descripcion',        $_REQUEST['descripcion']);
            $alm->__SET('precio',            $_REQUEST['precio']);


            $model->Actualizar($alm);
            header('Location: index.php');
            break;

        case 'registrar':
            $alm->__SET('producto',          $_REQUEST['producto']);
            $alm->__SET('descripcion',        $_REQUEST['descripcion']);
            $alm->__SET('precio',            $_REQUEST['precio']);


            $model->Registrar($alm);
            header('Location: index.php');
            break;

        case 'eliminar':
            $model->Eliminar($_REQUEST['codigo']);
            header('Location: index.php');
            break;

        case 'editar':
            $alm = $model->Obtener($_REQUEST['codigo']);
            break;
    }
}

?>

<!DOCTYPE html>
<html lang="es">
    <head>
        <title>si</title>
        <link rel="stylesheet" href="http://yui.yahooapis.com/pure/0.5.0/pure-min.css">
    </head>
    <body style="padding:15px;">

        <div class="pure-g">
            <div class="pure-u-1-12">

                <form action="?action=<?php echo $alm->codigo > 0 ? 'actualizar' : 'registrar'; ?>" method="post" class="pure-form pure-form-stacked" style="margin-bottom:30px;">
                    <input type="hidden" name="codigo" value="<?php echo $alm->__GET('codigo'); ?>" />

                    <table style="width:500px;">
                        <tr>
                            <th style="text-align:left;">producto</th>
                            <td><input type="text" name="producto" value="<?php echo $alm->__GET('producto'); ?>" style="width:100%;" /></td>
                        </tr>
                        <tr>
                            <th style="text-align:left;">descripcion</th>
                            <td><input type="text" name="descripcion" value="<?php echo $alm->__GET('descripcion'); ?>" style="width:100%;" /></td>
                        </tr>
                        <tr>
                            <th style="text-align:left;">precio</th>


                                    <td><input type="text" name="precio" value="<?php echo $alm->__GET('precio'); ?>" style="width:100%;" /></td>
                                </select>
                            </td>
                        </tr>

                        <tr>
                            <td colspan="2">
                                <button type="submit" class="pure-button pure-button-primary">Guardar</button>
                            </td>
                        </tr>
                    </table>
                </form>

                <table class="pure-table pure-table-horizontal">
                    <thead>
                        <tr>
                            <th style="text-align:left;">producto</th>
                            <th style="text-align:left;">descripcion</th>
                            <th style="text-align:left;">precio</th>
                            <th style="text-align:left;">Nacimiento</th>
                            <th></th>
                            <th></th>
                        </tr>
                    </thead>
                    <?php foreach($model->Listar() as $r): ?>
                        <tr>
                            <td><?php echo $r->__GET('producto'); ?></td>
                            <td><?php echo $r->__GET('descripcion'); ?></td>
                            <td><?php echo $r->__GET('precio') == 1 ? 'H' : 'F'; ?></td>

                            <td>
                                <a href="?action=editar&codigo=<?php echo $r->codigo; ?>">Editar</a>
                            </td>
                            <td>
                                <a href="?action=eliminar&codigo=<?php echo $r->codigo; ?>">Eliminar</a>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                </table>     

            </div>
        </div>

    </body>
</html>

Code 2

<?php
class productosalta
{
    private $pdo;

    public function __CONSTRUCT()
    {
        try
        {
            $this->pdo = new PDO('mysql:host=localhost;dbname=carro_compras', 'root', '');
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                
        }
        catch(Exception $e)
        {
            die($e->getMessage());
        }
    }

    public function Listar()
    {
        try
        {
            $result = array();

            $stm = $this->pdo->prepare("SELECT * FROM productos");
            $stm->execute();

            foreach($stm->fetchAll(PDO::FETCH_OBJ) as $r)
            {
                $alm = new productos();

                $alm->__SET('codigo', $r->codigo);
                $alm->__SET('producto', $r->producto);
                $alm->__SET('descripcion', $r->descripcion);
                $alm->__SET('precio', $r->precio);


                $result[] = $alm;
            }

            return $result;
        }
        catch(Exception $e)
        {
            die($e->getMessage());
        }
    }

    public function Obtener($codigo)
    {
        try 
        {
            $stm = $this->pdo
                      ->prepare("SELECT * FROM productos WHERE codigo = ?");


            $stm->execute(array($codigo));
            $r = $stm->fetch(PDO::FETCH_OBJ);

            $alm = new productos();

            $alm->__SET('codigo', $r->codigo);
            $alm->__SET('producto', $r->producto);
            $alm->__SET('descripcion', $r->descripcion);
            $alm->__SET('precio', $r->precio);


            return $alm;
        } catch (Exception $e) 
        {
            die($e->getMessage());
        }
    }

    public function Eliminar($codigo)
    {
        try 
        {
            $stm = $this->pdo
                      ->prepare("DELETE FROM productos WHERE codigo = ?");                    

            $stm->execute(array($codigo));
        } catch (Exception $e) 
        {
            die($e->getMessage());
        }
    }

    public function Actualizar(productos $data)
    {
        try 
        {
            $sql = "UPDATE productos SET 
                        producto          = ?, 
                        descripcion        = ?,
                        precio            = ?, 

                    WHERE codigo = ?";

            $this->pdo->prepare($sql)
                 ->execute(
                array(
                    $data->__GET('producto'), 
                    $data->__GET('descripcion'), 
                    $data->__GET('precio'),

                    $data->__GET('codigo')
                    )
                );
        } catch (Exception $e) 
        {
            die($e->getMessage());
        }
    }

    public function Registrar(productos $data)
    {
        try 
        {
        $sql = "INSERT INTO productos (producto,descripcion,precio) 
                VALUES (?, ?, ?, ?)";

        $this->pdo->prepare($sql)
             ->execute(
            array(
                $data->__GET('producto'), 
                $data->__GET('descripcion'), 
                $data->__GET('precio'),

                )
            );
        } catch (Exception $e) 
        {
            die($e->getMessage());
        }
    }
}
?>

Code 3

<?php
class productos
{


    private $codigo;
    private $producto;
    private $descripcion;
    private $precio;


    public function __GET($k){ return $this->$k; }
    public function __SET($k, $v){ return $this->$k = $v; }
}
?>

mysql php pdo mariadb

asked by Luchoo Asspero 15.12.2016 в 07:43

source

1 answer

How to get the Id of the last record inserted in MongoDB and C #? Doubt with Unit Tests Junit

score 1 · Answer 1

When you are in emulation mode PDO::ATTR_EMULATE_PREPARES (which is enabled by default), PDO replaces position markers (Placeholder) with current data.

And with the lazy linkage (using array in execute() ), PDO treats each parameter as a string (string) .

As an example, the limit prepared ?,? in the query becomes LIMIT '10', '10' which is an invalid syntax that causes the query to fail.

There are two solutions:

Disable emulation (MySQL can sort all placeholders (placeholder) correctly).
Link the number explicitly and set the appropriate type (PDO::PARAM_INT) for that variable.

To disable the emulation, this code can be executed (or set in an array of connection options):

$conn->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

Or to link these variables explicitly with the type of parameter:

$stm = $pdo->prepare('SELECT * FROM table LIMIT ?, ?');
$stm->bindParam(1, $limit_from, PDO::PARAM_INT);
$stm->bindParam(2, $per_page, PDO::PARAM_INT);
$stm->execute();
$data = $stm->fetchAll();

Original Source in English

Solution in your case adds:

$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);