MYSQL / PHP / JQuery query assistant

I am working on a report that shows the results of an SQL query in an HTML table

I can get the data very easy with PHP

$link=new PDO('mysql:host=localhost;dbname=dbname','user','pass',array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

$resultado=$link->query("SELECT users.nombre, sucursales.nombre FROM users LEFT JOIN sucursales ON sucursales.idsucursales=users.idsucursales");

        $headers="";
        $T_campos=$resultado->columnCount();
        for($x=0;$x<$T_campos;$x++){
            $meta=$resultado->getColumnMeta($x);
            $headers[$x]=$meta['name'];
        }
        foreach($resultado as $row){
            $local=array();
            for($con=0;$con<$T_campos;$con++){
                $local[$con]=$row[$con];
            }
            $jsondata[]=$local;
        }

But the results I get are always the same in columns of the same name

For example in this query

SELECT **users.nombre, sucursales.nombre** FROM users LEFT JOIN sucursales ON sucursales.idsucursales=users.idsucursales

The view I get of results is something like this

nombre | nombre
user x | sucursal x
user x | sucursal x

How can I get the full names with everything and table in this query without using alias "AS"?

Something like this

users.nombre | sucursales.nombre
user x | sucursal x
user x | sucursal x

The idea is to be able to create a reporter where any SQL can be entered and this one throws the tables and fields to which each column of the query belongs

One of the ideas that come out is at the same time to make the query as such, make a decoding of the SQL itself

Let me explain

Unpack the " SELECT tabla1.campo1, tabla2.campo2 FROM tabla1, tabla2... " in an array

  

tabla1.campo1 - > table1

     

table2.field2 - > table2

And go through "schema" to validate that this field corresponds to that table

Thanks to our friend A. Cedano for his contributions

UPDATE

function multiexplode($delimiters,$string){
    $ready=str_replace($delimiters,$delimiters[0],$string);
    $launch=explode($delimiters[0],$ready);
    return $launch;
}
function LimpiaCadena($SQL){
    return preg_replace('/[ \t]+/',' ',preg_replace("/[\r\n]+/"," ",$SQL));
}
function EliminaEspacios($SQL){
    return trim(preg_replace("/[[:blank:]]+/"," ",$SQL));
}

function GetCampos($link,$SQL){
    function Get_Pos($SQL,$OMITE=array()){
        $SQL=strtoupper($SQL);
        $C=array(" LEFT JOIN "," RIGHT JOIN "," INNER JOIN "," WHERE "," GROUP BY "," HAVING "," ORDER BY ",);
        $Pos=0;
        foreach($C as $row){
            if(!in_array($row,$OMITE)){
                $Pos=strpos($SQL,$row);
                if($Pos!==false) return $Pos;
            }
        }
        return strlen($SQL);
    }
    function Sep_Tabla($valor,$sep="."){
        if(strpos($valor,$sep)!==false){
            $sp=explode($sep,$valor);
            $sp[0]=EliminaEspacios($sp[0]);
            $sp[1]=EliminaEspacios($sp[1]);
            return array($sp[0],$sp[1]);
        }else{
            $valor=EliminaEspacios($valor);
            return array("",$valor);
        }
    }

    $CAMPOS=array();
    $ALIAS=array();

    $SQL=LimpiaCadena($SQL);
    $PSelect=strpos(strtoupper($SQL),"SELECT ");
    $PFrom=strpos(strtoupper($SQL)," FROM ");
    if($PSelect!==false && $PFrom!==false){
        $PSelect+=7;   
        $np=$PFrom-$PSelect;
        $cam=substr($SQL,$PSelect,$np);
        //OBTENGO CAMPOS
        $ACam=explode(",",$cam);
        foreach($ACam as $key=>$row){
            $Pos=strpos(strtoupper($row)," AS ");
            //SI TIENE ALIAS
            if($Pos!==false){
                $nv=substr($row,0,$Pos);
                //$iv=substr($row,$Pos+4,strlen($row));
                //if($iv!="") $ACampos[$key]=$iv;
                $row=EliminaEspacios($nv);
            }
            $ca=Sep_Tabla($row);
            $CAMPOS[]=$ca;
        }
        //OBTENGO TABLAS
        $up=Get_Pos($SQL);
        $PFrom+=6;
        $np=$up-$PFrom;
        $tab=substr($SQL,$PFrom,$np);
        $ATab=explode(",",$tab);
        foreach($ATab as $row){
            $row=LimpiaCadena($row);
            $Pos=strpos(strtoupper($row)," ");
            //SI TIENE ALIAS
            if($Pos!==false){
                $sp=explode(" ",$row);
                $sp[0]=EliminaEspacios($sp[0]);
                $sp[1]=EliminaEspacios($sp[1]);
                if($sp[1]!="") $ALIAS[$sp[1]]=$sp[0];
            }
        }
        //OBTENGO RELACIONES
        $nsql=substr($SQL,$up,strlen($SQL));
        $up2=Get_Pos($SQL,array(" LEFT JOIN "," RIGHT JOIN "," INNER JOIN "));
        $np=$up2-$up;
        $join=substr($SQL,$up,$np);
        //OBTENGO TABLAS RELACIONADAS
        $output=multiexplode(array(" LEFT JOIN "," RIGHT JOIN "," INNER JOIN "),$join);
        $S=array();
        foreach($output as $key=>$row){
            $alt=multiexplode(array("ON ","on "),$row);
            $S[]=$alt[0];
        }
        foreach($S as $key=>$row){
            $Pos=strpos(strtoupper($row)," ");
            //SI TIENE ALIAS
            if($Pos!==false){
                $sp=explode(" ",$row);
                $sp[0]=EliminaEspacios($sp[0]);
                $sp[1]=EliminaEspacios($sp[1]);
                if($sp[1]!="") $ALIAS[$sp[1]]=$sp[0];
            }
        }
        //AGREGO A SALIDA
        foreach($CAMPOS as $key=>$row){
            $tabla=$row[0];
            $campo=$row[1];
            $tabla=EliminaEspacios($tabla);
            $campo=EliminaEspacios($campo);
            if($ALIAS[$tabla]!="") $tabla=$ALIAS[$tabla];
            if($campo=="*"){
                //$link=new PDO('mysql:host=localhost;dbname='.$BASE,'root','12345678');
                $r_campo=$link->query("DESCRIBE ".$tabla);
                foreach($r_campo as $row){
                    $SCampos[]=$tabla.".".$row[0];
                }
            }else{
                $SCampos[]=$tabla.".".$campo;
            }
        }
    }
    return $SCampos;
}

This code manages to solve basic queries with SELECT , LEFT JOIN , RIGHT JOIN , INNER JOIN , Alias in Tables and Fields

$SQL="SELECT
users.*, 
u.nombre AS 'sucu', 
s.nombre 
FROM users u
LEFT JOIN sucursales s ON s.idsucursales=users.idsucursales";

$SCampos=GetCampos("test",$SQL);
foreach($SCampos as $row){
    echo $row."<br>";   
}

This would be the result

users.idusers
users.nombre
users.fecha
users.idsucursales
users.idgrupo
users.nombre
sucursales.nombre

There is still a lot to be able to use more complex querys but I think it works as an example, what do you think ??? how do we improve it ??? how do we add sub querys ???

Here are some screenshots of the application

As it is a somewhat extensive code to post it, I uploaded it to a server in case you are interested in playing with the

DOWNLOAD CODE

Thanks for your time:)

Update 5/9/18

Our friend @A. Cedano gives us a great contribution.

<?
$SQL="SELECT u.nombre, s.nombre FROM  users u LEFT JOIN sucursales s ON s.idsucursales=u.idsucursales";

$link=new PDO('mysql:host=localhost;dbname=test',"root","12345678",array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$stmt=$link->prepare($SQL);
$stmt->execute();
$meta = $stmt->getColumnMeta(0);
echo "<pre>";
var_dump($meta);
echo "</pre>";


$con = new mysqli('localhost', 'root', '12345678', 'test'); 
$stmt = $con->prepare($SQL)or die("failed statment"); 
$stmt->execute(); 
$meta = $stmt->result_metadata(); 
echo "<pre>";
var_dump($meta);
echo "</pre>";
?>

Applying it does not give the following results

array(7) {
  ["native_type"]=>
  string(4) "BLOB"
  ["pdo_type"]=>
  int(2)
  ["flags"]=>
  array(1) {
    [0]=>
    string(4) "blob"
  }
  ["table"]=>
  string(1) "u"
  ["name"]=>
  string(6) "nombre"
  ["len"]=>
  int(-1)
  ["precision"]=>
  int(0)
}
object(mysqli_result)#2 (5) {
  ["current_field"]=>
  int(0)
  ["field_count"]=>
  int(2)
  ["lengths"]=>
  NULL
  ["num_rows"]=>
  int(0)
  ["type"]=>
  int(1)
}

Here is the source table but still masked. We continue in the search:)