CODEWITHSUNDEEP
phpmysql
user652792
user652792

Reputation:

How to optimize 10 queries into one

Please i need your help with how to optimize my query. I want to fetch 10 different rows data from the same table.

Here is one of the queries

     $query1 = mysql_query("SELECT m.Course_code AS 'Course', m.score, m.grade
                   FROM maintable AS m  
                   INNER JOIN students AS s ON
                   m.matric_no = s.matric_no
                   INNER JOIN Courses AS c ON
                   m.Course_code = c.Course_code
                   WHERE m.matric_no = '".$matric_no."'
                   AND m.semester_name = 'hamattarn'
                   AND m.level = '100' ") or die (mysql_error());

                  $number_cols1 = mysql_num_fields($query1) ;

this part of the query is changing for all the queries

  AND m.semester_name = 'hamattarn'
                   AND m.level = '100' "

 AND m.semester_name = 'rain'
                   AND m.level = '100' "

 AND m.semester_name = 'hamattarn'
                   AND m.level = '200' "

 AND m.semester_name = 'rain'
                   AND m.level = '200' "

  AND m.semester_name = 'hamattarn'
                   AND m.level = '300' "

  AND m.semester_name = 'rain'
                   AND m.level = '300' "

  AND m.semester_name = 'hamattarn'
                   AND m.level = '400' "

  AND m.semester_name = 'rain'
                   AND m.level = '400' "

 AND m.semester_name = 'hamattarn'
                   AND m.level = '500' "

 AND m.semester_name = 'rain'
                   AND m.level = '500' "

Here's a picture of what i've being able to achieve with the query. After optimization i still want it to look this way.

< href="http://i.imgur.com/IGEj2.png">Sample Picture

thanks for you time and patience.

UPDATE -- here is how i was displaying the row tables from each of the 10 queries before the optimization.. Please how can i achieve this table type reult with the new optimized query?

   echo "<table class=\"altrowstable\" id = " bgcolor = gold >\n";
     echo "<tr align=center>\n";
     for ($i=0; $i<$number_cols10; $i++)
     {
               echo "<th>" . mysql_field_name($query10, $i). "</th>";
     }
     echo "</tr>\n";
               while ($row = mysql_fetch_row($query10))
    {
    echo "<tr align=center>\n";
    for ($i=0; $i<$number_cols10; $i++)
    {
    echo "<td>";
    if (!isset($row[$i]))
    {echo "NULL";}
    else
    {
    echo "<b>".$row[$i]."</b>";
    }
    echo "</td>\n";
    }
    echo "</tr>\n";
    }
    echo "</table>"; 
    echo"</span>" ;

thanks

Upvotes: 0

Views: 101

Answers (3)

symcbean
symcbean

Reputation: 48357

If you really want to optimize it then build a karnaugh map - a table with one axis enumerating every possible value of semester_name and the other access with every possible value of level, then mark the intersections you want to include in your query. From this you sould be able to work out the simplest query to suply that data.

However you since you don't indicate what values you want to exclude and you have included every possible combination of semester_name={rain, hamattarn} and level={100,200,300,400,500} then a simple solution would be:

WHERE semester_name IN ('rain', 'hamattarn')
AND level in (100,200,300,400,500)

You'll need to include semester_name and level in the where clause, and life will be simpler if you

ORDER BY semester_name, level, course

Upvotes: 0

jpic
jpic

Reputation: 33420

Why not something like this:

WHERE m.semester_name IN ('rain', 'hamattarn') 
  AND m.level IN ('100', '200', '300', '400', '500'))

As the results will be mixed, you should get the results in an expectable order, for example:

ORDER BY m.semester_name, m.level

This will allow you to split the results in PHP, something like this is common:

$previous_semester = $previous_level = false;
foreach(mysql_fetch_array($query) as $row) {
    if ($previous_semester == $row['semester_name']) {
        // row semester is different from the previous one
        echo $row['semester_name'];
    }

    if ($previous_level == $row['level']) {
        // row level is different from previous one
        echo $row['level']; 
    }

    print_r($row);

    $previous_level = $row['level'];
    $previous_semester = $row['semester_name'];
}

You could open an HTML table before the for loop. When the semester or level changes, you could close the previous table and open a new one, add a title for the rest of the rows of the same semester/level, etc, etc... You could close an HTML table after the for loop.

It's a little tricky but eventually it will get you there.

Upvotes: 3

feeela
feeela

Reputation: 29922

Just get alls possible combinations via your WHERE clause:

WHERE m.matric_no = :matric_no
AND (
       m.semester_name = 'hamattarn'
    OR m.semester_name = 'rain'
)
AND (
       m.level = '100'
    OR m.level = '200'
    OR m.level = '300'
    OR m.level = '400'
)

Upvotes: -1

Related Questions