Jump to content

select statements and html tables


glendango
 Share

Go to solution Solved by Barand,

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

}
      
?>
Edited by glendango
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

Edited by glendango
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. 

Edited by glendango
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

Edited by glendango
Link to comment
Share on other sites

  • Solution

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

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • 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.