how to call a mysql stored procedure from php with data from a form

Question

how to call a mysql stored procedure from php with data from a form

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

<!DOCTYPE html>
<?php
include 'MenuNav.php';
?>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link href="../Resources/css/bootstrap.css" rel="stylesheet" type="text/css"/>
        <title>Aplicacion Web</title>

        <link rel="stylesheet" href="../Resources/css/bootstrap.min.css">
        <title>Registro de Embarazadas</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
    </head>

    <body>
        <h2 align ="center">Ingresar Nueva Embarazada</h2>

        <div class='container'> 
            <form class='form-horizontal' name='regEmb' id="regEmb">
                <div class='form-group'>
                    <div class='col-sm-offset-1 col-sm-10'>
                        <table class='table table-striped' align="center" id="tablaReg">
                            <tr><td>Id Embarazada</td><td><input type ='text' name='idEmbarazada' id='idEmbarazada' value='0' readonly/></td>
                                <td>Fecha Visita 1</td><td><input type ='date' name='visita1' id='visita1' /></td>
                            </tr>
                            <tr><td>Nombre de la Embarazada</td><td><input type ='text' name='nombreEmb' id='nombreEmb' required /></td>
                                <td>Fecha Visita 2</td><td><input type ='date' name='visita2' id='visita2' /></td>
                            </tr>
                            <tr><td >Edad: </td><td><input type ='number' min='10' max='52' name='edad' step='1' id='edad' required/></td>
                                <td>Fecha Visita 3</td><td><input type ='date' name='visita3' id='visita3' /></td>
                            </tr>
                            <tr><td >Expediente: </td><td><input type ='text' name='Expediente' id='Expediente' /></td>
                                <td>Fecha Visita 4</td><td><input type ='date' name='visita4' id='visita4' /></td>
                            </tr>
                            <tr><td >Canton :</td><td><select name='idCanton' id='idCanton'>
                                        <option value=1>El Faro</option>
                                        <option value=2>Col. Bellos Horizontes</option>
                                        <option value=3>La Shila</option>
                                        <option value=4>Los Conacastes</option>
                                        <option value=6>El Penon</option>
                                        <option value=7>El Matazano</option>
                                        <option value=8>El Rosario</option>
                                        <option value=9>Zona A Urbana</option>
                                        <option value=10>Zona B Urbana</option>
                                        <option value=11>San Antonio</option>
                                        <option value=12>Col. Venezuela</option>
                                        <option value=13>San Luis II</option>
                                    </select></td>
                                <td>Fecha Visita 5</td><td><input type ='date' name='visita5' id='visita5' /></td>

                            </tr>
                            <tr><td>Fecha de Inscripcion</td><td><input type ='date' name='fechaInscripcion' id='fechaInscripcion'/></td>
                                <td>Fecha Visita 6</td><td><input type ='date' name='visita6' id='visita6'  /></td>
                            </tr>
                            <tr><td>Riesgo: </td><td><input type ='text' name='riesgo' id='riesgo' required /></td>
                                <td>Fecha Visita 7</td><td><input type ='date' name='visita7' id='visita7' /></td>
                            </tr>

                            <tr><td>Fecha Ultima Regla</td><td><input type ='date' name='fur' id='fur'/></td>
                                <td>Fecha Visita 8</td><td><input type ='date' name='visita8' id='visita8' /></td>
                            </tr>
                            <tr><td>Fecha Probable de parto</td><td><input type ='date' name='fpp' id='fpp' /></td>
                                <td>Fecha Fracaso del Parto</br></br>Edad gestacional al fracaso</td><td><input type ='date' name='fechaFracaso' id='fechaFracaso' />
                                    </br><input type ='number' min='0' max='42' step='1' name='edadgesFracaso'   id='edadgesFracaso' value='0' /></td>

                            </tr>
                            <tr><td>Formula Obstetrica</td><td>
                                    G:<input type ='number' min='0' max='18' step='1' name='gravida' id='gravida' value='0' required/>
                                    P:<input type ='number' min='0' max='18' step='1' name='paridad' id='paridad' value='0' />
                                    P:<input type ='number' min='0' max='18' step='1' name='prematuro' id='prematuro' value='0' /></br></br>
                                    A:<input type ='number' min='0' max='18' step='1' name='aborto' id='aborto' value='0' />
                                    V:<input type ='number' min='0' max='18' step='1' name='vive' id='vive' value='0' /></td>
                                <td>Fecha  Parto </br></br>Lugar del parto:</td><td><input type ='date' name='parto' id='parto'/></br><input type ='text' name='lugarParto' id='lugarPartp'/></td>


                                </td>

                            </tr>
                            <tr><td>Inscripcion Odontologia</td><td><input type ='date' name='insodontologia' id='insodontologia'/></td>
                                <td>Complicacion en el parto: </td><td><input type ='text' name='complicacionParto' id='complicacionParto' /></td>
                            </tr>
                            <tr><td>UCSF Responsable: </td><td> <select name='idUCSF' id='idUCSF' required> <option value="">seleccion UCSF</option> <option value='1'>UCSF La Shila</option>  <option value='2'>UCSF El Conacaste</option><option value='3'>UCSF El Matazano</option> <option value='4'>UCSF Intermedia Comasagua</option><option value='5'>UCSF Talnique</option></select></td>
                                <td>Fecha  Inscripcion de la Puerpera</td><td><input type ='date' name='FechaInsPP' id='FechaInsPP' /></td>
                            </tr>

                            <tr><td>Promotor Responsable:</td><td><select name='idPromotor' id='idPromotor' required>
                                        <option value="">seleccion Promotor de Salud</option>
                                        <option value=1>Jorge Antonio Gonzalez</option>
                                        <option value=2>Maria Carlota Reyes Perez</option>
                                        <option value=3>Carlos Antonio Valladares </option>
                                        <option value=4>Maira Guadalupe Hernandez</option>
                                        <option value=5>Rafael Antonio Escobar</option>
                                        <option value=6>Jaquelin Nohemy Amaya Dominguez</option>
                                        <option value=7>Flor Maria Jacobo</option>
                                        <option value=8>Bill Alexander Gonzalez Reales</option>
                                        <option value=9>Diana Stephany Linares Fuentes</option>
                                        <option value=10>Iris Iveth Pacheco</option>
                                        <option value=11>Jose Ricardo Cornejo</option>
                                        <option value=12>Gerson Ulises Cortez Martinez</option>
                                        <option value=13>Mauricio Ernesto Umana Iraheta</option>
                                        <option value=14>Erazmo Escobar</option>
                                    </select></td>
                                <td>Fecha Inscripcion del recien nacido  </td><td><input type ='date' name='fechaInsRN' id='fechaInsRN' /></td>

                            </tr>
                            <tr><td>Observaciones : </td><td><textarea rows='3' cols='30' name='observaciones' id='observaciones' ></textarea></td>
                                <td>Complicaciones del Recien nacido:</td><td><input type ='number' min='0' max='18' step='1' name='complicacionRN' id='complicacionRN' value='0' /></td>
                            </tr>
                            <tr>
                                <td colspan="4" align="center"> <button type="button" class="btn btn-danger" data-dismiss="modal">Cerrar</button>
                                    <button type="button" class="btn btn-success" id="ingresar">Guardar</button>
                                </td></tr>

                        </table>

                    </div></div> 
            </form>
        </div>
  <script src="../Resources/js/jquery-1.11.2.js"></script>
    <script src="../Resources/js/bootstrap.min.js"></script>
    <script src="../Resources/js/embarazadas.js"></script>

    </body>
</html>

And this is my method in the Model

function ingresar($idEmbarazada, $nombreEmb, $edad, $Expediente, $idCanton,     
$fechaInscripcion, $riesgo, $fur, $fpp, $gravida, $paridad, $prematuro, $aborto, $vive, 
$visita1, $visita2, $visita3, $visita4, $visita5, $visita6, $visita7, $visita8, 
$fechaFracaso, $edadgesFracaso, $parto, $lugarParto, $complicacionParto, $FechaInsPP, 
$fechaInsRN, $complicacionRN, $insodontologia, $observaciones, $idUCSF, $idPromotor)
{
    $sql = "insert into embarazadas(nombreEmb, edad, Expediente, idCanton, fechaInscripcion, 
    riesgo, fur, fpp, gravida, paridad, prematuro, aborto, vive, visita1, visita2, visita3, 
    visita4, visita5, visita6, visita7, visita8, fechaFracaso, edadgesFracaso, parto, 
    lugarParto, complicacionParto, FechaInsPP, fechaInsRN, complicacionRN, 
    insodontologia, observaciones, IdUCSF, idPromotor) 
    VALUES ('$nombreEmb','$edad','$Expediente',$idCanton,'$fechaInscripcion','$riesgo',
    '$fur','$fpp','$gravida','$paridad','$prematuro','$aborto','$vive','$visita1',
    '$visita2','$visita3','$visita4','$visita5','$visita6','$visita7','$visita8',
    '$fechaFracaso','$edadgesFracaso','$parto','$lugarParto','$complicacionParto',
    '$FechaInsPP','$fechaInsRN','$complicacionRN','$insodontologia','$observaciones',
    '$idUCSF','$idPromotor')";
    if ($this->conexion->conexion->query($sql)) {
        return true;
    }
    else {
        return false;
    }

    $this->conexion->cerrar();
}

and this is the controller that receives the parameters.

How do I change the insert into statement to the stored procedure called SP_insertEMB in my BD?

mysql php stored-procedure

asked by Matias Olivera 10.08.2017 в 03:40

source

2 answers

Delete Record LINQ to ENTITY Call an element of an array whose element name is in a variable

score 1 · Answer 1

You have to use the SQL statement CALL to execute a stored procedure.

$sql = "CALL procedimientoAlmacenado();";

In your case, it would be as follows:

$sql = "CALL SP_insertEMB( $nombre, $edad );";

In addition, it is a good practice to use prepared statements to avoid SQL injection.

$nombre = "Maria";
$edad = 25;
$stmt = $db->prepare("CALL SP_insertEMB (?, ?);");
$stmt->bind_param("si", $nombre, $edad);
$stmt->execute();
$stmt->close();
$db->close();

score 0 · Answer 2

Another way would also be the following: To change the sentence, you create a function, and send it the parameters you need, and from there you call the stored procedure. (The procedure in the BD must be prepared to receive and insert all the parameters that you are sending).

It would stay like this in php.

    public function executInsert($nombreEmb, $edad, $Expediente){
    try{
    //Declaras parámetros de salida
    $sentencia = $this->db->prepare("CALL SP_insertEMB(?, ?, ?);");
    $sentencia->bindParam("1", $fname, PDO::PARAM_STR, 4000);
    $sentencia->bindParam("2", $tlfn, PDO::PARAM_STR, 4000); 
    $sentencia->bindParam("3", $email_cliente, PDO::PARAM_STR, 4000);  

    // llamar al procedimiento almacenado
    $sentencia->execute();

    //print "El procedimiento contiene: $nombreEmb, $edad, $Expediente;

    return true;

    } catch(PDOException $e) {

    echo $e->getMessage();  
        return false;
    }