Consultation of Inner Join to the same table

Question

Consultation of Inner Join to the same table

Navigation

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

2

in advance thanks:)

I have a small question, I must take the data from an id that is in the table using phalcon with mvc.

The question is this, my table is this:

|-----------|-----------|----------|--------------|----------------|
| idusuario | nombre    | apellido | clase        | idpatrocinador |
|-----------|-----------|----------|--------------|----------------|  
| 20065     | María     | Perez    | distribuidor | **20012**      |
|-----------|-----------|----------|--------------|----------------|
| **20012** | Christian | Ramirez  | consumidor   | 20015          |
|-----------|-----------|----------|--------------|----------------|

If you notice, Christian is Maria's sponsor and what I want is to show Christian's data in the view instead of his id.

In my controller I have this:

<?php
class PruebaController extends \Phalcon\Mvc\Controller
{
public function pruebaAction()
{
}

public function indexAction()
{
    //Primera consulta general
    $usuarios = Usuario::find();
    $this->view->usuarios = $usuarios;

    //Segunda consulta para patrocinadores
    $query = "SELECT * FROM usuario INNER JOIN usuario ON usuario.idusuario=usuario.idpatrocinador";
    $sentencia = $this->db->prepare($query);
    $sentencia->execute();
    $row = $sentencia->fetch();
    $this->view->row = $row;

}
}

And in the view this is my table in the view:

<table class="table table-striped  table-bordered table-hover"  
 id="enviar_usuario">
 <thead>
 <tr>
  <th scope="col">#</th>
  <th scope="col">Nombre completo</th>
  <th scope="col">Clase</th>
  <th scope="col">Patrocinador</th>
  <th scope="col">Acción</th>
  </tr>
  </thead>
  <tbody>
   <?php 
    foreach($usuarios as $user)
    {
?>
<tr>
  <th><?php echo $user->idusuario ?></th>
  <td><?php echo $user->nombre . ' ' . $user->apellido; ?></td>
  <td><span class='badge badge-info'><?php echo $user->clase ?></span>";      
  </td>
<td> <!-- Aqui debe ir los datos del patrocinador. Su nombre completo, id y clasesph -->
</td>
  <td>
    <button type="button" class="btn btn-warning btn-sm" id="id_usuario" onclick="enviarid('<?php echo $user->idusuario; ?>')" ><i class="icon-pencil5 position-left"></i>
    Editar</button>
  </td>
</tr>
<?php 
    }
?>

I would greatly appreciate those who could help me

mysql html php phalcon

asked by Isaacnia Majano 22.03.2018 в 22:43

source

2 answers

2

Doing a JOIN to the same table is considered a recursive type relationship, it is solved by the use of aliases.

SELECT u2.*
FROM usuario u1
INNER JOIN usuario u2 ON u1.idusuario = u2.idpatrocinador

You have more information here .

answered by 22.03.2018 в 23:11

Error getting user's location - Android GPS How to clean the input buffer?

score 0 · Accepted Answer

You do not need to make a second query to get the sponsors. You can generate the sponsor arrangement in the controller right there

$usuarios = Usuario::find();
$patrocinadores = array();
$foreach ($usuarios as $usuario) {
    $patrocinadores[$usuario->idusuario] = $usuario;
}
$this->view->usuarios = $usuarios;
$this->view->patrocinadores = $patrocinadores;

in the view, you can do the following

    <?php 
    foreach($usuarios as $user)
    {
?>
<tr>
  <th><?php echo $user->idusuario ?></th>
  <td><?php echo $user->nombre . ' ' . $user->apellido; ?></td>
  <td><span class='badge badge-info'><?php echo $user->clase ?></span>";      
  </td>
<td> 
    <?php
    $patrocinador = $patrocinadores[$user->idpatrocinador];
    echo $patrocinador->nombre . ' ' . $patrocinador->apellido . ' ' . $patrocinador->idusuario . ' ' . $patrocinador->clase;
    ?>
</td>
  <td>
    <button type="button" class="btn btn-warning btn-sm" id="id_usuario" onclick="enviarid('<?php echo $user->idusuario; ?>')" ><i class="icon-pencil5 position-left"></i>
    Editar</button>
  </td>
</tr>
<?php 
    }