Welcome Guest, Not a member yet? Register   Sign In
Getting and Comparing Data in Foreach best pratice
#1

(This post was last modified: 11-27-2018, 09:03 AM by soneax.)

Hi,

I'm asking for your help to understand if i'm doing something in the correct way or if exist another method to do this.

I have a foreach in my view to retrieve rows and their data from a specific table.

One of the columns table is an ID, and i want to use that ID to get values from another table.

What i did after the foreach was query all my data from the second table in a model and pass it in the controller to the view and nest another foreach to see if the ID exists in that second table and list the data.

My question is, it's possible to pass the ID from the first foreach to the query function somehow and prevent a full query to the second table?
Reply
#2

It'll be problematic, because every time you do SELECT to DB, there's overhead of connecting to DB server and all that, so the less individual queries you have to server one page request, the better.

If in your view you have one html <table> tag, you could try to join two tables together, and then all the data would be available on per row bases.

Another a bit more extensive way to do it is get all necessary data from the first table, then loop through it once, get all the necessary IDs, make single query to second table using ids from first table in SQL IN(id, id, id...) statement. Things that come back, you can store in associative PHP array in form of foreach ($q->result() as $row) { $secondTableData[$row->id] = $row; }

Now, in your HTML table you can access data from second table - $secondTableData[$row->linked_id]->label

One thing to note is there's limit on how many items you can feed into IN() statement, and also because CodeIgniter runs regex to validate query builder values, you can run into some issues if you expect thousands of rows back from second table.
Reply
#3

(This post was last modified: 11-27-2018, 10:13 AM by soneax.)

Hi, sorry, when i mention table i wanted to say MySQL tables.

Here's an example:


PHP Code:
<?php
 
foreach($things as $thing) {
    echo 
$thing['id'];
 }
?>


I want to use $thing['id'] to query another MySQL table to get child data from this ID. This will be showed in a Dashboard front page to calculate costs.
Reply
#4

@soneax,

Is it possible to have 1 query or database view pull all of your data? This way there will only be one call to the database.
Reply
#5

(11-27-2018, 09:56 PM)php_rocs Wrote: @soneax,

Is it possible to have 1 query or database view pull all of your data?  This way there will only be one call to the database.

Ok, maybe with a join, i have to test, thank you.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB