Extract two fields from a SQL table with JOIN in PHP

Question

Extract two fields from a SQL table with JOIN in PHP

Navigation

#1 by (0 votes)

1

The problem is this, I want to get 2 fields out of a table.

This is the part of the page that should show the results.

Here is the code for that part:

<div class="panel panel-info">
   <div class="panel-heading text-center"><i class="fa fa-refresh fa-2x"></i><h3>Actualizar estado de pedido</h3></div>
  <div class="table-responsive">
      <table class="table table-bordered">
          <thead class="">
              <tr>
                  <th class="text-center">#</th>
                  <th class="text-center">Fecha</th>
                  <th class="text-center">Cliente</th>
                  <th class="text-center">Total</th>
                  <th class="text-center">Estado</th>
                  <th class="text-center">Banco</th>
                  <th class="text-center">Referencia</th>
                  <th class="text-center">opciones</th>
              </tr>
          </thead>
          <tbody>
              <?php
                $pedidoU=  ejecutarSQL::consultar("select * from venta");
                $upp=1;
                while($peU=mysql_fetch_array($pedidoU)){
                    echo '
                        <div id="update-pedido">
                          <form method="post" action="process/updatePedido.php" id="res-update-pedido-'.$upp.'">
                            <tr>
                                <td>'.$peU['NumPedido'].'<input type="hidden" name="num-pedido" value="'.$peU['NumPedido'].'"></td>
                                <td>'.$peU['Fecha'].'</td>
                                <td>';
                                    $conUs= ejecutarSQL::consultar("select * from cliente where NIT='".$peU['NIT']."'");
                                    while($UsP=mysql_fetch_array($conUs)){
                                        echo $UsP['Nombre'];
                                    }
                        echo   '</td>
                                <td>'.$peU['TotalPagar'].'</td>
                                <td>
                                    <select class="form-control" name="pedido-status">';
                                        if($peU['Estado']=="Pendiente"){
                                           echo '<option value="Pendiente">Pendiente</option>'; 
                                           echo '<option value="Entregado">Entregado</option>'; 
                                        }
                                        if($peU['Estado']=="Entregado"){
                                           echo '<option value="Entregado">Entregado</option>';
                                           echo '<option value="Pendiente">Pendiente</option>'; 
                                        }

                        echo        '
                              <td>BANCO</td>
                              <td>REFERENCIA</td>
                                </td>
                                <td class="text-center">
                                    <button type="submit" class="btn btn-sm btn-primary button-UPPE" value="res-update-pedido-'.$upp.'">Actualizar</button>
                                    <div id="res-update-pedido-'.$upp.'" style="width: 100%; margin:0px; padding:0px;"></div>
                                </td>
                            </tr>
                          </form>
                        </div>
                        ';
                    $upp=$upp+1;
                }
              ?>
          </tbody>
      </table>
  </div>

Here is an image of the tables and relationships

You have to make certain modifications and of all that is the one that has given me the most problems. I have no idea, I tried doing it with inner join but it always throws me syntax error or some other error.

The queries are in AJAX , I do not understand that much.

sql html php ajax phpmyadmin

asked by Endry Rodriguez 30.05.2017 в 04:09

source

1 answer

How to synchronize the modifications we make in the repo of our machine with the repo github? List query in MySQL

score 0 · Answer 1

Even if it's a bit late, I add the answer in case it works. To get the data you want to present, you need to make connections between the tables using the relational keys, according to your scheme this would be the query to fill in the table:

SELECT d.NumPedido, v.Fecha, v.TotalPagar, v.Estado, v.ref,
c.Nombre, p.banco, p.ref as referencia
FROM detalle d
INNER JOIN venta v ON v.NumPedido = d.NumPedido
INNER JOIN cliente c ON c.NIT = v.NIT
INNER JOIN pago p ON p.nit = C.NIT

If you wanted only the tuple of a certain order, you would have to add:

WHERE d.NumPedido = '".$numpedido."'

I have taken the ref values of the two tables because I do not know which corresponds to the one you want to show.

Just as advice in case it is in your hand to modify the tables, it is a little strange to link the payment to the sale through the client, the logical thing would be to join the payments to sales and both to clients, since a client You can have several sales, and each sale at least one payment. The current structure only acquires meaning if the payments correspond to deadlines or to an accumulation of different sales, perhaps this is the case.