CODEWITHSUNDEEP
phpmysqlpdoiteration
codeguerrilla
codeguerrilla

Reputation: 472

logic for listing categories and sub categories PHP

I have a table which holds what are more or less blog articles. The structure is as follows:

  1. id INT PRIMARY KEY AUTO_INCREMENT
  2. user VARCHAR
  3. title VARCHAR
  4. category VARCHAR
  5. section VARCHAR
  6. post_body TEXT
  7. post_date DATETIME

The section field is basically a sub category. I have a second table for sections:

  1. id INT PRIMARY KEY AUTO_INCREMENT
  2. section_name VARCHAR
  3. category VARCHAR

What I have been trying to do is create a list on a page that would be structured something like this:

Science(this is the category)

Geology(this the first section)

title1

title2

title3

etc

Biology(second section)

title1

title2

title3

etc

Etc etc for all sections with category of science

When a user clicks to view the articles in the science category they land on this page.

I have tried numerous things to accomplish this but have had no success. Getting the section names for display is easy but making a query for all article titles based on the sections retrieved from the first query is where I am stuck so here is my code up to that point: 

if(isset($_GET['cat'])){
    $cat = preg_replace('#[^a-z0-9_]#i','',$_GET['cat']);
    $cat = str_replace('_',' ',$_GET['cat']);
    $stmt = $db->prepare("SELECT section_name FROM sections WHERE category=:cat GROUP BY section_name");
    $stmt->bindValue(':cat',$cat,PDO::PARAM_STR);
    try{
        $stmt->execute();
    }
    catch(PDOException $e){
        echo $e->getMessage();
        $db = null;
        exit();
    }
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $sectionName = $row[section_name];
    }
}

Upvotes: 0

Views: 943

Answers (1)

Paul Hulett
Paul Hulett

Reputation: 98

I would suggest that you broaden the scope of your query to tag each title with its section, then loop over only the titles (indicating when you have a section change).

$stmt = $db->prepare("SELECT section_name FROM sections t1, title FROM posts t2  WHERE t1.category=:cat AND t1.section_name = t2.section GROUP BY section_name");

now that you have the list of titles you wish to display, loop over them:

$currentSection = null;
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  $sectionName = $row[section_name];
  if ($sectionName != $currentSection) {
    echo "<h2>".$sectionName."</h2>";
    $currentSection = $sectionName;
  }
  echo "<p>".$row['title']."</p>";
}

You could also create an array with the section name as the key and the titles as members of a nester array. It can, of course, get a lot more complicated, but I hope that gives you a good idea of where to start.

Upvotes: 1

Related Questions