Creating a multidimensional array with php and MySQL

Question

Creating a multidimensional array with php and MySQL

Navigation

#1 by (0 votes)

0

I'm creating a multidimensional array, which by necessity should be something like this: The lengths (225,250 ... 950) will not always be the same, depending on what is in the database, as well as the categories (T1, T2, T3 ... T12)

I did a stored procedure to create this table in the database:

SET @sql_dynamic = (
        SELECT
            GROUP_CONCAT( DISTINCT
                CONCAT(
                    'ROUND (SUM( IF(longitud_inv = '
                    , longitud_inv
                    , ', volumen,0) ),2) AS ''
                    , longitud_inv , '''
                )
                order by longitud_inv ASC
            )
        FROM inventario_ingreso_detalle
    );  
    SET @sql = CONCAT('SELECT IFNULL(CONCAT("T",id_cat),"TOTAL") AS "Categoria/Long", ', 
                  @sql_dynamic, ',

               ROUND (SUM(volumen),2) AS Total
               FROM inventario_ingreso_detalle
               INNER JOIN Categoria_RW_OP ON inventario_ingreso_detalle.categoria_inv = Categoria_RW_OP.idCategoria_RW
               GROUP BY 
               id_cat WITH ROLLUP'
           );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

However, I have 2 problems: When calling the procedure, since it does not have a clear return value, it does not show anything on the screen and I do not know how else to call it. Second: I try to make the query from php, without a stored procedure, but still it is not showing me anything.

This is the PHP code I use:

    $sql = "SELECT GROUP_CONCAT( distinct
        CONCAT(
            'ROUND(SUM(IF(longitud_inv = '
            , longitud_inv
            , ', volumen,0) 
            ),2) AS '''
            , longitud_inv . '''
        )
        order by longitud_inv ASC
    )
    FROM inventario_ingreso_detalle
    CONCAT('SELECT IFNULL(CONCAT('T', id_cat), 'TOTAL') AS 'Categoria/Long',', '
    ROUND(SUM(volumen),2) AS Total
    FROM inventario_ingreso_detalle
    INNER JOIN Categoria_RW_OP ON inventario_ingreso_detalle.categoria_inve = Categoria_RW_OP.idCategoria_RW
    GROUP BY 
    id_cat WITH ROLLUP'
    )
    ";

$result= mysqli_query($GLOBALS['link'] , $sql);
if ($result){
while($row = mysqli_fetch_array($result)) { 
    echo $row['volumen'];
    }
  }
}

mysql php mysqli

asked by Kevin Peña 03.12.2018 в 16:19

source

1 answer

How to transform an image that I got from an input to base 64 with php? jquery validate Uncaught TypeError: $ (...) .validate is not a function

score 0 · Answer 1

Kevin, I wrote this code to help you find the problem.

It is fully controlled and will tell you exactly what is happening.

It seems strange to me that the connection exists within GLOBALS.

Also, you do not have any column called volumen . I used another * fetch_style 'to print the first column, whatever it is, just as a test.

$sql = "SELECT GROUP_CONCAT( distinct
        CONCAT(
            'ROUND(SUM(IF(longitud_inv = '
            , longitud_inv
            , ', volumen,0) 
            ),2) AS '''
            , longitud_inv . '''
        )
        order by longitud_inv ASC
    )
    FROM inventario_ingreso_detalle
    CONCAT('SELECT IFNULL(CONCAT('T', id_cat), 'TOTAL') AS 'Categoria/Long',', '
    ROUND(SUM(volumen),2) AS Total
    FROM inventario_ingreso_detalle
    INNER JOIN Categoria_RW_OP ON inventario_ingreso_detalle.categoria_inve = Categoria_RW_OP.idCategoria_RW
    GROUP BY 
    id_cat WITH ROLLUP'
    )
    ";

$mysqli=$GLOBALS['link'];   
if($mysqli){
    if ( $result= mysqli_query($mysqli , $sql) ){
        if ( $result->num_rows > 0 ){
            while($row = mysqli_fetch_row($result)) { 
                echo $row[0];
            }
        } else {
            echo "Cero registros encontrados";
        }
    } else {
        echo "Error en la consulta: ".mysqli_error($mysqli);
    }
} else {
    echo "No hay conexion... Lo de GLOBALS es muy raro";
}

It's a talking code , it's bound to tell you what happens. I hope it serves you and if we have to refine it we will do it.