How can you get the number of matches in a mysql query with left join?

Question

How can you get the number of matches in a mysql query with left join?

Navigation

#1 by (1 votes)

0

I have in a database a table where the logins of each user are registered, and another where I store the modifications that the same user makes in the system.
If the user does not make any changes to the system, nothing will be saved in the table of modifications.

I need to print a list in html where every <li></li> is a login and only if in the session changes were made to embed a <ul><ul> where all the corresponding modifications to that session will be.

For example:

<ul>
 <li>27-02-2017</li>
 <li>28-02-2017
     <ul>
         <li>Creo usuario nuevo</li>
         <li>Cambio departamento a usuario</li>
     </ul>
 </li>
 <li>01-09-2017
     <ul>
         <li>Elimino usuario</li>
     </ul>
 </li>
 <li>02-02-2018</li>

and in php what I have is something like that

<?php
$query = "SELECT a.field1, a.field2, b.field3, b.field4 from tabla_a a left join tabla_b b on a.field1 = b.field4";
$query = mysqli_query($connect,$query);
echo "<ul>"
while($inf = mysqli_fetch_array()){
    echo "<li>{$inf["field2"]}</li>"
}
echo "</ul>";
?>

What I do not know is how to make if the number of matches with left join is equal to or greater than 1 insert the <ul></ul> , it occurred to me to make a query within the first query but the response would be too slow.

Any suggestions? Thanks in advance.

mysql php mysqli

asked by Fernando Ferretiz 23.02.2018 в 19:45

source

1 answer

I can not link UIButton with IBOutlet or IBActins do not work, Object Exit by default !! Xamarin Json deserealization

score 1 · Accepted Answer

Keep a variable with the value of field1 to know when it changes its value. In addition you have to check if field4 has a value to know if you have to add another li within a ul . The query must be sorted by field1 so as not to skip data.

    <?php
    $query = "SELECT a.field1, a.field2, b.field3, b.field4 from tabla_a a left join tabla_b b on a.field1 = b.field4 ORDER BY field1";
    $query = mysqli_query($connect,$query);
    echo "<ul>";
    $pivote = -1;
    $inf = mysqli_fetch_array();
    while($inf){
      if ($pivote != $inf["field1"]){
          echo "<li>{$inf["field2"]}</li>";
          $pivote = $inf["field1"];
      }
      if ($inf["field4"]){
          echo "<lu>";
          do {
              if ($pivote != $inf["field1"]){
                 break;
              }
              echo "<li>{$inf["field3"]}</li>";
          }while ($inf = mysqli_fetch_array());
          echo "</ul>";
      }else{
          $inf = mysqli_fetch_array();
      }
    }
    echo "</ul>";
    ?>