CODEWITHSUNDEEP
phpmysql
Lee Price
Lee Price

Reputation: 5212

Getting related data from another table

I have 2 MySQL tables, something like this:

categories:

+---------------+--------------------+
|  id           |   category_name    |
+---------------+--------------------+
|  1            |   Category 1       |
|  2            |   Category 2       |
+---------------+--------------------+

tasks:

+---------------+---------------------------+-----------------+
|  id           |   task_name               |   category_id   |
+---------------+---------------------------+-----------------+
|  1            |   First task of the day   |       1         |
|  2            |   Then do this            |       1         |
+---------------+---------------------------+-----------------+

This is a simplified version of my database, but you get the idea.

I want to display the category as a title followed the tasks below, for example:

Category 1
   - First task of the day
   - Then do this

Category 2
   no tasks here :)

I'd be inclined to do something like this...

$query = $this->db->query("SELECT * FROM categories");

foreach($query->result() as $cat){

    echo '<h1>'. $cat->category_name . '</h1>';

    $tasks_query = $this->db->query("SELECT * FROM tasks WHERE category_id='{$cat->id}'");

    foreach($tasks_query->result() as $task){
        echo $task->task_name;
    }
}

I know this would work, but it seems like it would be quite resource intensive with all those queries. So, I was wondering if this is the right way of doing this or is there a better, more efficient way. What would be the best practice way of doing this?

Hope all this makes sense.

Upvotes: 0

Views: 64

Answers (3)

spencer7593
spencer7593

Reputation: 108500

You are quite right that there is some overhead with each query. The operations of sending the SQL text to the database server, the server parsing the statement for syntax, and then for semantics, developing an execution plan, then actually running the statement (obtaining locks, accessing blocks, releasing locks), preparing the resultset to send back to the client. And on the client side, checking the status, and retrieving the result.

In terms of just the database, it is usually more efficient to run a single statement to return a single resultset. (There are cases when it isn't, such as when the large query is inefficient, like when the resultset is really, really big, and requires an expensive sort operation.)

Example of how to get the result in one statement:

SELECT c.category_name
     , t.task_name
  FROM categories c
  LEFT
  JOIN tasks t 
    ON t.category_id = c.id
 ORDER 
    BY c.category_name
     , t.task_name

The flip side of that is there is some "extra" work that needs to be one on the client to process that.

initialize a variable to hold "previous" category_name
loop fetching rows (until no more rows)
  if category_name is not equal to previous category_name
     emit category e.g. output "<h1>category_name"
     save new category_name as previous
  end if
  if task_name is not null
     emit task e.g. output "<br>task_name"
  end if
end loop

Upvotes: 1

Lee Price
Lee Price

Reputation: 5212

Thanks all for your answers and help. I looked more into MySQL joins and this is what I come up with and it works!...

    $query = $this->db->query("
        SELECT c.category_name, t.task_name
        FROM categories c
        INNER JOIN tasks t
        ON c.id = t.category_id");

    $categories = array();

    foreach($query->result() as $result)
    {

        if ( !in_array($result->category_name, $categories) )
        {
            $categories[] = $result->category_name;
            echo '<h3>' . $result->category_name . '</h3>';
        }

        echo "<div>" . $result->task_name . "</div>";
    }

Upvotes: 0

anthony_mcdougle
anthony_mcdougle

Reputation: 306

If you use a join in your SQL query it will put more of the burden on the database rather than the PHP engine (it'll be quicker and less resource-intensive, since you're running one query rather than many within a loop).

$query = $this->db->query(
                'SELECT *
                FROM tasks t
                    INNER JOIN categories c
                        ON t.category_id = c.id');

foreach ( $query->result() as $result) {
    echo $result->category_name . ' ' . $result->task_name;
}

Upvotes: 1

Related Questions