title: MySQL, PHP Find the greatest value/row by comparing multiple tables
body: <p>I have multiple tables in my database and those are updated daily</p>
  <p>I'm doing a web app, which compares data from multiple tables.</p>
  <p>Data is updated daily, so I really don't know the greatest values and I can't hard code those to a database.</p>
  <p>Instead, I should compare data with the corresponding key.</p>
  <p>To find the head for my table, I already do a comparison to find different Engine - Transmission -combinations and print one of each:</p>
  <pre><code>## GET cars with different motors by type
  function findEngines($link, $type, $queryNumber, $data)
  {
    $query = &quot;
  SELECT Engine
       , Transmission
       , ID 
    FROM `model_&quot; . $type . &quot;` 
   GROUP 
      BY Engine
       , Transmission 
   LIMIT &quot; . $queryNumber . &quot;, 1&quot;;
    if ($result = mysqli_query($conn, $query)) {
      while ($row = mysqli_fetch_array($result)) {
        //currently, carData is all, which is needed
        if ($data == &quot;engine&quot;) {
          return $row[&quot;Engine&quot;] . &quot; &quot; . $row[&quot;Transmission&quot;];
        } else if ($data == &quot;carData&quot;) {
          return $row[&quot;ID&quot;];
        }
      }
    }
  }
  </code></pre>
  <p>Then, I echo categories for those Engine - Transmission -Combinations:</p>
  <pre><code>##Categories
  function printCategories($conn, $carID)
  {
    $query = &quot;
  SELECT TechSpecCategoryName
       , Spec_Name
       , SorterI 
    FROM `categories_&quot; . $carID . &quot;`
   ORDER 
      BY FIELD(`categories_&quot; . $carID . &quot;`.TechSpecCategoryName, 'header1', 'header2', 'header3', 'header4', 'header5', 'header6', 'header7'), `categories_&quot; . $carID . &quot;`.SorterI&quot;;
  
    if ($result = mysqli_query($conn, $query)) {
  
      $techSpecCategory = &quot;&quot;;
      while ($row = mysqli_fetch_array($result)) {
        if ($row['Spec_Name'] == &quot;&quot;) {
          $spec_name = &quot;-&quot;;
        } else {
          if ($row[&quot;TechSpecCategoryName&quot;] != $techSpecCategory) {
            echo &quot;&lt;td class='ad-td w-40'&gt;&lt;h5&gt;&quot; . $row[&quot;TechSpecCategoryName&quot;] . &quot;&lt;/h5&gt;&lt;/td&gt;&quot;;
            $techSpecCategory = $row[&quot;TechSpecCategoryName&quot;];
   
          }
          $spec_name = $row[&quot;Spec_Name&quot;];
          $spec_name = preg_replace(&quot;/(\/generaattori)/&quot;, &quot; &quot;, $spec_name);
        }
        echo &quot;&lt;td class='ad-td w-40'&gt;&quot; . $spec_name . &quot;&lt;/td&gt;&quot;;
  
      }
    } else {
      echo mysqli_errno($conn) . &quot;: &quot; . mysqli_error($conn) . &quot;&lt;br&gt;&quot;;
    }
  }
  </code></pre>
  <p>Also, I print the data for every column in another function:</p>
  <h2>Print data</h2>
  <pre><code>function printData($conn, $itemID, $type, $countResults)
  {
    if($countResults == 1){
      $tableColWidth = &quot;w-60&quot;; 
    }
    elseif($countResults == 2){
      $tableColWidth = &quot;w-30&quot;;
    }
    elseif ($countResults == 3) {
      $tableColWidth = &quot;w-20&quot;;  
    }
    else {
      $tableColWidth = &quot;w-15&quot;;  
    }
  
    $table = str_replace(&quot;-&quot;, &quot;_&quot;, $itemID);
  
    $queryCarData = &quot;SELECT `categories_&quot; . $type . &quot;`.Item_Name, `categories_&quot; . $type . &quot;`.SorterI, `&quot; . $table . &quot;`.*, COALESCE(SUBSTRING(`&quot; . $table . &quot;`.Item_value, 1, 15), '-') FROM `categories_&quot; . $type . &quot;`
    LEFT JOIN  `&quot; . $table . &quot;`
    ON `&quot; . $table . &quot;`.Item_Name = `categories_&quot; . $type . &quot;`.Item_Name
    ORDER BY FIELD(`categories_&quot; . $type . &quot;`.TechSpecCategoryName, 'Kulutus ja päästöt', 'Moottori', 'Vaihteisto', 'Suorituskyky', 'Jarrut', 'Jousitus', 'Ohjaus', 'Mitat ja massat', 'Kuormauskapasiteetti', 'Offroad', 'Renkaat ja vanteet'), `categories_&quot; . $type . &quot;`.SorterI&quot;;
  
    if ($result = mysqli_query($conn, $queryCarData)) {
      $rowDataNumber = 0;
      $techspecCategory = &quot;&quot;;
      while ($row = mysqli_fetch_array($result)) {
  
        if ($row['Item_Name'] == &quot;&quot;) {
          $Item_value = &quot;-&quot;;
        } else {
  
          if ($row[&quot;TechSpecCategoryName&quot;] != $techspecCategory) {
            echo &quot;&lt;td class='ad-td &quot;.$tableColWidth.&quot;'&gt;&quot; . &quot;&lt;br&gt;&quot; . &quot;&lt;/td&gt;&quot;;
            $Item_value = &quot; &quot;;
            $techspecCategory = $row[&quot;TechSpecCategoryName&quot;];
            $rowDataNumber++;
          }
          $Item_value = $row[&quot;Item_value&quot;];
        }
        echo &quot;&lt;td class='ad-td &quot;.$tableColWidth.&quot;'&gt;&quot; . $Item_value . &quot;&lt;/td&gt;&quot;;
        $rowDataNumber++;
      }
    } else {
      echo mysqli_errno($conn) . &quot;: &quot; . mysqli_error($conn) . &quot;&lt;br&gt;&quot;;
    }
  }
  </code></pre>
  <p>What I'd like to archieve:
  while i'm echoing values, I'd like to echo them with greatest corresponding value, like this:</p>
  <pre><code>echo &quot;&lt;td class='ad-td &quot;.$tableColWidth.&quot;'&gt;&quot; . $Item_value .&quot; / &quot;. $greatest_value .&quot;&lt;/td&gt;&quot;;
  </code></pre>
  <p>I'm aware of <code>MySQL's GREATEST()</code> function, which looks like right for my problem, but I couldn't figure out, how to use it in a situation as mine is.</p>