<?php
   $con = mysqli_connect('localhost', 'mayabeac_wine', '3]eGo-r(Scru',"mayabeac_WineList");
        if (mysqli_connect_errno()) {
            echo"
                Failed to connect to the MySQL DB:".mysqli_connect_error();
            }
           else{


}
 
mysqli_query($con, "SET NAMES 'utf8'");
// mysqli_select_db('WineList'); 
$sql="SELECT DISTINCT Category FROM Wines2 WHERE QtyInStock > 0 AND OutOfStock = 0 AND Archived=0 AND LeaveOffMenu=0 AND WineType = 'Red' ORDER BY Category";
 
$records = mysqli_query($con, $sql); 

$linecount = 1;
$reds = array();
$whites = array();
$redCount=0;
$whiteCount=0;

while($wine=mysqli_fetch_assoc($records)){
     if(!empty($wine['Category'])){
          $redCount = $redCount + 1;
          $reds[$redCount] = $wine['Category'];
        
    }
}

$sql="SELECT DISTINCT Category2 FROM Wines2 WHERE QtyInStock > 0 AND OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu=0 AND WineType = 'Red' ORDER BY Category2";
 
$records = mysqli_query($con, $sql); 
while($wine=mysqli_fetch_assoc($records)){
     if(!empty($wine['Category2'])){
          $wineFound = 0;
           for ($x = 1; $x <= $redCount; $x++) {
                 if($reds[$x] == $wine['Category2']){
                  
                        $wineFound = 1;
                  }
           }
           if($wineFound == 0){
              $redCount = $redCount + 1;
              $reds[$redCount] = $wine['Category2'];
          }
    }
}

sort($reds);






$sql="SELECT DISTINCT Category FROM Wines2 WHERE QtyInStock > 0 AND OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu=0  AND WineType = 'White' ORDER BY Category";
 
$records = mysqli_query($con, $sql); 

while($wine=mysqli_fetch_assoc($records)){
    if(!empty($wine['Category'])){
          $whiteCount = $whiteCount + 1;
          $whites[$whiteCount] = $wine['Category'];
         //  echo $whites[$whiteCount];
      }
}

$sql="SELECT DISTINCT Category2 FROM Wines2 WHERE QtyInStock > 0 AND OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu=0  AND WineType = 'White' ORDER BY Category2";
 
$records = mysqli_query($con, $sql); 
while($wine=mysqli_fetch_assoc($records)){
     if(!empty($wine['Category2'])){
          $wineFound = 0;
           for ($x = 1; $x <= $whiteCount; $x++) {
                 if($whites[$x] == $wine['Category2']){
                  
                        $wineFound = 1;
                  }
           }
           if($wineFound == 0){
              $whiteCount = $whiteCount + 1;
              $whites[$whiteCount] = $wine['Category2'];
          }
         //  echo $whites[$whiteCount];
    }
}

sort($whites);




if($whiteCount > $redCount){
   $listItems = $whiteCount;
}
else{
   $listItems = $redCount;
}

echo "<body><table style='width: 97%; margin-left: 2%; margin-right: 2%;'>";
       
   //  echo "<colgroup>";
   //  echo "<col span='1' style='width: 20em;'>";
   // echo "<col span='1' style='width: 1em;'>";
    // echo "<col span='1' style='width: 20em;'>";
    // echo "</colgroup>";

echo "<tr><td style='font-family: Open Sans; font-weight: 600; font-size: 1.5em; text-align: left;'><a href='http:www.bistrowinelist.com/byprofile/?Listing=WhitePrice'>Whites</a></td></tr>";

      for($x = 0; $x <= $whiteCount-1; $x++){
                  
           $whitestr = "<td style='font-size: 1.5em; text-align: left;'><a href='http://www.bistrowinelist.com/byVarietal/?Listing=".$whites[$x]."'>".$whites[$x]."</a></td>";
           


             echo "<tr>";
             echo $whitestr;
             echo "</tr>";
             
             

      }
echo "<tr></tr>";
echo "<tr><td style='font-family: Open Sans; font-weight: 600; font-size: 1.5em; text-align: left;'><a href='http:www.bistrowinelist.com/byprofile/?Listing=RedPrice'>Reds</a></td></tr>";
      for($x = 0; $x <= $redCount-1; $x++){
       $redstr = "<td style='font-size: 1.5em; text-align: left;'><a href='http://www.bistrowinelist.com/byVarietal/?Listing=".$reds[$x]."'>".$reds[$x]."</a></td>";
echo "<tr>";

             echo $redstr;
             echo "</tr>";
}


echo "</table>";
echo "</body>";