Welcome Guest, Not a member yet? Register   Sign In
selecting data from 2 tables
#1

[eluser]Bigil Michael[/eluser]
can any one help me the problem is "resut repeating".

my tables
Quote:table name: constituency_details


id constituency_name constituency_id elect_year

1 vattiyoorkavu 4 2010
2 parassala 1 2011
3 neyyattinkara 2 2011
8 nemom 9 2008

tablename:candidate_details

id constituency_id candidate_name candidate_party elect_year

1 2 Selvaraj CPM 2011
2 4 babu incu 2010
4 1 George INC 2011
5 1 Nagappan CPM 2011
6 1 Suresh BJP 2011
7 2 Ravi INC 2011
8 2 sajith BJP 2011

my controller
Quote:function index($pgoffset='')
{
$config['per_page'] = 9;

$this->data['pagetitle'] ='Election Results';

$build_array = array();
$fleets = $this->Election_result_model->select_constituency($config['per_page'], $pgoffset);

foreach($fleets as $row){
$build_array[] = array (
'fleets_array' => $row,
'listefleets_array' => $this->Election_result_model->list_constituency($row['constituency_id'])
);
}
$this->data['meow'] = $build_array;

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

model
Quote:function select_constituency($limit=NULL, $pgoffset=NULL)
{
$sql = "SELECT constituency_details.constituency_name, constituency_details.elect_year, constituency_details.constituency_id FROM constituency_details JOIN candidate_details ON (candidate_details.constituency_id = constituency_details.constituency_id AND candidate_details.elect_year = constituency_details.elect_year) ORDER BY constituency_details.constituency_name";

$query = $this->db->query($sql);
return $query->result_array();
}

function list_constituency($cid)
{
$this->db->where('constituency_id',$cid);
$this->db->order_by('candidate_name');
$result_product = $this->db->get('candidate_details');
return $result_product->result_array();
}
view
Quote: <div id="t1">

&lt;?php
foreach ($meow as $crow)
{ ?&gt;
<div id="t2">
<div style="padding-bottom:10px; font-size:14px;">&lt;?php echo $crow['fleets_array']['constituency_name'];?&gt; &nbsp;&nbsp;&nbsp;&lt;?php echo $crow['fleets_array']['elect_year'];?&gt; </div>

&lt;?php
foreach ($crow['listefleets_array'] as $lrow)
{
?&gt;
<p>&lt;?=$lrow['candidate_name'];?&gt;</p>
<p>&lt;?= $lrow['candidate_party'];?&gt;</p>
<p>&lt;?= $lrow['candidate_votes'];?&gt; </p>
<p>&lt;?= $lrow['percentage_votes'];?&gt;</p>
&lt;?php
}
?&gt;
</div>
&lt;?php
}
?&gt;

</div>

it is working but result is repeating
result like this
Quote:neyyattinkara 2011

Ravi

INC

879879

20

sajith

BJP

7878

5

Selvaraj

CPM

123456876

75
neyyattinkara 2011

Ravi

INC

879879

20

sajith

BJP

7878

5

Selvaraj

CPM

123456876

75
neyyattinkara 2011

Ravi

INC

879879

20

sajith

BJP

7878

5

Selvaraj

CPM

123456876

75

can anyone help me
i dont know how to remove this problem
urgent.......

thanks in advance.......
#2

[eluser]toopay[/eluser]
In your model, at select_constituency function, try change your query to...
Code:
$sql = "SELECT DISTINCT constituency_details.constituency_name, constituency_details.elect_year, constituency_details.constituency_id "
      ."FROM constituency_details "
      ."JOIN candidate_details "
      ."ON (candidate_details.constituency_id = constituency_details.constituency_id "
      ."AND candidate_details.elect_year = constituency_details.elect_year) "
      ."ORDER BY constituency_details.constituency_name";
#3

[eluser]Bigil Michael[/eluser]
thans for your reply

that problem solved

but now 2010 result and 2011 result are combined

please look at parassala

here ssssssssssssss ssssssssss 11111111111111 1 this result is with respect to the year 2010


neyyattinkara 2011

Ravi INC 879879 20

sajith BJP 7878 5

Selvaraj CPM 123456876 75

parassala 2010

George INC 6756576576 75

Nagappan CPM 878787 15

ssssssssssssss ssssssssss 11111111111111 1

Suresh BJP 67565 5

parassala 2011

George INC 6756576576 75

Nagappan CPM 878787 15

ssssssssssssss ssssssssss 11111111111111 1

Suresh BJP 67565 5

vattiyoorkavu 2010

babu incu 4678 83
#4

[eluser]toopay[/eluser]
I don't have an idea what your data means, but i expected above data had 'order' problems. It its correct, just modify above query into :
Code:
$sql = "SELECT DISTINCT constituency_details.constituency_name, constituency_details.elect_year, constituency_details.constituency_id "
      ."FROM constituency_details "
      ."JOIN candidate_details "
      ."ON (candidate_details.constituency_id = constituency_details.constituency_id "
      ."AND candidate_details.elect_year = constituency_details.elect_year) "
      ."ORDER BY constituency_details.elect_year, constituency_details.constituency_name";
#5

[eluser]Bigil Michael[/eluser]
i solved the above problem

now i want to apply the limit inside your code

can u help me??
thank you very much for your help.
#6

[eluser]toopay[/eluser]
Something like...
Code:
$sql = "SELECT DISTINCT constituency_details.constituency_name, constituency_details.elect_year, constituency_details.constituency_id "
      ."FROM constituency_details "
      ."JOIN candidate_details "
      ."ON (candidate_details.constituency_id = constituency_details.constituency_id "
      ."AND candidate_details.elect_year = constituency_details.elect_year) "
      ."ORDER BY constituency_details.elect_year, constituency_details.constituency_name ";
if( ! is_null($limit) AND $limit != '')
{
    if( ! is_null($pgoffset) AND $pgoffset != '')
    {
        $sql .= "LIMIT ".$pgoffset." , ".$limit;
    }
    else
    {
        $sql .= "LIMIT 0 , ".$limit;  
    }
}
#7

[eluser]Bigil Michael[/eluser]
limit is woking now

now i want to find total no:of rows for pagination

i used this query to find count
Quote:function get_total()
{
$count=0;
$sql = "SELECT COUNT(id) AS total "
."FROM constituency_details "
."JOIN candidate_details "
."ON (candidate_details.constituency_id = constituency_details.constituency_id "
."AND candidate_details.elect_year = constituency_details.elect_year) "
."ORDER BY constituency_details.elect_year DESC ";
if($sql->num_rows()>0){
$row = $sql->row();
$count = $row->total;
}
return $count;
}
but it shows an error "Call to a member function num_rows() on a non-object"
can u tell me what is the problem???
thanks in advance
#8

[eluser]Bigil Michael[/eluser]
can anyone help me????
#9

[eluser]Bigil Michael[/eluser]
i want to display the person with maximum votes as result

so i changed my controller
Quote:function index($pgoffset=’‘)
{
$config[‘per_page’] = 9;

$this->data[‘pagetitle’] =‘Election Results’;

$build_array = array();
$fleets = $this->Election_result_model->select_constituency($config[‘per_page’], $pgoffset);

foreach($fleets as $row){
$build_array[] = array (
‘fleets_array’ => $row,
‘listefleets_array’ => $this->Election_result_model->list_constituency($row[‘constituency_id’])
);
}
$this->data[‘meow’] = $build_array;

$this->load->view(‘election_result’,$this->data);
}

to
Quote:function index($pgoffset='')
{
$config['per_page'] = 9;

$this->data['pagetitle'] ='Election Results';

$build_array = array();
$fleets = $this->Election_result_model->select_constituency($config['per_page'], $pgoffset);

foreach($fleets as $row){
$build_array[] = array (
'fleets_array' => $row,
'listefleets_array' => $this->Election_result_model->list_constituency($row['constituency_id'],
'winner' => $this->Election_result_model->select_winner($row['constituency_id'],$row['elect_year'])
);
}
$this->data['meow'] = $build_array;

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

it shows parse error
can anyone help me
urgent...
thanks in advance.......
#10

[eluser]toopay[/eluser]
modify your model...
Code:
function select_constituency($limit=NULL, $pgoffset=NULL)
{
    $sql = "SELECT DISTINCT constituency_details.constituency_name, constituency_details.elect_year, constituency_details.constituency_id "
      ."FROM constituency_details "
      ."JOIN candidate_details "
      ."ON (candidate_details.constituency_id = constituency_details.constituency_id "
      ."AND candidate_details.elect_year = constituency_details.elect_year) "
      ."ORDER BY constituency_details.elect_year, constituency_details.constituency_name ";

    if( ! is_null($limit) AND $limit != '')
    {
        if( ! is_null($pgoffset) AND $pgoffset != '')
        {
            $sql .= "LIMIT ".$pgoffset." , ".$limit;
        }
        else
        {
            $sql .= "LIMIT 0 , ".$limit;  
        }
    }
    
    $query = $this->db->query($sql);
    $res = array(
           'data'   => $query->result_array(),
           'total'  => count($query->result_array())
           );
    return $res;
}
Then, you can get the total item and the data (in $res array) to use within your controller.




Theme © iAndrew 2016 - Forum software by © MyBB