Jump to content

aligning tables with echo


glendango

Recommended Posts

 
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>
Link to comment
Share on other sites

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>";
Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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!!!!!!! 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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>";
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.