How to put the count result from a query in view.

[eluser]gazza7364[/eluser]
Hi

I have the following query which brings me the count results of registrations plus the user info.

Controller snippet

Code:

$sql = "SELECT user.user_id, user.s_name, user.f_name, user.year, it_club.date, it_club.room, it_club.id, COUNT( it_club.user_id )
       FROM it_club
       INNER JOIN user ON user.user_id = it_club.user_id
       WHERE it_club.date
       BETWEEN '$start'
       AND '$end'
       GROUP BY it_club.user_id
       ORDER BY `user`.`user_id` ASC";

     $result['data'] = $this->it_model->lunch_id($sql);

   //echo "<pre>";
      //print_r($result);
   //echo "</pre>";
$this->load->view('table', $result)

Model snippet

Code:

function lunch_id($sql) {
  $query = $this->db->query($sql);

  if ($query->num_rows() > 0)
        {
                foreach ($query->result() as $row)
                {

                $data[] = $row;
        }

         }
  else
{

          return false;    
        }
        return $data;

view snippet

Code:

<table id = "test-table" width = "100%"><th><div class="results">First Name</div></th><th><div "class="results">Surname</th><th><div "class="results">Year</th><th>Date/Time</th><th>Room</th><th>Registration Count by Range dates</th>


   &lt;? foreach ($data as $names) :?&gt;
   <tr><td>&lt;?= $names->f_name?&gt;</td><td>&lt;?=$names->s_name ?&gt;</td><td>&lt;?=$names->year?&gt;</td><td>&lt;?=$names->date?&gt;</td> <td>&lt;?=$names->room?&gt;</td>

  &lt;? endforeach; ?&gt;

if i run

Code:

//echo "<pre>";
      //print_r($result);
   //echo "</pre>";

I get the correct information for each user,

[0] => stdClass Object
(
[user_id] => 6
[s_name] => joe
[f_name] => bloggs
[year] => Year 99
[date] => 2013-03-06
[room] => IT1
[id] => 2504
[COUNT( it_club.user_id )] => 1
)


The problem I've having is how to put this part in the view

Code:

COUNT( it_club.user_id )

I tried it this way

Code:

<td>&lt;?=$names->COUNT( it_club.user_id ) ?&gt;</td>

this only brings back one row without the count value.
Any ideas?

[eluser]Lifekent[/eluser]
Hi, u just want to count all users?

[eluser]gazza7364[/eluser]
[quote author="Lifekent" date="1363783334"]Hi, u just want to count all users?[/quote]

What the database does, is register users who come to a IT club at dinner each day and makes a record for each user.

I use two tables, users and it_club.
The it_club table keeps a record of which room and date the users use.

If I do a query for the users for a date range, say a month.
I get back users showing when he used the room and date, but if he used say, IT1 6 times, I would get back the same user 6 times with 6 different dates. All I want back is the user with the number of times he used the room. The query above works fine when used in phpmyadmin, showing the user name , room and number of times used.

When i used

Code:

echo "<pre>";
      print_r($result);
   echo "</pre>";

It shows the expected results.

I think its this part in the view whats causing the problem.

Code:

<?=$names->COUNT( it_club.user_id ) ?>

Is this the way it should be entered in the view?

[eluser]Lifekent[/eluser]
[quote author="gazza7364" date="1363785321"][quote author="Lifekent" date="1363783334"]Hi, u just want to count all users?[/quote]

What the database does, is register users who come to a IT club at dinner each day and makes a record for each user.

I use two tables, users and it_club.
The it_club table keeps a record of which room and date the users use.

If I do a query for the users for a date range, say a month.
I get back users showing when he used the room and date, but if he used say, IT1 6 times, I would get back the same user 6 times with 6 different dates. All I want back is the user with the number of times he used the room. The query above works fine when used in phpmyadmin, showing the user name , room and number of times used.

When i used

Code:

echo "<pre>";
      print_r($result);
   echo "</pre>";

It shows the expected results.

I think its this part in the view whats causing the problem.

Code:

<?=$names->COUNT( it_club.user_id ) ?>

Is this the way it should be entered in the view?[/quote]

When u make a query and select "COUNT( it_club.user_id )" just add an alias, for example:
"SELECT COUNT( it_club.user_id ) as uvisits" and then in view u can access it with $names->uvisits.
If u don't have this in query just add it to SELECT statement

[eluser]gazza7364[/eluser]
[quote author="Lifekent" date="1363786231"][quote author="gazza7364" date="1363785321"][quote author="Lifekent" date="1363783334"]Hi, u just want to count all users?[/quote]

What the database does, is register users who come to a IT club at dinner each day and makes a record for each user.

I use two tables, users and it_club.
The it_club table keeps a record of which room and date the users use.

If I do a query for the users for a date range, say a month.
I get back users showing when he used the room and date, but if he used say, IT1 6 times, I would get back the same user 6 times with 6 different dates. All I want back is the user with the number of times he used the room. The query above works fine when used in phpmyadmin, showing the user name , room and number of times used.

When i used

Code:

echo "<pre>";
      print_r($result);
   echo "</pre>";

It shows the expected results.

I think its this part in the view whats causing the problem.

Code:

<?=$names->COUNT( it_club.user_id ) ?>

Is this the way it should be entered in the view?[/quote]

When u make a query and select "COUNT( it_club.user_id )" just add an alias, for example:
"SELECT COUNT( it_club.user_id ) as uvisits" and then in view u can access it with $names->uvisits.
If u don't have this in query just add it to SELECT statement[/quote]

Thanks, for your time that works perfect, I didn't know I could do that, we learn something every day.

[eluser]CroNiX[/eluser]
As pointed out, all you had to do was add an alias to your COUNT(), and then use that alias in the results...

Code:

$sql = "SELECT user.user_id, user.s_name, user.f_name, user.year, it_club.date, it_club.room, it_club.id, COUNT( it_club.user_id ) AS visit_total
       FROM it_club
       INNER JOIN user ON user.user_id = it_club.user_id
       WHERE it_club.date
       BETWEEN '$start'
       AND '$end'
       GROUP BY it_club.user_id
       ORDER BY `user`.`user_id` ASC";

Then when you were looping through your results you just output

Code:

echo $name->visit_total;

Using PHP's count(), while it does indeed "work", is a LOT more inefficient than doing it in the db query since count() has to iterate (internally) over each element of the array in order to give you the total.