Show stored procedure values as json

Question

Show stored procedure values as json

Navigation

#1 by (0 votes)

0

Hi, I have the following problem, I am creating two functions to show queries in json format. This is my code:

<?php
include'db.php';
class Descuento extends Database{
    public function listar_producto(){
        $sql = "CALL sp_mostrar_producto";
        $array = array();
        $query = mysqli_query($this->con,$sql);
        while($row = mysqli_fetch_array($query)){
            $array[] = $row;
        }
        echo json_encode($array);
    }
    public function listar_categoria(){
        $sql = "SELECT * FROM alm_categoria ";
        $array = array();
        $query = mysqli_query($this->con,$sql);
        while($row = mysqli_fetch_assoc($query)){
            $array[] = $row;
        }
        echo json_encode($array);
    }

}

$desc = new Descuento();
if(isset($_GET["show"])){
    $respuesta = $_GET["show"];
    if($respuesta == 1){
        $desc->listar_producto();
    }elseif($respuesta == 2){
        $desc->listar_categoria();
    }

}

?>

The problem is that when I have to show list_product () , it does not show, while list_category () if it does. I also add that when I use the query:

SELECT * FROM glb_producto

in list_product (), if it works Thanks in advance.

mysql php json

asked by Raphael 06.07.2017 в 06:07

source

1 answer

Wordpress | Incorrect image selected by Facebook when sharing an entry or post Decorator Django

score 0 · Accepted Answer

Because it is not a select, you have to design the procedure in SQL so that you can linekear the variables.

I recommend you use PDO instead of mysqli if you need to call custom procedures and functions (in mysqli it's php.net/manual/en/mysqli-stmt.bind-param.php, but I'm not sure if it will work for you correctly).

With PDO you have the examples here ( link ):

<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";

Here is an example of how to do it with Oracle (source: link ):

<?php
// etc.

$sql = 'BEGIN sayHello(:name, :message); END;';

You then bind the parameters to PHP variables with calls to oci_bind_by_name() .

If the sayHello procedure was defined by the following DDL statement:

CREATE OR REPLACE PROCEDURE 
        sayHello (name IN VARCHAR2, greeting OUT VARCHAR2) 
    AS
        BEGIN
            greeting := 'Hello ' || name;
        END;
/

Note that you can run the above statement using the SQL*Plus command line. Save the statement to a file (SAYHELLO.SQL). Next, login with SQL*Plus:

$ sqlplus username@SID

Then create the procedure using the START command:

SQL> START /home/username/SAYHELLO.SQL

The following PHP script calls the procedure:

<?php
$conn = oci_connect('SCOTT','TIGER') or die;


$sql = 'BEGIN sayHello(:name, :message); END;';

$stmt = oci_parse($conn,$sql);

//  Bind the input parameter
oci_bind_by_name($stmt,':name',$name,32);

// Bind the output parameter
oci_bind_by_name($stmt,':message',$message,32);

// Assign a value to the input 
$name = 'Harry';

oci_execute($stmt);

// $message is now populated with the output value
print "$message\n";