<?php

$tableName = "Wines2";
$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 ".$tableName." WHERE OutOfStock = 0 AND QtyInStock > 0 AND LeaveOffMenu = 0 AND Archived = 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;
}

}

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



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



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

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

sort($reds);






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

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

for ($i = 2; $i <= 6; $i++){
    $category = "Category".$i;
    $sql="SELECT DISTINCT ".$category." FROM ".$tableName." WHERE OutOfStock = 0 AND QtyInStock > 0 AND LeaveOffMenu = 0 AND Archived = 0 AND WineType = 'White' ORDER BY ".$category;
    
    $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++;
                $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='/byprofile/?Listing=Sparkling'>Champagne/Sparkling Wines</a>, <a href='/byprofile/?Listing=Rose'>Rosés</a> and <a href='/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='/byprofile/?Listing=WhitePrice'>Whites</a></b></td><td></td><td style='font-size: 1.5em; text-align: left;'><b><a href='/byprofile/?Listing=RedPrice'>Reds</a></b></td></tr>";

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

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

      }

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