CODEWITHSUNDEEP
phphtmlmysqlcodeigniter
Leon Baños
Leon Baños

Reputation: 13

sql count display number

Simple question: I'm using Codeigniter and MySQL database. I currently have a Table in my database named "profiles" to store each user's information. Back on track, I need to display in an HTML table the number of users registered per location ('location' is one of the fields in my 'profiles' table).

I've tried this, so far: MODEL:

    function asia_count()
{
    $query = "SELECT COUNT(*) FROM profiles WHERE location = 'Asia'";
    return $query;
}

CONTROLLER:

public function admin_m_reports_users_loc()
{
    $data['asia_count'] =$this->help_model->asia_count();
    $this->load->view('squidtopus1-admin-reports-users-location',$data);
}

VIEW: (related line:)

    <tr>
  <td><a href="#">Asia</a></td>
  <td><?php echo $asia_count; ?></td>
  </tr>

In regards to the VIEW(related line), I need it to display as Asia | 1 (1 is example result)

...but I can't track down where I went wrong. Could someone please point me to the right direction? Much appreciated.

Upvotes: 0

Views: 1367

Answers (2)

lighter
lighter

Reputation: 2806

You miss result() or result_array(). You can try this, use this code as your model.

function asia_count()
{
    $query = "SELECT COUNT(*) as Total FROM profiles WHERE location = 'Asia'";
    $result = $query->result_array();
    return $result[0]['Total'];
}

If you want list all location count you can try this

Model

function asia_count()
{
    $query = "SELECT location, COUNT(*) as Total FROM profiles GROUP BY location";
    return $query->result_array();
}

View

foreach ($lists as $index => $row) {
    echo $row['location'] . ':' . $row['Total'];
}

Upvotes: 0

Priyank
Priyank

Reputation: 3868

$query = $this->db->query("SELECT COUNT(*) FROM profiles WHERE location = 'Asia'");

if ($query->num_rows() > 0)
 {
$row = $query->row_array(); 
echo $row['COUNT(*)'];
}

Upvotes: 1

Related Questions