Help to build a mySql query with PHP

Navigation
0

I've been trying to find the error of this PHP script for a week. What it does is to construct an 'INSERT' query taking into account the fields filled in by the user in a form. The error it throws is this:

  

Error at line: 470

     

Error message: SQLSTATE [HY093]: Invalid parameter number: parameter was not defined

The code is as follows: 

require_once('../../../../validadores_datos_usuario/datos_anuncios/generate_id.php'); // para usar la función dameId(), que devuelve un código alfanumérico 
require(realpath($_SERVER["DOCUMENT_ROOT"]) .'/qbc_web/datos_conexion.php'); // datos de conexión con la base de datos 

try {
    session_start();

$user = $_SESSION['usuario'];
$ide = dameId();


if(isset($_POST['btn-anadirInventario'])){
    $action = 1;

$c_query = "INSERT INTO inventory (Inv_Id,Inv_User";

$v_query = "(:ide ,:user";

if(isset($_POST['ref']) && $_POST['ref'] != ""){
    $c_query .= ",Inv_Ref";
    $ref = $_POST['ref'];
    $v_query .= ", :ref ";
    $checkRef = 1;  
}
if(isset($_POST['nombre']) && $_POST['nombre'] != ""){
    $c_query .= ",Inv_Name";
    $nombre = $_POST['nombre'];
    $v_query .= ", :nombre ";
    $checkNombre = 1;
}else{
    $nombre = "";
}
if(isset($_POST['desc']) && $_POST['desc'] != ""){
    $c_query .= ",Inv_Desc";
    $desc = nl2br($_POST['desc']);
    $v_query .= ", :desc ";
    $checkDescription = 1;
}
if(isset($_POST['grupo']) && $_POST['grupo'] != ""){
    $c_query .= ",Inv_Group";
    $grupo = $_POST['grupo'];
    $v_query .= ", :grupo ";
    $checkGrupo = 1;
}
if(isset($_POST['tipo']) && $_POST['tipo'] != ""){
    $c_query .= ",Inv_Type";
    $tipo = $_POST['tipo'];
    $v_query .= ", :tipo ";
    $checkTipo = 1;
}
if(isset($_POST['marca']) && $_POST['marca'] != ""){
    $c_query .= ",Inv_Brand";
    $marca = $_POST['marca'];
    $v_query .= ", :marca ";
    $checkMarca = 1;
}
if(isset($_POST['modelo']) && $_POST['modelo'] != ""){
    $c_query .= ",Inv_Model";
    $modelo = $_POST['modelo'];
    $v_query .= ", :modelo ";
    $checkModelo = 1;
}
if(isset($_POST['n_compra']) && $_POST['n_compra'] != ""){
    $c_query .= ",Inv_Purch_Num";
    $n_compra = $_POST['n_compra'];
    $v_query .= ", :n_compra ";
    $checkNumeroCompra = 1;
}
if(isset($_POST['rubro']) && $_POST['rubro'] != ""){
    $c_query .= ",Inv_Item";
    $rubro = $_POST['rubro'];
    $v_query .= ", :rubro ";
    $checkRubro = 1;
}
if(isset($_POST['presentacion']) && $_POST['presentacion'] != ""){
    $c_query .= ",Inv_Pres";
    $presentacion = $_POST['presentacion'];
    $v_query .= ", :presentacion ";
    $checkPresentacion = 1;
}
if(isset($_POST['peso']) && $_POST['peso'] != ""){
    $c_query .= ",Inv_Weight";
    $peso = $_POST['peso'];
    $v_query .= ", :peso ";
    $checkPeso = 1;
}
if(isset($_POST['unidad_peso']) && $_POST['unidad_peso'] != ""){
    $c_query .= ",Inv_Weight_Un";
    $unidad_peso = $_POST['unidad_peso'];
    $v_query .= ", :unidad_peso ";
    $checkUnidadPeso = 1;
}
if(isset($_POST['volumen']) && $_POST['volumen'] != ""){
    $c_query .= ",Inv_Volume";
    $volumen = $_POST['volumen'];
    $v_query .= ", :volumen ";
    $checkVolumen = 1;
}
if(isset($_POST['unidad_volumen']) && $_POST['unidad_volumen'] != ""){
    $c_query .= ",Inv_Volume_Un";
    $unidad_volumen = $_POST['unidad_volumen'];
    $v_query .= ", :unidad_volumen ";
    $checkUnidadVolumen = 1;
}
if(isset($_POST['color']) && $_POST['color'] != ""){
    $c_query .= ",Inv_Colour";
    $color = $_POST['color'];
    $v_query .= ", :color ";
    $checkColor = 1;
}
if(isset($_POST['ancho']) && $_POST['ancho'] != ""){
    $c_query .= ",Inv_Width";
    $ancho = $_POST['ancho'];
    $v_query .= ", :ancho ";
    $checkAncho = 1;
}
if(isset($_POST['unidad_ancho']) && $_POST['unidad_ancho'] != ""){
    $c_query .= ",Inv_Width_Un";
    $unidad_ancho = $_POST['unidad_ancho'];
    $v_query .= ", :unidad_ancho ";
    $checkUnidadAncho = 1;
}
if(isset($_POST['alto']) && $_POST['alto'] != ""){
    $c_query .= ",Inv_Height";
    $alto = $_POST['alto'];
    $v_query .= ", :alto ";
    $checkAlto = 1;
}
if(isset($_POST['unidad_alto']) && $_POST['unidad_alto'] != ""){
    $c_query .= ",Inv_Height_Un";
    $unidad_alto = $_POST['unidad_alto'];
    $v_query .= ", :unidad_alto ";
    $checkUnidadAlto = 1;
}
if(isset($_POST['profundidad']) && $_POST['profundidad'] != ""){
    $c_query .= ",Inv_Depth";
    $profundidad = $_POST['profundidad'];
    $v_query .= ", :profundidad ";
    $checkProfundidad = 1;
}
if(isset($_POST['unidad_profundidad']) && $_POST['unidad_profundidad'] != ""){
    $c_query .= ",Inv_Depth_Un";
    $unidad_profundidad = $_POST['unidad_profundidad'];
    $v_query .= ", :unidad_profundidad ";
    $checkUnidadProfundidad = 1;
}
if(isset($_POST['iva']) && $_POST['iva'] != ""){
    $c_query .= ",Inv_Iva";
    $iva = $_POST['iva'];
    $v_query .= ", :iva ";
    $checkIva = 1;
}
if(isset($_POST['precio_compra']) && $_POST['precio_compra'] != ""){
    $c_query .= ",Inv_Purch_Price";
    $precio_compra = $_POST['precio_compra'];
    $v_query .= ", :precio_compra ";
    $checkPrecioCompra = 1;
}
if(isset($_POST['moneda_compra']) && $_POST['moneda_compra'] != ""){
    $c_query .= ",Inv_Purch_Curr";
    if($_POST['moneda_venta'] == "€"){
       $moneda_compra = 1;
    }else if($_POST['moneda_compra'] == "$"){
        $moneda_compra = 2;
    }else if($_POST['moneda_compra'] == "£"){
        $moneda_compra = 3;
    }else{
        $moneda_compra = 1;
    }
    $v_query .= ", :moneda_compra ";
    $checkMonedaCompra = 1;
}
if(isset($_POST['cantidad']) && $_POST['cantidad'] != ""){
    $c_query .= ",Inv_Quant";
     $cantidad = $_POST['cantidad'];
    $v_query .= ", :cantidad ";
    $checkCantidad = 1;
}
if(isset($_POST['fecha_compra']) && $_POST['fecha_compra'] != ""){
    $c_query .= ",Inv_Purch_Date";
     $fecha_compra = $_POST['fecha_compra'];
    $v_query .= ", :fecha_compra ";
    $checkFechaCompra = 1;
}
if(isset($_POST['precio_venta']) && $_POST['precio_venta'] != ""){
    $c_query .= ",Inv_Sale_Price";
     $precio_venta = $_POST['precio_venta'];
    $v_query .= ", :precio_venta ";
    $checkPrecioVenta = 1;
}
if(isset($_POST['moneda_venta']) && $_POST['moneda_venta'] != ""){
    if($_POST['moneda_venta'] == "€"){
         $moneda_venta = 1;
    }else if($_POST['moneda_venta'] == "$"){
         $moneda_venta = 2;
    }else if($_POST['moneda_venta'] == "£"){
         $moneda_venta = 3;
    }else{
         $moneda_venta = 1;
    }

    $c_query .= ",Inv_Sale_Curr";

    $v_query .= ", :moneda_venta ";
    $checkMonedaVenta = 1;
}
if(isset($_POST['margen_porc']) && $_POST['margen_porc'] != ""){
    $c_query .= ",Inv_Profit_Marg_P";
     $margen_porc = $_POST['margen_porc'];
    $v_query .= ", :margen_porc ";
    $checkMargenPorc = 1;
}
if(isset($_POST['margen_dinero']) && $_POST['margen_dinero'] != ""){
    $c_query .= ",Inv_Profit_Marg_C";
     $margen_dinero = $_POST['margen_dinero'];
    $v_query .= ", :margen_dinero ";
    $checkMargenDinero = 1;
}
if(isset($_POST['proveedor']) && $_POST['proveedor'] != ""){
    $c_query .= ",Inv_Prov_Name";
     $proveedor = $_POST['proveedor'];
    $v_query .= ", :proveedor ";
    $checkProveedor = 1;
}
if(isset($_POST['adquisicion']) && $_POST['adquisicion'] != ""){
    $c_query .= ",Inv_Acqui";
     $adquisicion = $_POST['adquisicion'];
    $v_query .= ", :adquisicion ";
    $checkAdquisicion = 1;
}
if(isset($_POST['descuento']) && $_POST['descuento'] != ""){
    $c_query .= ",Inv_Discount";
     $descuento = $_POST['descuento'];
    $v_query .= ", :descuento ";
    $checkDescuento = 1;
}
if(isset($_POST['habilitado']) && $_POST['habilitado'] != ""){
    $c_query .= ",Inv_Sale_Enabled";
     $habilitado = $_POST['habilitado'];
    $v_query .= ", :habilitado ";
    $checkHabilitado = 1; 
}
if(isset($_POST['deposito']) && $_POST['deposito'] != ""){
    $c_query .= ",Inv_Storage_Place";
     $deposito = $_POST['deposito'];
    $v_query .= ", :deposito ";
    $checkDeposito = 1;
}
if(isset($_POST['total_antes']) && $_POST['total_antes'] != ""){
    $c_query .= ",Inv_Total_Before";
     $total_antes = $_POST['total_antes'];
    $v_query .= ", :total_antes ";
    $checkTotalAntes = 1;
}
if(isset($_POST['total_despues']) && $_POST['total_despues'] != ""){
    $c_query .= ",Inv_Total_After";
     $total_despues = $_POST['total_despues'];
    $v_query .= ", :total_despues ";
    $checkTotalDespues = 1;
}
if(isset($_POST['observaciones']) && $_POST['observaciones'] != ""){
    $c_query .= ",Inv_Observ";
     $observaciones = $_POST['observaciones'];
    $v_query .= ", :observaciones ";
    $checkObservaciones = 1;
}
if(isset($_POST['inv_category']) && $_POST['inv_category'] != ""){
    $c_query .= ",Inv_Categ";
     $category = $_POST['inv_category'];
    $v_query .= ", :category ";
    $checkCategory = 1;
}
if(isset($_POST['inv_subcategory']) && $_POST['inv_subcategory'] != ""){
    $c_query .= ",Inv_Sub_Categ";
     $subCategory = $_POST['inv_subcategory'];
    $v_query .= ", :subCategory ";
    $checkSubCategory = 1;
}
if(isset($_POST['status']) && $_POST['status'] != "" && $_POST['status'] != "0"){
    $c_query .= ",Inv_Status";
     $status = $_POST['status'];
    $v_query .= ", :staus ";
    $checkStatus = 1;
}



require_once('image_data_insert.php');




    $hQuery = "INSERT INTO Hist_Iny (HIy_User, HIy_Inv_Id, HIy_Action, HIy_Name) VALUES (:user, :ide, 'añadido', :name);";
    $resultHistory = $base -> prepare($hQuery);
    $resultHistory -> bindValue(':user', $user);
    $resultHistory -> bindValue(':ide', $ide);
    $resultHistory -> bindValue(':name', $nombre);
    $resultHistory -> execute();


    $queryLocation = "SELECT Dir_Province FROM directions WHERE Dir_User = :user ";
    $resultLoc = $base -> prepare($queryLocation);
    $resultLoc -> bindValue(':user', $user);
    $resultLoc -> execute();

    while($row = $resultLoc -> fetch(PDO::FETCH_ASSOC)){
        $location = $row['Dir_Province'];
    }



    $c_query .= ',Inv_Location';
    $v_query .= ", :location";

echo $query = $c_query .= ") VALUES " . $v_query . ");";


$result = $base -> prepare($query);

$result -> bindValue(':ide', $ide);
$result -> bindValue(':user', $user);

if(isset($location)){
    $result -> bindValue(':location', $location);
}
if(isset($checkRef)){
    $result -> bindValue(':ref', $ref);

}
if(isset($checkNombre)){
    $result -> bindValue(':nombre', $nombre);

}
if(isset($checkDescription)){
    $result -> bindValue(':desc', $desc);
}
if(isset($checkGrupo)){
    $result -> bindValue(':grupo', $grupo);
}
if(isset($checkTipo)){
    $result -> bindValue(':tipo', $tipo);
}
if(isset($checkMarca)){
    $result -> bindValue(':marca', $marca);
}
if(isset($checkModelo)){
    $result -> bindValue(':modelo', $modelo);
}
if(isset($checkNumeroCompra)){
    $result -> bindValue(':n_compra', $n_compra);
}
if(isset($checkRubro)){
    $result -> bindValue(':rubro', $rubro);
}
if(isset($checkPresentacion)){
    $result -> bindValue(':presentacion', $presentacion);
}
if(isset($checkPeso)){
    $result -> bindValue(':peso', $peso);
}
if(isset($checkUnidadPeso)){
    $result -> bindValue(':unidad_peso', $unidad_peso);
}
if(isset($checkVolumen)){
    $result -> bindValue(':volumen', $volumen);
}
if(isset($checkUnidadVolumen)){
    $result -> bindValue(':unidad_volumen', $unidad_volumen);
}
if(isset($checkColor)){
    $result -> bindValue(':color', $color);
}
if(isset($checkAncho)){
    $result -> bindValue(':ancho', $ancho);
}
if(isset($checkUnidadAncho)){
    $result -> bindValue(':unidad_ancho', $unidad_ancho);
}
if(isset($checkAlto)){
    $result -> bindValue(':alto', $alto);
}
if(isset($checkUnidadAlto)){
    $result -> bindValue(':unidad_alto', $unidad_alto);
}
if(isset($checkProfundidad)){
    $result -> bindValue(':profundidad', $profundidad);
}
if(isset($checkUnidadProfundidad)){
    $result -> bindValue(':unidad_profundidad', $unidad_profundidad);
}
if(isset($checkIva)){
    $result -> bindValue(':iva', $iva);
}
if(isset($checkPrecioCompra)){
    $result -> bindValue(':precio_compra', $precio_compra);
}
if(isset($checkMonedaCompra)){
    $result -> bindValue(':moneda_compra', $moneda_compra);
}
if(isset($checkCantidad)){
    $result -> bindValue(':cantidad', $cantidad);
}
if(isset($checkFechaCompra)){
    $result -> bindValue(':fecha_compra', $fecha_compra);
}
if(isset($checkPrecioVenta)){
    $result -> bindValue(':precio_venta', $precio_venta);
}
if(isset($checkMonedaVenta)){
    $result -> bindValue(':moneda_venta', $moneda_venta);
}
if(isset($checkMargenPorc)){
    $result -> bindValue(':margen_porc', $margen_porc);
}
if(isset($checkMargenDinero)){
    $result -> bindValue(':margen_dinero', $margen_dinero);
}
if(isset($checkProveedor)){
    $result -> bindValue(':proveedor', $proveedor);
}
if(isset($checkAdquisicion)){
    $result -> bindValue(':adquisicion', $adquisicion);
}
if(isset($checkDescuento)){
    $result -> bindValue(':descuento', $descuento);
}
if(isset($checkHabilitado)){
    $result -> bindValue(':habilitado', $habilitado);
}
if(isset($checkDeposito)){
    $result -> bindValue(':deposito', $deposito);
}
if(isset($checkTotalAntes)){
    $result -> bindValue(':total_antes', $total_antes);
}
if(isset($checkTotalDespues)){
    $result -> bindValue(':total_despues', $total_despues);
}
if(isset($checkObservaciones)){
    $result -> bindValue(':observaciones', $observaciones);
}
if(isset($checkCategory)){
    $result -> bindValue(':category', $category);
}
if(isset($checkSubCategory)){
    $result -> bindValue(':subCategory', $subCategory);
}
if(isset($checkStatus)){
    $result -> bindValue(':status', $status);
}
if(isset($checkImg1)){
    $result -> bindValue(':nombreImagen1', $nombreImagen1);
}
if(isset($checkImg2)){
    $result -> bindValue(':nombreImagen2', $nombreImagen2);
}
if(isset($checkImg3)){
    $result -> bindValue(':nombreImagen3', $nombreImagen3);
}
if(isset($checkImg4)){
    $result -> bindValue(':nombreImagen4', $nombreImagen4);
}


$result -> execute();



}
} catch (Exception $e) {
    echo "Error at line: " . $e -> getLine();
    echo '<br> Error message: ' . $e -> getMessage();
}

The following code refers to require_once('image_data_insert.php'); 

<?php 


    try {
        if(isset($_FILES['anunImg_1']['name']) && $_FILES['anunImg_1']['name'] != ""){
            $nombreImagen1=filter_var($_FILES['anunImg_1']['name'], FILTER_SANITIZE_STRING);
            $tipoImagen1=$_FILES['anunImg_1']['type'];
            $tamanoImagen1=$_FILES['anunImg_1']['size'];
            $c_query .= ",Inv_Img_1";
            $v_query .= ", :nombreImagen1";
            $checkImg1 = 1;

        }else{
            $tamanoImagen1 = 0;
            $nombreImagen1 = filter_var($_POST['auxAnunImg_1'], FILTER_SANITIZE_STRING);


        }
        if(isset($_FILES['anunImg_2']['name']) && $_FILES['anunImg_2']['name'] != ""){
            $nombreImagen2=filter_var($_FILES['anunImg_2']['name'], FILTER_SANITIZE_STRING);
            $tipoImagen2=$_FILES['anunImg_2']['type'];
            $tamanoImagen2=$_FILES['anunImg_2']['size'];
            $c_query .= ",Inv_Img_2";
            $v_query .= ", :nombreImagen2";
            $checkImg2 = 1;

        }else{
            $nombreImagen2 = filter_var($_POST['auxAnunImg_2'], FILTER_SANITIZE_STRING);

        }
        if(isset($_FILES['anunImg_3']['name']) && $_FILES['anunImg_3']['name'] != ""){
            $nombreImagen3=filter_var($_FILES['anunImg_3']['name'], FILTER_SANITIZE_STRING);
            $tipoImagen3=$_FILES['anunImg_3']['type'];
            $tamanoImagen3=$_FILES['anunImg_3']['size'];
            $c_query .= ",Inv_Img_3";
            $v_query .= ", :nombreImagen3";
            $checkImg3 = 1;

        }else{
            $nombreImagen3 = filter_var($_POST['auxAnunImg_3'], FILTER_SANITIZE_STRING);

        }
        if(isset($_FILES['anunImg_4']['name']) && $_FILES['anunImg_4']['name'] != ""){
            $nombreImagen4=filter_var($_FILES['anunImg_4']['name'], FILTER_SANITIZE_STRING);
            $tipoImagen4=$_FILES['anunImg_4']['type'];
            $tamanoImagen4=$_FILES['anunImg_4']['size'];
            $c_query .= ",Inv_Img_4";
            $v_query .= ", :nombreImagen4";
            $checkImg4 = 1;

        }else{
            $nombreImagen4 =  filter_var($_POST['auxAnunImg_4'], FILTER_SANITIZE_STRING);

        }
        if($tamanoImagen1 <2500001 || $tamanoImagen2 <2500001 || $tamanoImagen3 <2500001 || $tamanoImagen4 <2500001){
        if($tipoImagen1="image/jpeg" || $tipoImagen1="image/jpg" ||   $tipoImagen1="image/png" || $tipoImagen1="image/gif" || $tipoImagen2="image/jpeg" || $tipoImagen2="image/jpg" || $tipoImagen2="image/png" || $tipoImagen2="image/gif"  || $tipoImagen3="image/jpeg" || $tipoImagen3="image/jpg" ||   $tipoImagen3="image/png" || $tipoImagen3="image/gif" || $tipoImagen4="image/jpeg" || $tipoImagen4="image/jpg" ||   $tipoImagen4="image/png" || $tipoImagen4="image/gif"){

        $carpetaDestino=$_SERVER['DOCUMENT_ROOT'] . '/img_productos/';

        move_uploaded_file($_FILES['anunImg_1']['tmp_name'], $carpetaDestino.$nombreImagen1);
        move_uploaded_file($_FILES['anunImg_2']['tmp_name'], $carpetaDestino.$nombreImagen2);
        move_uploaded_file($_FILES['anunImg_3']['tmp_name'], $carpetaDestino.$nombreImagen3);
        move_uploaded_file($_FILES['anunImg_4']['tmp_name'], $carpetaDestino.$nombreImagen4);

        }else{
            echo "Solo se pueden subir imagenes jpeg, jpg, png, gif";
        }
    }else{
        echo "El tamaño es demasiado grande";
    }
    } catch (Exception $e) {
        echo "Error at line: " . $e -> getLine();
        echo '<br> Error message: ' . $e -> getMessage();
    }






 ?>

If anyone knows any other way of doing what this code does but in a simpler way I would appreciate it. I have recently used prepared consultations and I do not know how to use them in the best way possible

    
asked by gmarsi 13.09.2017 в 12:12
source

1 answer

1

@ jose-fg apparently answered the why of the error, we still do not know ;-) I just want to give you an idea of how you could make your code a little easier: 

$cantidad_campos = 3; // Cantidad de campos en $tabla_inventario['campos']

$tabla_inventario = array(
    'campos' => array(// campos de la tabla
        'Inv_Ref',
        'Inv_Name',
        'Ivn_Desc'
    ) ,
    'incluir' => array_fill(0, $cantidad_campos, false), 
    // Como los nombres de los campos en tu form son distintos a 
    // los nombres de los campos de la tabla agrego esta key
    // Si tuvieran el mismo nombre, sólo usaría el key 'campos'
    // en todos los lugares dónde aparece 'campos_form'
    'campos_form' => array( 
        'ref',
        'nombre',
        'desc'
    ),

);

// Datos necesarios para armar el insert;
$insert = array(
    'campos' => '',
    'parametros' => '',
    'valores' => array() ,
);

for ($i = 0; $i < $cantidad_campos; $i++) {
    $campo = $tabla_inventario['campos'][$i];

    $campo_form = $tabla_inventario['campos_form'][$i];    
    if (!empty($_POST[$campo_form])) {
        $insert['campos'].= ',' . $campo;
        $insert['parametros'].= ',:' . $tabla_inventario['campos'][$i];
        $insert['valores'][$campo] = $_POST[$campo_form];
        $tabla_inventario['incluir'][$i] = true;
    }
}

$query = 'INSERT INTO inventory (Inv_Id,Inv_Use' . $insert['campos'] . ')';
$query.= ' VALUES (:ide ,:user' . $insert['parametros'] . ');';

echo $query . PHP_EOL;

for ($i = 0; $i < $cantidad_campos; $i++) {
    if ($tabla_inventario['incluir'][$i]) {
        $campo = $tabla_inventario['campos'][$i];
        // Aquí bindearías tus parámetros con sus valores.
        // $result->bindValue(':'.$campo, $insert['valores'][$campo]);
        echo 'Parámetro :' . $campo . ' => ' . 
                $insert['valores'][$campo] . PHP_EOL;
    }
}

I hope it will guide you. Greetings

One thing that I just realized that the :staus parameter in the last if before requiring the image file should be :status

    
answered by 13.09.2017 / 23:35
source
ng-repeat does not show me the list run an event if it passes x seconds running?