select statements and html tables

[glendango]

hi, can anyone advise regarding practice of displaying data from a database to the end user. 

 

If you creating 1 large table with a lot of stats in for users, should you have have 1 massive select statement or do it in individual statments like i have. 

 

my code works ok but i cant work out how to get the 'Total Year' column to populate..the stats i want appear under the user id's...(see attached jpeg)

 

 

 

$result = mysqli_query($conn,"SELECT u.company_id, 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 group by u.id  having u.company_id='".$_SESSION['company_id']."'  ") ;

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

 echo "<tr>";
        //changes date to english format from a time stamp
  echo"<td>".$firsts['company_id']."</td>";
   echo"<td>".$firsts['id']."</td>";
 echo"<td>".$firsts['name']. ' '.$firsts['surname']."</td>";
      
       echo"<td>".$firsts['num_rows']."</td>";
//echo"<td>".$firsts['date_mades']."</td>";

}
$result1 = mysqli_query($conn,"SELECT u.company_id,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)  group by u.id having u.company_id='".$_SESSION['company_id']."'     ");

   while($firsts=mysqli_fetch_array($result1))
//echo "<tr>";
{

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

}
$result = mysqli_query($conn, "SELECT count(f.date_made) as date_mades FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id    and   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_mades']."</td>";
       //echo"<td>".$firsts['date_madess']."</td>";

 

}
      

?>

Edited November 5, 2017 by glendango

[benanamen]

OP, I suggest you post an SQL dump of your DB first so we can make sure that is correct first.

[Barand]

As I have told you twice before, to little effect apparently, use WHERE for that company_id condition and NOT a HAVING clause. Put an index on users(company_id) if you haven't already.

[glendango]

i get an error if i use 'where'  and if i make it work , will it all sit in the table correctly? 

Edited November 5, 2017 by glendango

[Barand]

What error do you get?

[glendango]

i get this error:   Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\******\www\li****\firstsleague.php on line 78

 

when using this :  

 

$result = mysqli_query($conn,"SELECT u.company_id, 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 group by u.id  where u.company_id='".$_SESSION['company_id']."'  ") ;

 

 

also could you explain the theory :    should i only have one query for every html table and join tables together?? or is it ok to have say 4 separate select statements all feeding the results into 1 table?  cheers

Edited November 5, 2017 by glendango

[Barand]

GROUP BY needs to go after the WHERE.

 

EDIT: As for the theory, I have no idea what you are trying to produce.

Edited November 5, 2017 by Barand

[glendango]

your a legend lol..it works.

 

$result = mysqli_query($conn,"SELECT u.company_id, 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 ") ;
 

 

why doesn't my 'group by'  go 'red' like my other code??   anyway

 

now that works,,what about getting it all into the same table,,,, at moment i have 4 separate tables for all the info iam trying to display to the user. 

Edited November 5, 2017 by glendango

[Barand]

now that works,,what about getting it all into the same table,,,, at moment i have 4 separate tables for all the info iam trying to display to the user.

 

Once more I repeat myself - I have no idea exactly what output you want to produce.

[glendango]

did my attachement not show up? ::::   so the 10, 3, 4, 0 show up but not in the 'total year ' column

 

Company id id Name                       Total Ever     Total Year

1               1        Richio Williams           11

1               3        lisa williams                  3

1               9        billy bob                        4

1              16       billy jo                          0

 

10

3

4

0

Total     18

Edited November 5, 2017 by glendango

[Barand]

That last post shows two sets of output. Your code, from what I can decipher from the poorly formatted mess (use code tags), shows three queries and output sets.

 

Having output one table you cannot then append a column to those rows - they've already been output. You need to have the data available before you output.

 

If you use multiple queries you have to store the data in an array then output from the array.

 

Personally, I would prefer to use a single query if I can.