Home / Questions / 487998
Accepted
ArcanisGK507
Asked: 2022-10-02 14:07:21 +0800 CST 2022-10-02 14:07:21 +0800 CST

php script returns nothing but phpmyadmin does

  • 772

Starting from the following hypothetical example structure:

Table: Master_Product

Prod_Code   perfil  Marca   Orden   Categoría
1           1       10      25      2
2           1       10      26      2
3           1       10      27      2
4           1       10      28      6
5           1       10      29      6
6           1       10      30      6
7           1       10      31      6

Table: Product_Structure

Prod_Code   perfil  UxC CxP
1           1       4   60
2           1       10  60
3           1       8   60
4           1       12  60
5           1       20  60
6           1       24  60
7           1       50  60

Table: Product_Supplier

Prod_Code   perfil  Proveedor
1           1       30
2           1       30
3           1       30
4           1       30
5           1       40
6           1       40
7           1       40

Table: Product_Price

Prod_Code   perfil  Price  Prod_DateUpd
1           1       10     20/10/2021
2           1       10     20/10/2021
1           1       5      19/10/2021
2           1       5      19/10/2021
3           1       20     20/10/2021
1           1       20     15/10/2021
1           1       15     15/10/2021

implement the following query to fetch all the data:

SELECT * FROM Master_Producto
LEFT JOIN(SELECT * FROM Producto_Estructura) AS Producto_Estructura ON ( Master_Producto.Prod_Code = Producto_Estructura.Prod_Code AND Master_Producto.Prod_PF = Producto_Estructura.Prod_PF)
LEFT JOIN(SELECT * FROM Producto_Proveedor) AS Producto_Proveedor ON ( Master_Producto.Prod_Code = Producto_Proveedor.Prod_Code AND Master_Producto.Prod_PF = Producto_Proveedor.Prod_PF)
LEFT JOIN(SELECT * FROM Producto_Precio ORDER BY Prod_DateUpd DESC) AS Producto_Precio ON ( Master_Producto.Prod_Code = Producto_Precio.Prod_Code AND Master_Producto.Prod_PF = Producto_Precio.Prod_PF)

Tested with phpmyadminit works correctly, but I can't get it to work from the php.

it happens that this is executed by ajaxand the responseand errorare returned empty; when you expect something to always come, hopefully it's garbage...

the scenario as I was commenting is hypothetical, let's say that the real query uses the same pattern, to 10 tables where there are between 2000 products in the master and in the other tables there can be between 2000 and 500,000 records. I go back and repeat in phpmyadminthe Query.

there are like 500 columns...

enter image description here

These are the scripts:

where everything starts:

<?php
    $stmtpre           = "SELECT * FROM Master_Producto
    LEFT JOIN(SELECT * FROM Producto_Estructura) AS Producto_Estructura ON ( Master_Producto.Prod_Code = Producto_Estructura.Prod_Code AND Master_Producto.Prod_PF = Producto_Estructura.Prod_PF)
    LEFT JOIN(SELECT * FROM Producto_Proveedor) AS Producto_Proveedor ON ( Master_Producto.Prod_Code = Producto_Proveedor.Prod_Code AND Master_Producto.Prod_PF = Producto_Proveedor.Prod_PF)
    LEFT JOIN(SELECT * FROM Producto_Precio ORDER BY Prod_DateUpd DESC) AS Producto_Precio ON ( Master_Producto.Prod_Code = Producto_Precio.Prod_Code AND Master_Producto.Prod_PF = Producto_Precio.Prod_PF);";
    $data              = $this->BDquery($stmtpre, DB_PRODUCT); //aquí se queda
    //nada despues de este punto se ejecuta;
    echo '<pre>';
    echo var_export($data,true);
    echo '</pre>';

and the database connection scripts:
Note: This script is only used to search for data that does not require search parameters.

<?php

    function Open_Con_DB($dbUsing) {
            $error            = [];
            $error['dberror'] = 'no';
            $dblink           = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASS); # verificar donde se incluye el puerto
            $selected         = mysqli_select_db($dblink, DB_PRE . $dbUsing); # verificar donde se incluye el puerto
            if (!$selected) {$error = $this->errorDB($dblink);} # Verificacion de Errores de Coneccion a la base de Datos
            try {
                if ($error['dberror'] == true) {
                    throw new Exception('A ocurrido un Error De coneccion a la Base de Datos.<br>' . $error['msg']);
                }
            } catch (Exception $e) {
                echo 'Error: ' . $e->getMessage() . '<br>Error Trasado:' . $e->getTraceAsString();
                die('Proceso Cancelado');
            }
            $dblink->query("SET NAMES 'utf8'"); # Enlace de Coneccion UTF-8
            return $dblink;
    }
    
    function Close_Con_DB($dblink) {
        mysqli_close($dblink);
    }
    
    function ErrorDB($dblink) {
        $error['msg']     = 'Indicacion: ' . mysqli_error($dblink);
        $error['dberror'] = true;
        return $error;
    }

    function BDquery($dbquery, $dbUsing) {
        $dblink = $this->Open_Con_DB($dbUsing);
        $datos  = mysqli_query($dblink, $dbquery) or $this->errorDB($dblink);
        $this->Close_Con_DB($dblink);
        return $datos;
    }

MySQL configuration:

[client]
#password=your_password
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=512M
max_allowed_packet=2048M
table_open_cache=256
sort_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=64M
thread_cache_size=8

secure-file-priv=""
explicit_defaults_for_timestamp=1
datadir= "C:/laragon/data/mysql"
lower_case_table_names=2
sql_mode=""


[mysqldump]
quick
max_allowed_packet=2048M

Update #1:
I had forgotten to check the MySQL Log, here is the data:

2021-10-01T22:12:10.757688Z 120 [Note] Aborted connection 120 to db: 'dbproducts' user: 'root' host: 'localhost' (Got an error writing communication packets)

Update #2:
I have decided to check the Apache log:

[Fri Oct 01 19:29:28.308182 2021] [cgi:error] [pid 12144:tid 1124] [client 127.0.0.1:58769] AH01215: PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 24576 bytes) in Unknown on line 0: C:/laragon/bin/php/php72/php-cgi.exe, referer: http://avi2.me/

for so few bytes:

Setting:

the web server runs php 7.2as in the following images, but the phpmyadmin runs with php 7.4 apache handler 2.0, the two environments have the same environment variables 3gb of memory limit.

PHPMyAdmin executes everything correctly and the web app does not

enter image description here

Environment Variables, it will be that 1 GB is very little:

enter image description here

Doubts:
if the problem is memory it goes to more than 3GB but I still don't understand why in php-cgi it dies for and not in phpmyadmin?
If the connection was aborted why don't I get an error?
So I don't know where the error could be or what I should correct?

enter image description here

php

1 Answers

  1. Best Answer

    Your code is poorly conceived and perhaps the reason for the aborted connection is that error in the conception of your code (although the cause may be something else, such as firewall problems and others).

    If we look at your method BDquery(), there you would be getting a resource with mysqli_query(), then you close the connection and return the resource. OK, very nice, but to do that is to ignore how mysqli works. When you need to read the resource $datos the connection will be closed and you need the connection to read the resource and that could be the reason for the error.

    One way to solve this would be to read the data inside the method and return it read, not as a resource:

    function BDquery($dbquery, $dbUsing) {
    $dblink = $this->Open_Con_DB($dbUsing);
    if ($dblink) {
        if ( $datos = mysqli_query($dblink, $dbquery) ) {
            $outPut=array();
            while ($row = mysqli_fetch_assoc($datos)) {
                $outPut[]=$row;
            }
        } else {
            $outPut['error']=$this->errorDB($dblink);
        }
        $this->Close_Con_DB($dblink);
    } else {
        $outPut['error']='No hay conexión';
    }
    return $outPut;
}

    Here $outPutit will be, either the data already read or the possible errors that have occurred. And if there is a problem of no connection, you will also know it, since a check is made of it.

    On the other hand, your method Open_Con_DB()is bizarre. You can simplify it like this:

    function Open_Con_DB($dbUsing) {
    $dblink = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASS, DB_PRE . $dbUsing);
    if ($dblink) {
        $dblink->query("SET NAMES 'utf8'"); # Enlace de Coneccion UTF-8
        return $dblink;
    } else {
        #No conviene mostrar errores internos
        #Cámbialos por mensajes personalizados en producción
        die( 'Error de Conexión ('
             .mysqli_connect_errno(). ') '
             . mysqli_connect_error()
            );
        return null;
    }
}

    As you can see, we've passed the database in the same function call, because mysqli allows this (unlike good old mysql_ which didn't).

    There is no need to mess with blocks try ... catch. We simply worry that the connection is valid and if it is not, we throw an error message. Anyway, if for some reason you are interested in working with Exceptions, you can raise one in the part of else (where is the die())

    • 2