<?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'); 

$redWineCount=0;
$whiteWineCount=0;
$sql="SELECT * FROM Wines WHERE QtyInStock > 0 AND WineType != 'Glass'";
$records2 = mysqli_query($con, $sql);
while($wines=mysqli_fetch_assoc($records2)){

if($wines['WineType']=='Red'){
$redWineCount=$redWineCount+1;
}
elseif($wines['WineType']=='White'){
$whiteWineCount=$whiteWineCount+1;
}

}



$sql="SELECT DISTINCT Category FROM Wines WHERE QtyInStock > 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 Wines WHERE QtyInStock > 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 Wines WHERE QtyInStock > 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 Wines WHERE QtyInStock > 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 "<div style='text-align: center; font-size: 1.5em;'>Choose from our current selection of ".$whiteWineCount." Whites and ".$redWineCount." Reds</div><br>";
echo "<div style='text-align: center; font-size: 1.25em;'>Plus <a href='http://www.bistrowinelist.com/byprofile/?Listing=Sparkling'>Champagne/Sparkling Wines</a>, <a href='http://www.bistrowinelist.com/byprofile/?Listing=Rose'>Rosés</a> and <a href='http://www.bistrowinelist.com/byprofile/?Listing=Dessert Wines and Port'>Dessert Wines</a></div><br>";

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-size: 1.5em; text-align: right;'><b><a href='http://www.bistrowinelist.com/byprofile/?Listing=WhitePrice'>Whites</a></b></td><td></td><td style='font-size: 1.5em; text-align: left;'><b><a href='http://www.bistrowinelist.com/byprofile/?Listing=RedPrice'>Reds</a></b></td></tr>";

      for($x = 0; $x <= $listItems-1; $x++){
             
             if($x <= $redCount){
                   $redx = str_replace("'","%27",$reds[$x]);
                    
                   $redstr = "<td style='font-size: 1.5em; text-align: left;'><a href='http://www.bistrowinelist.com/byVarietal/?Listing=".$redx."'>".$reds[$x]."</a></td>";
             }
             else{
              $redstr = "<td></td>";
             }
             
             if($x <= $whiteCount){
            
                   $whitestr = "<td style='font-size: 1.5em; text-align: right;'><a href='http://www.bistrowinelist.com/byVarietal/?Listing=".$whites[$x]."'>".$whites[$x]."</a></td>";
             }
             else{
              $whitestr = "<td></td>";
             }

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

      }

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