How can I filter posts by category on a PHP blog (non-wordpress)?


#1

I am creating a project where I have created a system that allows a user to create and publish blog posts. I want to be able to have a drop down function that will filter and display posts by a specific category they are tagged with. How would I go about doing that?

This is not a WordPress site, and any research I've been doing on my own has mainly turned up results for WordPress sites.

If anyone could help me or at least point me in a good direction, that would be great. I'm fairly new to working with PHP so I will take any help I can get.

Below is the code I have so far.

Here is the PHP I have at the top of the file:

<?php
include('includes/db_connect.php');

   $queryString = "SELECT post_id, title, price, image, LEFT(description, 300) AS description, category FROM post INNER JOIN categories ON categories.category_id=post.category_id WHERE category = '" . $_GET["category"] . "' ORDER BY post_id DESC LIMIT $start, $per_page"; 
    $_GET["category"] . "' ORDER BY post_id DESC LIMIT $start, $per_page"; 
    $query = $db->prepare($queryString);
    $query->execute();
    $query->bind_result($post_id, $title, $price, $image, $description, $category);

?>

I am also unable to make the dropdown work correctly. I am trying to get it to grab the categories from the database since I have it so the poster can create categories for the posts. I created a drop down similar to this on the post creation page, but I am now trying to get it to work for this filtering dropdown. Right now my biggest problem is not being able to get the to show categories or even show anything else on the page that goes below it. If I remove it, then my posts all show. I tried ending the while loop for it, but I cannot find a way for it to actually let me do that.

Here is the part of the index.php where the posts are being displayed:

<div class="container">
        <div class="row">
        <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12" id="centertext">
            <h1>Posts</h1>
            <!-- THIS IS THE DROPDOWN /-->
   <article>
      <form>
          <select name="category" class="form-control">
            <?php
              $query = $db->query("SELECT * FROM categories");
              while($row = $query->fetch_object()){
                echo "<option value='index.php?category=".$row->category_id."'>".$row->category."
                </option>";
                exit();

              }
            ?>
          </select>
      <button type="submit" name="submit" value="Submit" class="btn btn-default">SEARCH</button>
      </form>
      </article>

            <?php
			while ($query->fetch()):
        $lastspace = strrpos($description, '');
		?>
        <!-- THIS IS THE STRUCTURE FOR EACH POST /-->
		<article>
			<div class="preview">
				<div class="ptop">
					<?php echo "<img src='admin/images/".$image."' width='100%' height='100%' >";?>
          <div class="basicinfo">
            <h2><?php echo $title?></h2>
            <?php echo $category?>
          </div>
				</div>
				<div class="pbottom">
          <p><?php echo substr($description, $lastspace).'...<br><br><a href="post.php?id='.$post_id.'">VIEW POST</a>'?></p>
        </div>
			</div>
		</article>
	<?php endwhile?> 

        </div>          
        </div>
    </div>

The following are the post and categories tables from my database:

POST

CREATE TABLE `post` (
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `category_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `posted` datetime NOT NULL,
  `photo_id` int(11) DEFAULT NULL,
  `image` varchar(300) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CATEGORIES

CREATE TABLE `categories` (
  `category_id` int(11) NOT NULL,
  `category` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#2

First off, I think this line

echo "<option value='index.php?category=".$row->category_id."'>".$row->category."

should be more like

echo "<option value='".$row->category_id."'>".$row->category."

You don't need to give it a destination because it's part of a form. What you should do is add a method and an action parameter to your form declaration and point it to the correct code.

At the top of your code, you should check if the category drop-down has been selected, and if it has, run the query including that selection as part of your WHERE clause. If it has not, then just run a plain query. So for that you might want to add a first option with a specific value that cannot appear in the categories table, and use that to indicate whether a category was chosen. You've got a syntax error in the code you posted where you seem to repeat part of the query on the next line, that might just be down to posting it here.

The reason the dropdown code stops anything else is this line

exit();

after the line where you echo the category information, which immediately terminates the execution of the script. So remove that line.

You're doing that on the very next line after the exit(), with the close-brace. You can either do while() { ... } as you are doing in the category display code, or you can do while() : ... endwhile as you are doing to display the posts. I'm not sure I'd mix both in the same piece of code. I prefer brackets, but I think it's a personal choice.

Another issue you might face, and might not, is down to the way you name your queries. I can't tell where the code for the query that retrieves posts runs - if it runs at the top of the code, then you'll have to choose different variable names when you retrieve the categories list. At the moment you're using $query for both of them, which is fine as long as you remember the sequence and don't mix things up. Personally I'd use different names. But as it stands, if you retrieve the posts into $query at the top of the script, then run another query using the same variable name for the result set before you do anything with the posts, by the time you come to display posts, there will be nothing there as by then, $query will refer to the results from the second query, which is empty as your loop has already display them.


#3

Thank you so much for your help! I was able to get my drop down to work correctly so it didn't interfere with anything else.

Now that I have both the drop down function and the post display function working, I have one other question. I was wondering if you had any suggestions for me on how to get the posts tagged with the specified category to show once the option is selected and submitted from the drop down?

I've been trying to figure out how to tie the two functions together, and anything useful I'm finding for it is strictly WordPress based.

Here is the working code I have in the HTML body:

 <!-- THIS IS THE DROPDOWN SEARCH /-->

 <form action="<?php echo $_SERVER['PHP_SELF']?>" method="GET" enctype="multipart/form-data">
              <select name="category" class="form-control">
                <?php
                $getcats = $db->query("SELECT * FROM categories");
                  while($row = $getcats->fetch_object()):
                    echo "<option value='".$row->category_id."'>".$row->category."</option>";
                ?> 
                <?php endwhile?>
              </select>
              <button type="submit" value="Submit" class="btn btn-default">SEARCH BY CATEGORY</button>
          </form>

     <!-- THIS IS WHERE THE POSTS ARE DISPLAYED /-->

    <?php
      $queryString = "SELECT post_id, title, price, image, LEFT(description, 300) AS description, category FROM post INNER JOIN categories ON categories.category_id=post.category_id ORDER BY post_id DESC LIMIT $start, $per_page"; 
      $query = $db->prepare($queryString);
           $query->execute();
                       $query->bind_result($post_id, $title, $price, $image, $description, $category);

			while ($query->fetch()):
        $lastspace = strrpos($description, '');
		?>
		<article>
			<div class="preview">
				<div class="ptop">
					<?php echo "<img src='admin/images/".$image."' width='100%' height='100%' >";?>
          <div class="basicinfo">
            <h2><?php echo $title?></h2>
            $<?php echo $price?>
            <?php echo $category?>
          </div>
				</div>
				<div class="pbottom">
          <p><?php echo substr($description, $lastspace).'...<br><br><a href="post.php?id='.$post_id.'">VIEW PRODUCT</a>'?></p>
        </div>
			</div>
		</article>
	<?php endwhile?> `

Would I need to work with if(isset($_GET['submit'])) or something else?


#4

That's what I was saying earlier - to filter the posts, you need to check on display whether the category has been selected. If it has, change the query to have a WHERE clause using that category, if it has not, just use the query you have already.

You could look at that, then use the value of the drop-down selection. There are other ways, including using the POST method and checking to see if the script was called using that method, or you could use Ajax so that when the value of the dropdown changes, it re-populates the list without re-drawing the entire page.


closed #6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.