按产品类型筛选结果并按价格排序（PHP / SQL）

Question

I'm in the process of learning how to create an e-commerce site. I'm trying to figure out how to order product results by price. Code below is a menu list to either filter a specific product category or the option to view all products--the code for that works.

I'm not sure how to add code to "sort by price" (low to high, high to low). I've tried creating a function called priceList and calling inside the function categoryList (which queries the database for which product type or to view all products), but does not work.

    function priceList() {
      $con = getConnection();

     if($pUserCat == "lowToHigh") {
        $sqlQuery = "SELECT Price from Products
         ORDER BY Price ASC";
     } elseif($pUserCat == "highToLow") {
        $sqlQuery = "SELECT Price from Products
         ORDER BY Price DESC";

     // Execute Query -----------------------------           
            $result = mysqli_query($con, $sqlQuery);
      if(!$result) {
       echo "Cannot do query" . "<br/>";
       exit;
      }

      $row = mysqli_fetch_row($result);
      $count = $row[0];

      if ($count > 0) {
       echo "Query works" . "<br/>";
      } else {
       echo "Query doesn't work" ."<br/>";
      }

              // Display Results -----------------------------

      $num_results = mysqli_num_rows($result);

      for ($i=0; $i<$num_results; $i++) {
       $row = mysqli_fetch_assoc ($result);
         // print_r($row);
        echo '<img src="data:image/jpeg;base64,'.base64_encode($row['Image']).'" />';
        echo "Price: " . stripslashes($row['Price']);
      }

      // Close connection
      closeConnection($con);
}

Form

    <!--category and price form ------------------------- -->
    <form action="register_script.php" name="frm" method="post">
        <select name="category" id="category">
            <option value="viewall">View All</option>
            <option value="dress">Dress</option>
            <option value="athletic">Athletic</option>
            <option value="sandals">Sandals</option>
       </select>

      <input type="submit" value="Go" />

  </form>


   <form action="register_script.php" name="frm" method="post">

        <select name="price" id="price">
            <option value="lowToHigh">Low to High</option>
            <option value="highToLow">High to Low</option>
       </select>

       <input type="submit" name="orderPrice" value="orderPrice" />

    </form>
    </div>

PHP

<?php
    function categoryList($pUserCat=false) {
    echo "I am in category list" . "<br/>";

    $con = getConnection(); 

     $sqlQuery = "SELECT * from Products";

    if($pUserCat == "athletic") {
       $sqlQuery = "SELECT * from Products
                    WHERE ProductType='athletic'";
    } elseif ($pUserCat == "dress") {
        $sqlQuery = "SELECT * from Products
                    WHERE ProductType='dress'";
    } elseif ($pUserCat == "sandals") { 
            $sqlQuery = "SELECT * from Products
                    WHERE ProductType='sandals'";
    } elseif ($pUserCat == "viewall") {                 
       $sqlQuery = "SELECT * from Products";
    }

    // Execute Query -----------------------------           
        $result = mysqli_query($con, $sqlQuery);
        if(!$result) {
            echo "Cannot do query" . "<br/>";
            exit;
        }

        $row = mysqli_fetch_row($result);
        $count = $row[0];

        if ($count > 0) {
            echo "Query works" . "<br/>";
        } else {
            echo "Query doesn't work" ."<br/>";
        }

          // Display Results -----------------------------

        $num_results = mysqli_num_rows($result);

        for ($i=0; $i<$num_results; $i++) {
            $row = mysqli_fetch_assoc ($result);
           // print_r($row);
          echo '<img src="data:image/jpeg;base64,'.base64_encode($row['Image']).'" />';
          echo "Price: " . stripslashes($row['Price']);
        }


         // priceList();
        $priceOrder = getPriceBtn(); //$priceOrder holds value of option selected in         
     //price order menu
    priceList($priceOrder);
        // Close connection
        closeConnection($con);

}


function getPriceBtn() {    
    //echo "<br/>"."I am calling getPriceBtn function" . "<br/>";
    $priceBtn = $_POST["orderPrice"];           // price button
    return $price;
    //echo $priceBtn;
}

?>

Answer 1

I think you need to pass the $pUserCat to the function priceList($pUserCat);

A tip, you should have a default query like from high to low and if the other option is selected then run that query:

$sqlQuery = "SELECT Price FROM Products ORDER BY Price DESC";

if($pUserCat == "lowToHigh") {
   $sqlQuery = "SELECT Price FROM Products ORDER BY Price ASC";
}

Also you could re-write this:

$sqlQuery = "SELECT * from Products"; // is this needed?

if($pUserCat == "athletic") {
   $sqlQuery = "SELECT * from Products
                WHERE ProductType='athletic'";
} elseif ($pUserCat == "dress") {
    $sqlQuery = "SELECT * from Products
                WHERE ProductType='dress'";
} elseif ($pUserCat == "sandals") { 
        $sqlQuery = "SELECT * from Products
                WHERE ProductType='sandals'";
} elseif ($pUserCat == "viewall") {                 
   $sqlQuery = "SELECT * from Products";
}

like this categoryList($pUserCat):

if($pUserCat != 'viewall') {
   $sqlQuery = "SELECT * FROM Products WHERE ProductType='%s'";
   $sqlQuery = sprintf($sqlQuery, $pUserCat);
} else {
   $sqlQuery = "SELECT * from Products";
}