current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have two tables.

The finalists table which lists the finalists with fields such as ID, Name. The Votes table which stores the votes for those finalists. One row in the vote tables where the field "confirmed" is yes means one vote.

To get the number of votes for a particular finalist I run

$this->db->where('finalist_id', $finalist_id);
$this->db->where('confirmed', 'yes');
$query = $this->db->get('votes');
return $query->num_rows();

And that gets me the number of rows.(votes)

Now what I want to do is loop through my finalists and list their name, their ID and their number of votes but I am struggling to write the code in my controller.

My controller :

$this->load->database();

            $query = $this->db->get('finalists');
            $data['finalists'] = $query->result_array();


                $this->db->where('finalist_id', $finalist->id);
                $this->db->where('confirmed', 'yes');
                $query = $this->db->get('votes')
                $data['votes'] = $query->result_array();


    $this->template
            ->build('admin/listing', $data);

My view:

<?php foreach ($finalists as $finalist): ?>



<li>ID:<?php echo $finalist['id'] ?>, Name:<?php echo $finalist['name'] ?>, votes:<?php echo $finalist['votes'] ?></li>


<?php endforeach ?>

SO what I am trying to achieve is to add the key vote and its result to the array $finalists - obviously to the right one, and I can't get my head around how to do that.

I suppose I should loop through the finalists and somehow insert the result of the query for that particular finalist.

Any help would be appreciated.

share|improve this question
    
You don't want to loop and make a query for every finalist. What you need to learn how to do is use table JOIN and the SUM() function in conjunction with GROUP BY. This can allow you to get your entire result set in a single query. Though I don't use codeigniter, I am sure there is documentation out there on how to do this. –  Mike Brant Aug 6 '13 at 0:45
    
The thing is that the data I need from one of the tables is only the number of rows returned by a particular query. I think you're right that it must be possible in a single query but I have no idea how. I thought about looping through the results of the finalists query and query the number of rows for each ID so it can be pushed into the $data array along with the finalists other details. –  Ben Dubuisson Aug 6 '13 at 1:20
1  
Work on the raw SQL query first, then you can figure out how to implement in codeigniter second. You have to have an understanding of the join first. Something like SELECT f.id, f.name, COUNT(v.confirmed) AS votes FROM finalists AS f INNER JOIN votes AS v on f.id = v.finalist_id WHERE v.confirmed = 'yes' GROUP BY f.id should get you started. –  Mike Brant Aug 6 '13 at 1:34
    
This link should give you a better idea as to how to form more complex queries using codeigniter's ActiveRecord class. ellislab.com/codeigniter/user-guide/database/active_record.html –  Mike Brant Aug 6 '13 at 1:37
    
Hey Mike, that worked great thank you. Any way of grouping by vote ? I tried but it says there is no such column... Do you want to put your comment as an answer so I can mark it as the right answer ? –  Ben Dubuisson Aug 6 '13 at 21:15
show 2 more comments

2 Answers

up vote 0 down vote accepted

Work on the raw SQL query first, then you can figure out how to implement in codeigniter second. You have to have an understanding of the join first. Something like this should get you started:

SELECT f.id, f.name, COUNT(v.confirmed) AS votes
FROM finalists AS f
INNER JOIN votes AS v
  ON f.id = v.finalist_id
WHERE v.confirmed = 'yes'
GROUP BY f.id

This link should give you a better idea as to how to form more complex queries using codeigniter's ActiveRecord class. ellislab.com/codeigniter/user-guide/database/active_record.html

share|improve this answer
add comment
up vote 0 down vote

Try this into your controller

   $this->load->database();
   $this->db->select('*');
   $this->db->from('finalists');
   $this->db->join('votes',finalists.id = votes.finalist_id);
   $this->db->where('votes.confirmed', 'yes');

   $query = $this->db->get();
   $data['finalists'] = $query;


   $this->template->build('admin/listing', $data);

and this will your view

<?php foreach ($finalists->result() as $finalist){ ?>



<li>ID:<?php echo $finalist->id; ?>, Name:<?php echo $finalist->name; ?>, votes:<?php echo $finalist->votes; ?></li>


<?php } ?>

UPDATE

share|improve this answer
    
Can't use $finalist->id as it doesn't refer to anything –  Ben Dubuisson Aug 6 '13 at 1:13
    
$finalist->id is contain your id because i m now using result() in foreach –  Azam Alvi Aug 6 '13 at 1:21
    
Message: Undefined variable: finalist - Trying to get property of non-object - Fatal error: Cannot use object of type stdClass as array –  Ben Dubuisson Aug 6 '13 at 1:43
    
ok then tell me what is your Primary key of finalists table? –  Azam Alvi Aug 6 '13 at 2:39
    
the primary key is "id" –  Ben Dubuisson Aug 6 '13 at 3:08
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.