Welcome Guest, Not a member yet? Register   Sign In
Table join and display issue
#1

[eluser]debow[/eluser]
I'm trying to display similar info from 3 tables into one. It's somewhat working but is only showing the last joined table not all 3. I'm not sure what is wrong at this point. Any suggestions are welcomed.

controller.php

Code:
function getevents() {
        $data['detail'] = $this->exportexcel_model->getevents(); //call the model and save the result in $detail
        $this->load->view('excel/test', $data);
}

model.php
Code:
function getevents() {

        $this->db->select('*');
        $this->db->from('100m_times');
        $this->db->join('400m_times', '400m_times.name = 100m_times.name');
        $this->db->join('mile_times', 'mile_times.name = 100m_times.name');
        $this->db->join('swim_times', 'swim_times.name = 100m_times.name', 'left');

        $getData = $this->db->get();
        
        //$result= $getData->result_array();

        //echo $result;
         print_r($this->db->last_query());
        
        if ($getData->num_rows() > 0)
            return $getData->result_array();
        else
            return null;
    }


view.php
Code:
<h4>Score Sheet</h4>
    
    <table border="1">
&lt;?php if (count($detail) > 0) { ?&gt;
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Event</th>
        </tr>
        &lt;?php
        foreach ($detail as $rows) {
            echo "<tr>";
            echo "
                <td>" . $rows['athlete_id'] . "</td>
                <td>" . $rows['name'] . "</td>
                <td>" . $rows['event'] . "</td>
                ";
                        }
        ?&gt;
    </table>

&lt;?php } ?&gt;
<br> <br>
<a href='export_excel/toExcelAll'><span style='color:green;'>Export All Data</span></a>


Current results

Code:
SELECT * FROM (`100m_times`) JOIN `400m_times` ON `400m_times`.`name` = `100m_times`.`name` JOIN `mile_times` ON `mile_times`.`name` = `100m_times`.`name` LEFT JOIN `swim_times` ON `swim_times`.`name` = `100m_times`.`name`

This is what I get outputed.
Score Sheet
ID Name Event
238 Dan Dudley 100m Swim
239 Tom Clancey 100m Swim
237 Mike Jones 100m Swim

What I want is this
Score Sheet
ID Name Event
238 Dan Dudley 100m Swim
239 Tom Clancey 100m Swim
237 Mike Jones 100m Swim

238 Dan Dudley 100m
239 Tom Clancey 100m
237 Mike Jones 100m

238 Dan Dudley 400m
239 Tom Clancey 400m
237 Mike Jones 400m
#2

[eluser]CroNiX[/eluser]
You might need to make all of your joins left joins there.
#3

[eluser]debow[/eluser]
I was able to get what I wanted by using UNION instead of JOIN.
#4

[eluser]danmontgomery[/eluser]
You probably have identical column names in all of the tables, in which case they are all overwritten with the last table joined, since php can't have an array/object with identical keys.




Theme © iAndrew 2016 - Forum software by © MyBB