title: Most efficient way to re-order my database table and display to web
body: <p>I'm new to MySQL, Just read the section of my book explaining it and now at the point where I'm applying it to the website I'm building.</p>
  
  <p>I have the two following tables in my database:</p>
  
  <pre><code>Table Name: Transactions
  +------------------+------------+-----------+--------+
  | description      | date       | category  | amount |
  +------------------+------------+-----------+--------+
  | Internet         | 2013-01-04 | Bills     | $50.00 |
  +------------------+------------+-----------+--------+
  | Giant Eagle Trip | 2013-10-04 | Groceries | $30.00 |
  +------------------+------------+-----------+--------+
  | Car Insurance    | 2013-01-04 | Bills     | $90.00 |
  +------------------+------------+-----------+--------+
  | Starbucks        | 2013-09-04 | Coffee    |  $5.00 |
  +------------------+------------+-----------+--------+
  | Giant Eagle Trip | 2013-11-04 | Groceries | $15.00 |
  +------------------+------------+-----------+--------+
  
  Table Name: Categories
  +------+-----------+
  | rank | Name      |
  +------+-----------+
  |    1 | Bills     |
  +------+-----------+
  |    2 | Groceries |
  +------+-----------+
  |    3 | Coffee    |
  +------+-----------+
  </code></pre>
  
  <p>On My website I want to take that transaction table and order it by category column. The trick is I don't want it ordered alphabetically, I want it ordered in the same order as the Categories table.  The final result I have displayed below:</p>
  
  <pre><code>+------------------+------------+-----------+--------+
  | description      | date       | category  | amount |
  +------------------+------------+-----------+--------+
  | Internet         | 2013-01-04 | Bills     | $50.00 |
  +------------------+------------+-----------+--------+
  | Car Insurance    | 2013-01-04 | Bills     | $90.00 |
  +------------------+------------+-----------+--------+
  | Giant Eagle Trip | 2013-10-04 | Groceries | $30.00 |
  +------------------+------------+-----------+--------+
  | Giant Eagle Trip | 2013-11-04 | Groceries | $15.00 |
  +------------------+------------+-----------+--------+
  | Starbucks        | 2013-09-04 | Coffee    |  $5.00 |
  +------------------+------------+-----------+--------+
  </code></pre>
  
  <p>I have thought of two ways to do this and they are as follows:</p>
  
  <pre><code>//Option A: using arrays
     //Get categories in correct order
     $query  = "select name 
                from Categories
                order by rank";
     $result = $db-&gt;query($query);
     $numOfCategories = $result-&gt;num_rows;
     for($i=0; $i&lt;$numOfCategories; $i++) {
        $row = $result-&gt;fetch_assoc();
        $category[$i] = $row['name'];
     }
  
     //Get transactions and put in correct order
     $query  = "select *
                from Transactions";
     $result = $db-&gt;query($query);
     $numOfTransactions = $result-&gt;num_rows;
  
     for($i=0; $i&lt;$numOfTransactions; $i++) {
        $row = $result-&gt;fetch_assoc();
        for($j=0; $j&lt;$numOfCategories; $j++) {
           if ($category[$j] == $row['category']) {
              $tableRows[$j][] = $row;
           }
        }
     }
  
     //Display Table
     echo "&lt;table&gt;\n";
     for($i=0; $i&lt;count($tableRows); $i++) {
        for($j=0; $j&lt;count($tableRows[$i]); $j++) {
           echo "   &lt;tr&gt;\n";
           echo "      &lt;td&gt;".$tableRows[$i][$j]['description']."&lt;/td&gt;\n";
           echo "      &lt;td&gt;".$tableRows[$i][$j]['date']."&lt;/td&gt;\n";
           echo "      &lt;td&gt;".$tableRows[$i][$j]['category']."&lt;/td&gt;\n";
           echo "      &lt;td&gt;".$tableRows[$i][$j]['amount']."&lt;/td&gt;\n";
           echo "   &lt;/tr&gt;\n";
        }
     }
     echo "&lt;/table&gt;&lt;br&gt;&lt;br&gt;\n";
  
  //Option B: using where in select query
     //Get categories in correct order
     $query  = "select name 
                from Categories
                order by rank";
     $result = $db-&gt;query($query);
     $numOfCategories = $result-&gt;num_rows;
     for($i=0; $i&lt;$numOfCategories; $i++) {
        $row = $result-&gt;fetch_assoc();
        $category[$i] = $row['name'];
     }
  
     //Get transactions and display table
     echo "&lt;table&gt;\n";
     for($i=0; $i&lt;$numOfCategories; $i++) {
        $query  = "select * 
                   from Transactions
                   where category = '".$category[$i]."'";
        $result = $db-&gt;query($query);
        for($j=0; $j&lt;$result-&gt;num_rows; $j++) {
           $row = $result-&gt;fetch_assoc();
           echo "   &lt;tr&gt;\n";
           echo "      &lt;td&gt;".$row['description']."&lt;/td&gt;\n";
           echo "      &lt;td&gt;".$row['date']."&lt;/td&gt;\n";
           echo "      &lt;td&gt;".$row['category']."&lt;/td&gt;\n";
           echo "      &lt;td&gt;".$row['amount']."&lt;/td&gt;\n";
           echo "   &lt;/tr&gt;\n";
        }
     }
     echo "&lt;/table&gt;&lt;br&gt;&lt;br&gt;\n";
  </code></pre>
  
  <p>Which way is better and why, or is there another way that is even better than the ways (maybe using index feature in MySQL that my book didn't explain in much detail and/or using prepared statements that my book also didn't explain in much detail). </p>