How to optimize nested query with PHP and MySqli?

Question

I have this code, that fetches data from two tables in MySQLi. It first has to get the title, description, status and project_id from table 1, and then get the name from table 2 using the id from table 1.

Is there a better/faster way to do this? I have about 600 rows in the tables, and it takes about 5 sec to run this query. I will also add that this example is a bit simplified, so please don't comment on the db-structure.

<?php
$results = $connect()->db_connection->query(
    'SELECT title, description, status, project_id 
     FROM table 
     WHERE created_by ='.$user_id
);

if ($results) {
    while ($result = $results->fetch_object()) {
        $res = $connect()->db_connection->query(
            "SELECT name FROM projects WHERE id = ".$result->project_id
        );
        if ($res) {
             while ($r = $res->fetch_object()) { 
                echo $r->name;      
            }   
        }
     echo $result->title; 
     echo $result->status; 
    }
}     
?>

You need to use a JOIN: dev.mysql.com/doc/refman/5.6/en/join.html — jeroen
– jeroen, Commented Apr 8, 2015 at 12:34

harry · Accepted Answer · 2015-04-08 12:40:30Z

1

Use Query:
    SELECT title,description,status,project_id 
    FROM table tb
    inner join projects pr on pr.id = tb.project_id
    WHERE created_by = $user_id

answered Apr 8, 2015 at 12:40

harry

1,0072 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michal_Szulc · Accepted Answer · 2015-04-08 12:41:04Z

1

Try to use JOIN in your query.

You can find examples and description of this command here: http://www.w3schools.com/sql/sql_join.asp

Check out also this infographics: http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

answered Apr 8, 2015 at 12:41

Michal_Szulc

4,2076 gold badges35 silver badges63 bronze badges

Comments

n-dru · Accepted Answer · 2015-04-08 12:49:14Z

0

You can use JOIN on project_id:

$results = $connect()->db_connection->query('SELECT t.title title,t.description,t.status status,t.project_id, p.name name FROM `table` t JOIN projects p ON p.id= t.project_id WHERE t.created_by ='.$user_id);
if($results){
   while($result = $results->fetch_object()){
        echo $result->name;
        echo $result->title; 
        echo $result->status; 

    }
}

tables have aliases here - t for table and p for projects.

Also to make it faster, add index to project_id in table table, if you haven't done it yet:

$connect()->db_connection->query('ALTER TABLE `table` ADD INDEX `product_id`');

edited Apr 8, 2015 at 12:49

answered Apr 8, 2015 at 12:38

n-dru

9,4402 gold badges31 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to optimize nested query with PHP and MySqli?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related