Showing posts order by catagories

[TobiasNielsen]

first of all.. iam still new to php and only on a self learnd level..
But i got this to work 

 

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

function getPosts() {
	$query = mysql_query("SELECT * FROM posts") or die(mysql_error()) ;
	while($post = mysql_fetch_assoc($query)) {
		echo "<h2>" . $post['Title'] . " af " . $post['Author'] . " Katagori " . $post['Category_ID'] .  "</h2>";
		echo $post['Content'];
	}
}
?>

 

now my quest is to turn the flow so the newest post comes first on the page, and make it show the Category title insted of the id and to sort the posts into the diffent categories  Category_ID i think.. 

any help on that?

 

[Jessica]

Make your query ORDER BY the date posted field.

To do the categories you'll need to make it JOIN to the category table, ON the category id.

[Psycho]

Hmm, you say you want the newest posts first, but that you also want them sorted by category. I'm not sure how you mean for that to happen. If the newest post has a category of A and the second newest post has a category of B, do you want all the category A posts shown before the category B post?

 

This is really a MySQL question and not a PHP question.

 

Also, you haven't stated what your DB structure is. I will assume categories are defined in a related table. The below only groups by category and doesn't do anything regarding the newest posts because I'm not sure what you really want.

 

 

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

function getPosts()
{
    $query = "SELECT p.Title, p.Author, p.content,
                     c.Category_ID, c.Category_Name
              FROM posts AS p
              JOIN categories AS c
                  ON p.Category_ID = c.Category_ID
              ORDER BY Category_Name";
    $result = mysql_query($query) or die(mysql_error());
    while($post = mysql_fetch_assoc($result))
    {
        echo "<h2>{$post['Title']} af {$post['Author']} Katagori {$post['Category_ID']}</h2>{$post['Content']}\n";
    }
}
?>

Edited March 11, 2013 by Psycho

[TobiasNielsen]

i want to show it like on 3 pages (if i have 2 categories) first like they see it now but with the order by like jessica said and if people want to se the content of CatagoryA they will see id5 then id3 and last id1 and if they want to see CategoryB they will see id4 and then id2 and so on if the posts in that order

 

posts with
id 1 with categoryA

id 2 with categoryB

id 3 with categoryA

id 4 with categoryB

id 5 with categoryA

 

is that what you mean?

[Jessica]

That actually made things more confusing.

 

Either way you need to JOIN to the other table first.

[Psycho]

Um,yeah. Your explanation is very confusing.

 

You state you want the most recent post first, but that you want them sorted by category as well. That doesn't make sense. So, let's create a scenario:

 

Post 1, Time 8:00AM, Category A

Post 2, Time 9:00AM, Category B

Post 3, Time 10:00AM, Category B

Post 4, Time 11:00AM, Category A

 

How do you want those displayed?

[TobiasNielsen]

like this               ((((Re-edit)))

 

script / page  1

 

Post 4, Time 11:00AM, Category A

Post 3, Time 10:00AM, Category B

Post 2, Time 9:00AM, Category B

Post 1, Time 8:00AM, Category A

 

 

 

script / page 2

 

Post 4, Time 11:00AM, Category A

Post 1, Time 8:00AM, Category A

 

 

script / page 3 

 

Post 3, Time 10:00AM, Category B

Post 2, Time 9:00AM, Category B

Edited March 11, 2013 by TobiasNielsen

[Psycho]

OK, so you want TWO COMPLETELY DIFFERENT solutions. I don't see where you clearly stated that. All I see was a request to have the records ordered by data AND grouped by category.

 

In any event, this is still a MySQL problem (moving to the MySQL forum).

 

Request #1:

Show posts in order of submission and include the Category name in the output. You will need to JOIN the posts table onto the Category table (assuming there is a separate table - which there should be). I already showed how to do that, but I added an order by Category due to the previous confusing request. You should be using something like this. BUt, this is only an example because I do not know your table/field names.

 

    $query = "SELECT p.Title, p.Author, p.content, p.date
                     c.Category_ID, c.Category_Name
              FROM posts AS p
              JOIN categories AS c
ON p.Category_ID = c.Category_ID
              ORDER BY p.date DESC";

 

Request #2:

Show all the posts for a specific category. For this you will want to build a page that takes an optional parameter on the query string: e.g. show_category.php?catID=3. Then on the script you will use that category ID to get all the posts associated with that category. Here is an example of the query to use

 

$catID = intval($_GET['catID']);
    $query = "SELECT p.Title, p.Author, p.content, p.date
                     c.Category_ID, c.Category_Name
              FROM posts AS p
              JOIN categories AS c
                ON p.Category_ID = c.Category_ID
              ORDER BY p.date DESC
              WHERE c.Category_ID = '$catID'";