how to display mysql count on database item to view

[eluser]Neoraj3.0[/eluser]
Hi I am getting trouble to echo a count of 3 to my view but it works fine when i test in the Controller:
Please help. I am trying to count each time image name in the databse.

Model:

Code:

function count_uploads()
  {
  $query = $this->db->query("SELECT COUNT(image) FROM projects");
  return $query->num_rows();

  }

Controller:

Code:

function thetotaluploads()  
  {
  $this->load->helper('url');
  $this->load->view('templates/header');


  $data['no_of_uploads'] = $this->project->count_uploads();


  //for testing without a View
  //echo '<pre>'; print_r( $data['no_of_uploads']); echo '<pre/>';

  $this->load->view('viewprojects2', $data);

  $this->load->view('templates/footer');
  
  }

View:

Code:

<?php echo $no_of_uploads; ?>

I get this error for the view:

A PHP Error was encountered

Severity: Notice

Message: Array to string conversion

Filename: views/viewprojects2.php

Line Number: 114
Array


I also tried <?php echo $data['no_of_uploads'] ?> in the view

[eluser]Tim Brownlaw[/eluser]
Well a few questions

Is image the name of a field ( column ) in your table called projects?

Code:

function count_uploads()
{
  $query = $this->db->query("SELECT COUNT(image) FROM projects");
  return $query->num_rows();
}

So is

Code:

SELECT COUNT(image) FROM projects

a valid statement?
Performing a COUNT operation will only ever result in num_rows being 1, Row with a result of count.

You can do something like

Code:

function count_uploads()
{
  return $this->db->count_all('projects');
}

OR the ole fashioned way ( not tested this so it's... not tested )

Code:

function count_uploads()
{
  $query = $this->db->query("SELECT COUNT(image) as count FROM projects");
  if($query->num_rows() >0 )
  {
    $row =  $query->row();
     return $row->count; // return the count
  }
  return 0; // if we get there We don't have anything.
}

I dont know what your primary key is... if it''s something like 'id' or 'project_id' - use that instead of 'image'.

Read the User Guide on Active records... and experiment!

[eluser]Neoraj3.0[/eluser]
Yes sorry. image is a column name. So there are 3 records in the db table with 3 image names under that column.

Your solution works but doesn't that count records as opposed to image names?

[eluser]Tim Brownlaw[/eluser]
Ok so you can have duplicate image names and you only want to count unique instances of those?

If you have 3 image names... you have 3 records, don't you?

You'll have to give some example of what you have, if I'm not understanding you!

[eluser]Neoraj3.0[/eluser]
well I was looking to count where there was data present only. so if there is a record with no image I do not want it to be counted.


In other words i'm looking for a 'count where' scenario. I tried this in the model but cannot get it to display in the view.

Code:

function count_uploads()
  {
  $this->db->select('COUNT(image)');
  $this->db->from('projects');
  $this->db->where(array('image !=' => ''));
  $query = $this->db->get();
  return $query->result_array();
                }

[eluser]Tim Brownlaw[/eluser]
Ok so what about something like..

Code:

function count_uploads()
{
  $this->db->select('COUNT(image) as count');
  $this->db->from('projects');
  $this->db->where(array('image !=' => ''));
  $query = $this->db->get();
  if ($query->num_rows() > 0 )
  {
    $row = $query->row();
    return $row->count;
  }
  return 0;
}

That's Working on the basis that you want to return an integer!

[eluser]Neoraj3.0[/eluser]
Yip perfecto! that works. many thanks.

[eluser]Tim Brownlaw[/eluser]
Grand and you are welcome... Now I can get to bed

[eluser]Unknown[/eluser]
you can use

Code:

$this->db->select('COUNT(DISTINCT image) as count');

use of DISTINCT will avoid duplicates of same image, this will simplify the code