[El Forum]

[eluser]Unknown[/eluser]
Hi @all,

I am just migrating a project of mine into codeigniter. On one page the user is getting a list of over 300 tours and details of the tours.
So in my basic project I did the mysql-query and just ran a foreach loop and then in the foreach loop another mysql request to get all the details of each tour from a second details table.

Here is my codeigniter approach:
I have a model where all tours are loaded with one query. Foreach result in this query I do a
foreach($sql->result_array() as $tour) {
$tourarray[$tour['tourid']] = $tour;
and than again a subquery for the details which ends in a multidimensional array called $tourarray with a key per tour and then another subarray with all the details.
I return the $tourarray to the controller which loads the view.
In the view I have to go through the whole array again which ends up in ~6 seconds of loading time where my basic setup which did the same needed less than two.

Is there any shorter way to get a mysql result from a model to a view in codeigniter or is my approach totally wrong?

Thank you very much for your help.
Max

[El Forum]

[eluser]jonez[/eluser]
Instead of doing one query to pull the ID's then 300 subqueries for tour details do it all as a single query join and delegate the array. It should not take 6s to compile a view from data, can you post some code on how you're building the view?

[El Forum]

[eluser]Tpojka[/eluser]
Have you tried to join tables in one query in model?
What do your query codes look like? can you paste sample code examples of your query/subquery?

ed.
jonez was faster

[El Forum]

[eluser]ivantcholakov[/eluser]

Code:

foreach($sql->result_array() as $tour) {

This loads all the records within the memory, it is OK for smaller recordsets.

In CI3-dev there is a method unbuffered_row(), see http://ci3docs.cibonfire.com/database/results.html

I think, it cycles the returned recordset directly.

Code:

$query = $this->db->query("YOUR QUERY");

while ($row = $query->unbuffered_row())
{
        echo $row->title;
        echo $row->name;
        echo $row->body;
}

// Variations, see the documentation:
// $query->unbuffered_row();               // object
// $query->unbuffered_row('object');       // object
// $query->unbuffered_row('array');        // associative array

[El Forum]

[eluser]CroNiX[/eluser]
Also, do you have any fields indexed in the db besides primary key? Unless you construct the DB with proper indexes, queries get longer and longer with more data as it has to scan every single row to find results. I have real estate sites with literally millions of rows that only take about .5 seconds to run, and that includes joins on like 10 other tables.

[El Forum]

[eluser]Unknown[/eluser]
Hi. Thanks for the replies.

The problem is, that each tour has like up 30 details segment. So a join would produce a result with the whole tourdata for 30 times, which isn't needed and would have to be sorted out afterwards.I don't think the query itself is the problem. I think it is the transfer between model->controller->view where the whole thing is looped several times.

Yes there are several indexes. As i said: The same database is beeing used by the "old version" which is performing very well.

The view is loaded pretty basic. The tourarray is returned to the controller where the view is loaded
$this->load->template('umlauf',$data);
the load->template is only including header and footer so I don't have to do it on each controller.

I will check out the unbuffered->row

Thanks so far.

[El Forum]

[eluser]jonez[/eluser]
Correct, the join would result in one massive recordset (array). Pulling one query then delegating by ID in PHP is considerably faster than performing 300 individual queries. Try it and see for yourself.

Something like this should work well;

Code:

$results = $this->db->query( $sql, $params )->result_array( );
$data = array( );

foreach ( $results as $result ) {
if ( !array_key_exists( $result[ 'id' ], $data ) ) {
  $data[ $result[ 'id' ] ] = array(
   'id' => $result[ 'id' ],
   'rows' => array( ),
  );
}

$data[ $result[ 'id' ] ][ 'rows' ][ ] = $data;
}

unset( $result, $results );