aligning tables with echo

[glendango]

 

Hi, ive been trying to display 2 columns side by side with 2 select statements..   please see attached for results....   for some reason the 2nd select fetches the database values correctly but echo's them horizontally and not vertically like the first columns set of results. 

 

 

Branch            Name             Total Ever              Total Year

Barry             Rich Williams         13                       12            5 4 0 0 0 0

Brum                Lisa Williams        5

London            Stephen Eckley     4

Mythr              Debs Lovelock        0

Basing              Derek Barti            0

Devon             Barry Jims               0

Glasgow          James bonds         0

Total                                               22                       21

 

 

 

 

 

 

<table class="table table-striped  table-bordered  table-hover" style="width: auto; margin: " id="example" cellspacing="0" > <!--, table table-inverse   table-bordered     -->

 <thead >
<tr >
  <th>Branch</th>
 
  <th>Name</th>
        
          <th>Total Ever</th>
           <th>Total Year</th>

   <!--    <th>Total Year</th>    -->
  </tr>
 </thead>

<tbody >
      <?php
    

//TOTAL EVER original
$result = mysqli_query($conn,"SELECT u.company_id, u.branch, u.name ,u.surname,  u.id, count(f.date_made) as num_rows FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id  where u.company_id='".$_SESSION['company_id'].  "'   group by u.id  order by num_rows DESC limit 10  ") ;

 

$result1 = mysqli_query($conn,"SELECT u.company_id,  u.branch,u.name,u.surname,  u.id, count(f.date_made) as date_mades FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id AND DATE(f.date_made) and year(curdate()) = year(date_made)  where u.company_id='".$_SESSION['company_id']."'  group by u.id  order by date_mades DESC limit 10  ");

while($firsts=mysqli_fetch_array($result)){

 

 echo "<tr>";
      
  
  echo"<td>".$firsts['branch']."</td>";
  
 echo"<td>".$firsts['name']. ' '.$firsts['surname']."</td>";
      
       echo"<td>".$firsts['num_rows']."</td>";

while($firsts=mysqli_fetch_array($result1)){

echo"<td>".$firsts['date_mades']."</td>";

   
}
}

 

$result = mysqli_query($conn, "SELECT count(f.date_made) as date_madesss FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id   where u.company_id='".$_SESSION['company_id']."'   ");

 

       while($firsts=mysqli_fetch_array($result)){

      echo "<tr>";
     
echo"<td>".'Total'."</td>";
echo"<td>";
//echo"<td>";
      echo"<td>".$firsts['date_madesss']."</td>";
       //echo"<td>".$firsts['date_madess']."</td>";

 

 

//TOTAL YEAR

  $result = mysqli_query($conn, "SELECT count(f.date_made) as date_mader FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id  WHERE year(curdate()) = year(date_made)  and  u.company_id='".$_SESSION['company_id']."'    ");

 

       while($firsts=mysqli_fetch_array($result)){

     // echo "<tr>";
//echo"<td>".'Total'."</td>";
//echo"<td>".''."</td>";
//echo"<td>".''."</td>";
      echo"<td>".$firsts['date_mader']."</td>";

   

}

 

}
      

?>

</tbody>
</table>
</div>

Edited November 14, 2017 by glendango

[Barand]

As I may have told you before, it is better to use a single query. If you want to output from multiple queries into the same row, it's better to store the data in an array before outputting your results table. But your philosophy still remains "Why use one query when you can struggle with four?";

 

Try

$sql = "SELECT u.company_id
            ,  u.branch
            ,  u.name
            ,  u.surname
            ,  u.id, count(f.date_made) as totalever
            ,  SUM(CASE WHEN YEAR(f.date_made) = YEAR(CURDATE()) THEN 1 ELSE 0 END) as totalyear
        FROM users u 
            LEFT JOIN 
            firsts f 
               ON u.id  = f.usr_id   
        WHERE u.company_id = ? 
        GROUP BY u.id  
        ORDER BY u.branch 
        LIMIT 10";
$stmt = $pdo->prepare($sql);
$stmt-execute( [ $_SESSION['company_id'] ] );

$grand_total_ever = 0;
$grand_total_year = 0;

echo "<table>";
foreach ($stmt as $row) {
    echo "<tr>
            <td>{$row['branch']}</td>
            <td>{$row['name']} {$row['surname']}</td>
            <td>{$row['totalever']}</td>
            <td>{$row['totalyear']}</td>
          </tr>\n";
          $grand_total_ever += $row['totalever'];  // accumulate totals
          $grand_total_year += $row['totalyear'];
}  
echo "<tr><td colspan='3'>Totals</td><td>$grand_total_ever</td><td>$grand_total_year</td></tr>\n";
echo "</table>";

[ginerjm]

Here is what I see happening. Note how I posted my code in the proper code tags too.

<table class="table table-striped table-bordered table-hover" style="width: auto; margin: " id="example" cellspacing="0">
<thead >
<tr >
<th>Branch</th>
<th>Name</th>
<th>Total Ever</th>
<th>Total Year</th>
</tr>
</thead>
<tbody>
<?php
//TOTAL EVER original
$result = mysqli_query($conn,"SELECT u.company_id, u.branch, u.name ,u.surname, u.id, count(f.date_made) as num_rows
FROM users u
LEFT JOIN firsts f ON u.id = f.usr_id
where u.company_id='".$_SESSION['company_id']. "'
group by u.id
order by num_rows DESC limit 10 ");
$result1 = mysqli_query($conn,"SELECT u.company_id, u.branch,u.name,u.surname, u.id, count(f.date_made) as date_mades
FROM users u
LEFT JOIN firsts f ON u.id = f.usr_id AND DATE(f.date_made) and year(curdate()) = year(date_made)
where u.company_id='".$_SESSION['company_id']."'
group by u.id
order by date_mades DESC limit 10 ");
while($firsts=mysqli_fetch_array($result))
{
// start a row here query 1
echo "<tr>";
echo"<td>".$firsts['branch']."</td>";
echo"<td>".$firsts['name']. ' '.$firsts['surname']."</td>";
echo"<td>".$firsts['num_rows']."</td>";
while($firsts=mysqli_fetch_array($result1))
{
// add users data on same row query 2
echo"<td>".$firsts['date_mades']."</td>";
}
// no end of row here!!!
}
$result = mysqli_query($conn, "SELECT count(f.date_made) as date_madesss FROM users u LEFT JOIN firsts f ON u.id = f.usr_id where u.company_id='".$_SESSION['company_id']."' ");
while($firsts=mysqli_fetch_array($result))
{
// start new row(?) with new query data query 3
echo "<tr>";
echo"<td>".'Total'."</td>";
echo"<td>";
echo"<td>".$firsts['date_madesss']."</td>";
//TOTAL YEAR
// WIPING OUT RESULTS OR QUERY 3 HERE BEFORE LOOP FINISHES!!!
$result = mysqli_query($conn, "SELECT count(f.date_made) as date_mader FROM users u LEFT JOIN firsts f ON u.id = f.usr_id WHERE year(curdate()) = year(date_made) and u.company_id='".$_SESSION['company_id']."' ");
while($firsts=mysqli_fetch_array($result))
{
// add more user data to same row query 4
echo"<td>".$firsts['date_mader']."</td>";
}
// NO END OF ROW HERE EITHER.
}
?>
</tbody>
</table>
</div>

[glendango]

Thanks so much for the answers. Wanted to reply before i work through it all. 

Its literal lack of knowledge Barand not an unwillingness. But i totally get your point. Many thanks. 

Iam near the end of my prototype now anyway..  no way i can learn everything on my own including how to make it safe on a live site....  so hopefully my questions will stop soon...until my next app!!!!!!! 

[glendango]

hi handball,, i just worked through what you sent over..it echos same as i already have. what are you saying with the red text..  'put code here'  or are you commenting it out..,, i took  as commenting out. 

 

 

 

barend i see what your doing. i did have a go at trying to slect in 1 statement but could nt get the results due to the statement always seeming to mix the two f.date_made  columns up ( not this time but last week when i tried) ..but i will try again... this has to be the way forward as i love result based apps. 

' 

Edited November 14, 2017 by glendango

[Barand]

Out of curiosity, why would you think ginerjm's name is "handball" when his name appears on his posts in exactly the same place as your name does on yours (unless your name is really "advanced member")?

[ginerjm]

I took your code, cleaned it up to make it decipherable and added some pertinent comments to tell you what I see is wrong.

 

I don't know what you are doing with ALL THE QUERIES!!! but I do see that you html is screwed up.

[glendango]

lol   cheers handball ,  i mean ginerjm...  whats going on?  i cut and paste into your text field from sublime  and that's how it saves. 

 

 

 

 

i get undefined variablle for pdo  Barend.

Edited November 15, 2017 by glendango

[AzeS]

This one is escalating a bit... Be respectfull to each other arght ? 

[Barand]

i get undefined variablle for pdo Barend.

@glendengo: I used $pdo as a connection name to make it blindingly obvious that a PDO connection was being used.

 

Adapt the code to use a mysqli prepared statement instead.

Edited November 15, 2017 by Barand

[Barand]

It will look like this

[

$sql = "SELECT u.company_id
            ,  u.branch
            ,  u.name
            ,  u.surname
            ,  u.id, count(f.date_made) as totalever
            ,  SUM(CASE WHEN YEAR(f.date_made) = YEAR(CURDATE()) THEN 1 ELSE 0 END) as totalyear
        FROM users u 
            LEFT JOIN 
            firsts f 
               ON u.id  = f.usr_id   
        WHERE u.company_id = ? 
        GROUP BY u.id  
        ORDER BY u.branch 
        LIMIT 10";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $_SESSION['company_id'] );
$stmt-execute();
$stmt->bind_result($company,$branch,$name,$surname,$totalever,$totalyear);

$grand_total_ever = 0;
$grand_total_year = 0;

echo "<table>";
while ($stmt->fetch()) {
    echo "<tr>
            <td>$branch</td>
            <td>$name $surname</td>
            <td>$totalever</td>
            <td>$totalyear</td>
          </tr>\n";
          $grand_total_ever += $totalever;  // accumulate totals
          $grand_total_year += $totalyear;
}  
echo "<tr><td colspan='3'>Totals</td><td>$grand_total_ever</td><td>$grand_total_year</td></tr>\n";
echo "</table>";

Edited November 15, 2017 by Barand

[glendango]

oh, thanks.. i love a bit of cryptic code mixed in with my code. Azes  ,, lol  i deserve it. 

[Barand]

Pardon me for trying to help, it won't happen again.