Home / Questions / 178844
Accepted
Eduardo
Asked: 2020-07-09 20:39:02 +0800 CST 2020-07-09 20:39:02 +0800 CST

How can I avoid showing the title of a category when there is no content in it?

  • 772

I have added an ordering of news by its categories, where each new news that is in the table one_more_newsis added idto the tablecategory

Which displays all the titles of the new news with their respective category without problem.

enter image description here

 PREMIER LEAGUE
 Lorem ipsum dolor sit amet, consectetur adipiscing elit 1
 Lorem ipsum dolor sit amet, consectetur adipiscing elit 2

 CUP AMERICA
 Lorem ipsum dolor sit amet, consectetur adipiscing elit
 Lorem ipsum dolor sit amet, consectetur adipiscing elit

Those results are executed by the following query:

   $stmtcategory = $con->prepare("SELECT id_category,title_category FROM category WHERE active=?");
   $stmtcategory->bind_param("i",$active);
   $active = "1";
   $stmtcategory->execute();
   $stmtcategory->store_result();

   if ($stmtcategory->num_rows>0) {
    $stmtcategory->bind_result($id_category, $title_category);
    while ($stmtcategory->fetch()) {
      echo '<div class="date-text">
              <h4>'.$title_category.'</h4>
              <ul>';

      $stmt = $con->prepare("SELECT id_one_more_news,cover_page,title,description,detail,url,date_post FROM one_more_news WHERE id_category=? order by id_one_more_news ASC LIMIT 2");
      $stmt->bind_param("i",$id_category);
      $stmt->execute();
      $stmt->store_result();

      if ($stmt->num_rows>0) {
        $stmt->bind_result($id_one_more_news, $cover_page, $title, $description, $detail, $url, $date_post);
        while ($stmt->fetch()) {

          echo '<li>'.$title.'</li>';

        }

        echo '</ul>';
        echo '</div>';
      } else {
        //echo "<span>No existe noticias nuevas</span>";
        echo '</ul>';
        echo '</div>';
      }
    }
  } else {
    echo "No existe noticias nuevas";
  }

My little problem in the query code is that when there is no content for a category, the title of that category is displayed, resulting in the following:

 PREMIER LEAGUE
 Lorem ipsum dolor sit amet, consectetur adipiscing elit 1
 Lorem ipsum dolor sit amet, consectetur adipiscing elit 2

 CUP AMERICA

Can you explain to me, how can I make the title of the category not appear, when there is no news linked to the same category?

Additional Information

Tablecategory

id_category   title_category    active
   1          PREMIER LEAGUE      1
   2          CUP AMERICA         1

Tableone_more_news

id_one_more_news ..... title   .........  id_category   active
      1                ....                    1          1
      2                ...                     1          1
php

1 Answers

  1. Best Answer

    Eduardo promised is debt.

    Here I leave you a code proposal based on what has been said in comments. The executed query takes advantage of the concept of relational databases, obtaining data from several tables through indexes that serve to relate the data contained in them. That will save us from having to send two queries to the database. And it will avoid the original problem you have of repeated data.

    EDIT NOTE: Because you indicated that the url was also needed to build a link, the query has been modified so that it returns the data more or less like this:

            [title_category] => PREMIER LEAGUE
        [data] => News 1: Lorem ipsum dolor sit amet≠www.example.com/news1|
                  News 2: Lorem ipsum dolor sit amet≠www.example.com/news2|
                  News 5: Lorem ipsum dolor sit amet≠www.example.com/news5

    The data is grouped in a key datawith two separators. The first separator |separates each subtitle, and the separator ≠separates the subtitle text and the url. In this way we will work the results by code to show the organized content. Some ternary operators are used to verify that there really is a url and a title, if there is no url the link will not be presented of course, and if there is no title text the word will be presented in the link No data... you can change this, Of course.

    As before, the code puts each piece of information inside its respective <li>.

    I have implemented a control for when the data comes empty.

    Although I do not see that you use all the columns of SELECTthe I have put them all (so you can see more widely how it works GROUP_CONCAT). If you are not going to use them, remove them and that's it. In the end I have removed the columns that were not used for simplicity reasons, because I wanted to create a proof of concept in phpfiddle. If there were more columns, they would come out the same as title, with each value separated by |.

    Here is the updated code, with the controls also on the connection and on the preparation of the statement. If it fails tell me and if you don't understand something, ask in comments. I hope it helps. I have tested it in the new fiddle (updated link at the end) and it works perfectly for me.

    <?php
$con = new mysqli($host_name, $user_name, $pass_word, $database_name, $port);
if ($con){
    $strSQL="SELECT 
                 c.title_category,
                 GROUP_CONCAT(COALESCE(n.title,''),'≠',COALESCE(n.url,'') SEPARATOR '|') data
             FROM category_20180712 c 
                 INNER JOIN one_more_news_20180712 n ON c.id_category=n.id_category
             WHERE c.active=?
             GROUP BY c.id_category"; 
    if($stmtcategory = $con->prepare($strSQL)){
        $stmtcategory->bind_param("i",$active);
        $active = "1";
        $stmtcategory->execute();
        $stmtcategory->store_result();
        $strHTML='<div class="date-text">';
        if ($stmtcategory->num_rows>0) {
            $stmtcategory->bind_result($title_category, $data);
            $arrResult=array();
            while ($stmtcategory->fetch()) {
                $arrResult[]=array("title_category"=>$title_category, "data"=>$data);
            }
            echo "<pre>";
            print_r($arrResult);
            foreach ($arrResult as $row){
                $strHTML.="<h4>".$row["title_category"]."</h4>";
                $strHTML.="<ul>";
                $arrTitleNews=explode("|",$row["data"]);
                if(count(array_filter($arrTitleNews)) == 0){
                    $strHTML.="<li><span>No existe noticias nuevas</span></li>";
                }else{
                    foreach ($arrTitleNews as $theData){
                        $arrTitleParts=explode("≠",$theData);
                        $partOne=($arrTitleParts[0]) ? $arrTitleParts[0] : "No data";
                        $theTitle=($arrTitleParts[1]) ? '<li><a href="http://'.$arrTitleParts[1].'">'.$partOne.'</a></li>' : "";
                        $strHTML.=$theTitle;       
                    }
                }
                $strHTML.="</ul>";                  
            }       
        } else {
            $strHTML.="<ul>";  
            $strHTML.="<span>No existen noticias nuevas</span>";
            $strHTML.="</ul>";  
        }
        $strHTML.="</div>";
        $stmtcategory->close();
    }else{
        $strHTML="Ocurrió un error en la consulta";
    }
    $con->close();
}else{
    $strHTML="Error, la conexión es nula";
}  
echo $strHTML;
?>

    proof of concept

    Here you can see a test based on real data: VER DEMO EN PHPFIDDLE(click Run - F9to see the result).

    The query now brings the data like this:

    Array
(
    [0] => Array
        (
            [title_category] => PREMIER LEAGUE
            [data] => News 1: Lorem ipsum dolor sit amet≠www.example.com/news1|News 2: Lorem ipsum dolor sit amet≠www.example.com/news2|News 5: Lorem ipsum dolor sit amet≠www.example.com/news5
        )

    [1] => Array
        (
            [title_category] => CUP AMERICA
            [data] => News 3: Lorem ipsum dolor sit amet≠www.example.com/news3|News 4: Lorem ipsum dolor sit amet≠www.example.com/news4|News 6: Lorem ipsum dolor sit amet≠www.example.com/news6
        )

)

    And the reading we do inside foreachallows us to obtain a result like this:

    <div class="date-text">
  <h4>PREMIER LEAGUE</h4>
  <ul>
    <li><a href="http://www.example.com/news1">News 1: Lorem ipsum dolor sit amet</a></li>
    <li><a href="http://www.example.com/news2">News 2: Lorem ipsum dolor sit amet</a></li>
    <li><a href="http://www.example.com/news5">News 5: Lorem ipsum dolor sit amet</a></li>
  </ul>
  <h4>CUP AMERICA</h4>
  <ul>
    <li><a href="http://www.example.com/news3">News 3: Lorem ipsum dolor sit amet</a></li>
    <li><a href="http://www.example.com/news4">News 4: Lorem ipsum dolor sit amet</a></li>
    <li><a href="http://www.example.com/news6">News 6: Lorem ipsum dolor sit amet</a></li>
  </ul>
</div>

    • 5