Jump to content

select statements and html tables


glendango

Recommended Posts

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>";
 

}
      
?>
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

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.