Mysql query is not running in opencart and not fetching data form database

Question

I have run program in simpal query it is work.

<?php
 if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
if(isset($_POST['search']))
     {
            $category = $_POST['category_id'];
    $country = $_POST['country_id'];

 $sql = "SELECT DISTINCT p.name,p.product_id,pr.image
    FROM oc_product_description p, oc_product_filter pf ,oc_product_to_category ptc, oc_product pr 
WHERE p.product_id=pf.product_id AND p.product_id=ptc.product_id AND p.product_id=pr.product_id
AND ptc.category_id=$category AND pf.filter_id=$country";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    //echo "</br>name: " . $row["name"];

    $abc = $row["product_id"];


//echo "</br>id: " . $row["image"];
//echo "</br>id: " . $row["description"];
?>

<div>

    <div class="row" style="border: 1px solid;height: 210px;padding: 10px 10px;">
        <div class="product-block item-full" itemtype="http://schema.org/Product" itemscope="">
        <div class="block-img" style="width: 13%;float: left;margin-right: 10px;" >
                <?php
                $temp = $row["name"];
                $str = explode(" ",$temp);

                $pname = implode("",$str);

                ?>
                                    <div class="image" >
                    <a class="img" itemprop="url" title="<?php echo $row['name']; ?>" href="http://hrmssystem.com/<?php echo $pname; ?>">
                    <img class="img-responsive" itemprop="image" src="image/<?php echo $row['image']; ?>" title="<?php echo $row['name']; ?>" alt="<?php echo $row['name']; ?>" />
                    </a>

                </div> 

        </div>
        <div class="product-meta" style="height: 30%;width: 85%;float: left;">
            <div  style="height: 100%; width: 100%; ">
            <h3 class="name" itemprop="name">
            <a href="http://hrmssystem.com/<?php echo $pname; ?>">
            <?php echo $row['name']; 
                ?> 

            </a>
            </h3>

<?php

            $sql2 = "SELECT description from oc_product_description where product_id = $abc ";

  $res = $conn->query($sql2);
   if ($res->num_rows > 0) {
// output data of each row
while($col = $res->fetch_assoc()) {

        //echo $col["description"];

             if( isset($col['description']) ){ 

            $des = utf8_substr( strip_tags($col['description']),0,220);



  ?>

                <p style="display: block;"  class="description" itemprop="description"><?php echo $des; ?>...</p>
                <?php }

   } } ?>
            <div class="cart" style="float: right;margin-top: -30px;">

            <a style="color: #3498DB;" class="btn btn-default" href="http://www.hrmssystem.com/Get-Quote">Get Quote</a><?php  //echo $button_cart; ?>
            </br>

                <a style="color: #3498DB;" class="btn btn-default" href="http://www.hrmssystem.com/Request-demo">Request Demo</a><?php //echo $button_cart; ?>


                </div>
            </div>

        </div>

       </div>
    </div>

</div>


  <?php

}
 } else {
  echo "0 results";
}
 }
 $conn->close();
      ?>

Above Code is running. But I have select only category than no result. But I have Select category and country than i have find result of search.

 <?php
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
if(isset($_POST['search']))
     {
            $category = $_POST['category_id'];
            $country = $_POST['country_id'];

            $sql = "SELECT DISTINCT p.name,p.product_id,pr.image, pf.filter_id
            FROM oc_product_description p, oc_product_filter pf ,oc_product_to_category ptc, oc_product pr ";
            //$val = array();

            if(isset($category) && !empty($category)){

            $sql .= ' WHERE p.product_id=pf.product_id AND p.product_id=ptc.product_id AND p.product_id=pr.product_id AND ptc.category_id=$category '; 

           }else(isset($country) && !empty($country)){

           $sql .= ' WHERE p.product_id=pf.product_id AND p.product_id=ptc.product_id AND p.product_id=pr.product_id AND pf.filter_id=$country'; 

           }


 $result = $conn->query($sql);

 if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
    //echo "</br>name: " . $row["name"];

    $abc = $row["product_id"];


//echo "</br>id: " . $row["image"];
//echo "</br>id: " . $row["description"];
?>

<div>

    <div class="row" style="border: 1px solid;height: 210px;padding: 10px 10px;">
        <div class="product-block item-full" itemtype="http://schema.org/Product" itemscope="">
        <div class="block-img" style="width: 13%;float: left;margin-right: 10px;" >
                <?php
                $temp = $row["name"];
                $str = explode(" ",$temp);

                $pname = implode("",$str);

                ?>
                                    <div class="image" >
                    <a class="img" itemprop="url" title="<?php echo $row['name']; ?>" href="http://hrmssystem.com/<?php echo $pname; ?>">
                    <img class="img-responsive" itemprop="image" src="image/<?php echo $row['image']; ?>" title="<?php echo $row['name']; ?>" alt="<?php echo $row['name']; ?>" />
                    </a>

                </div> 

        </div>
        <div class="product-meta" style="height: 30%;width: 85%;float: left;">
            <div  style="height: 100%; width: 100%; ">
            <h3 class="name" itemprop="name">
            <a href="http://hrmssystem.com/<?php echo $pname; ?>">
            <?php echo $row['name']; 
                ?> 

            </a>
            </h3>

<?php

            $sql2 = "SELECT DISTINCT description from oc_product_description where product_id = $abc ";

$res = $conn->query($sql2);
if ($res->num_rows > 0) {
// output data of each row
while($col = $res->fetch_assoc()) {

        //echo $col["description"];


             if( isset($col['description']) ){ 


            $content = substr(strip_tags(htmlspecialchars_decode($col['description'])),0,220) . "...";



?>

                <p style="display: block;width: 87%;"  class="description" itemprop="description"><?php echo $content; ?></p>

                <?php }

     } } ?>
            <div class="cart" style="float: right;margin-top: -90px;">

            <a style="color: #3498DB;" class="btn btn-default" href="http://www.hrmssystem.com/Get-Quote">Get Quote</a><?php  //echo $button_cart; ?>
            </br>

                <a style="color: #3498DB;" class="btn btn-default" href="http://www.hrmssystem.com/Request-demo">Request Demo</a><?php //echo $button_cart; ?>


                </div>
            </div>

        </div>

       </div>
    </div>

</div>


   <?php

   }
   } else {
     echo "No Result Found";
   }
     }
    $conn->close();
      ?>

Answer 1

Try this:

$string = "";

if(isset($category) && !empty($category))
{
     $string .= ' AND  ptc.category_id=$category '; 
}

if(isset($country) && !empty($country))
{
    $string .= ' AND pf.filter_id=$country '; 
}
$sql = "SELECT DISTINCT p.name,p.product_id,pr.image, pf.filter_id
            FROM oc_product_description p, oc_product_filter pf ,oc_product_to_category ptc, oc_product pr 
            WHERE p.product_id=pf.product_id AND p.product_id=ptc.product_id AND p.product_id=pr.product_id
            $string
            ";

Please remove if - else condition and use only if condition and use variable to store the condition.