SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Feb 2013
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making a blog, question about categories.

    WARNING! PHP NOOB HERE.


    So i have almost finished making a blog from scratch. I can login and logout from admin panel. And from the adminpanel I can add/edit/delete posts and categories.

    I have tre mysql tables which are posts, users, categories.

    My question is regarding echoing out the categories and posts.

    On my right side of the blogpage i have the standard "latest posts" sidebar. The thing I don't know how do do is how to echo out the posttitles under the correct category, right now i dont even echo out the categories only the titles like this:

    Code PHP:
    function getTitles() {
    	$query = mysql_query("SELECT * FROM posts ORDER BY `ID` DESC") or die(mysql_error());
    	while($post = mysql_fetch_assoc($query)) {
    		echo "<h3><a href=\"singlePost.php?=" . $post['Title'] . "\">" . $post['Title'] . "</a></h3>";
    	}
    }

    WHat should i add to this to echo out the categories and get the correct titles under the correct category? I guess it has something to do with the Category_ID row i have in my post table?

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    2 things.

    This query using select * when you only actually want the title is very wasteful, imagine you have a blog post with 10,000 chars
    Code:
    "SELECT * FROM posts ORDER BY `ID` DESC"
    prefer:
    Code:
    "SELECT `Title` FROM posts ORDER BY `ID` DESC"
    Second, you will have to do a table JOIN if you want to fetch all categories with their titles. mysql table join example

    Get the sql working, then come back if you cannot work out how to have PHP display them as:

    Category 1
    =========
    title 1
    title 2
    title 3

    Category 2
    =========
    .. etc

    Else, post the schemas for these two tables.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2013
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    2 things.

    This query using select * when you only actually want the title is very wasteful, imagine you have a blog post with 10,000 chars
    Code:
    "SELECT * FROM posts ORDER BY `ID` DESC"
    prefer:
    Code:
    "SELECT `Title` FROM posts ORDER BY `ID` DESC"
    Second, you will have to do a table JOIN if you want to fetch all categories with their titles. mysql table join example

    Get the sql working, then come back if you cannot work out how to have PHP display them as:

    Category 1
    =========
    title 1
    title 2
    title 3

    Category 2
    =========
    .. etc

    Else, post the schemas for these two tables.
    Thanks for the reply! I changed * to title in the query but now i get error:

    Code PHP:
    ( ! ) SCREAM: Error suppression ignored for
    ( ! ) Notice: Undefined index: ID in C:\wamp\www\qssFINAL\includes\functions.php on line 14
    Call Stack
    #	Time	Memory	Function	Location
    1	0.0006	671952	{main}( )	..\news.php:0
    2	1.0102	704344	getTitles( )	..\news.php:8

    Here are my two tables posts and categories btw:

    Code SQL:
    CREATE TABLE IF NOT EXISTS `categories` (
      `ID` INT(11) NOT NULL AUTO_INCREMENT,
      `Title` VARCHAR(100) NOT NULL,
      `Description` VARCHAR(250) NOT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
     
    CREATE TABLE IF NOT EXISTS `posts` (
      `ID` INT(11) NOT NULL AUTO_INCREMENT,
      `Title` VARCHAR(150) NOT NULL,
      `Author` VARCHAR(100) NOT NULL,
      `Content` text NOT NULL,
      `Category_ID` INT(11) NOT NULL,
      `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    So what is on line 14?

  5. #5
    SitePoint Addict
    Join Date
    Feb 2013
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    So what is on line 14?
    Code PHP:
    echo "<h3><a href=\"singlePost.php?ID=" . $post['ID'] . "\">" . $post['Title'] . "</a></h3>";

    And here is the whole function:

    Code PHP:
    function getTitles() {
    	$query = mysql_query("SELECT `Title` FROM posts ORDER BY `ID` DESC") or die(mysql_error());
    	while($post = mysql_fetch_assoc($query)) {
    		echo "<h3><a href=\"singlePost.php?ID=" . $post['ID'] . "\">" . $post['Title'] . "</a></h3>";
    	}
    }

  6. #6
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    I suppose this is personal taste, but in my experience, a 'recent posts' list would simply list recent posts, while a category list would list categories. Most sidebars I've seen look like this.

    Recent Articles
    ----
    Some Post
    Another Post
    Older Post

    Categories
    ----
    Cat 1
    Cat 2
    Cat 3

    The reason I like this layout is because in most blogs, assigning a category(ies) to a post is optional, and a given post may not have a category. The recent post list would really only need to display the most recent posts, regardless of category. And then, lastly, a short list of categories used to spawn a 'search results' style page, with categorically related posts on them. Of course, this is just my two cents. Remember, keep things simple, not just for the users sake, but your own.

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2012
    Location
    United Kingdom
    Posts
    113
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ReGGaeBOSS View Post
    Code PHP:
    echo "<h3><a href=\"singlePost.php?ID=" . $post['ID'] . "\">" . $post['Title'] . "</a></h3>";

    And here is the whole function:

    Code PHP:
    function getTitles() {
    	$query = mysql_query("SELECT `Title` FROM posts ORDER BY `ID` DESC") or die(mysql_error());
    	while($post = mysql_fetch_assoc($query)) {
    		echo "<h3><a href=\"singlePost.php?ID=" . $post['ID'] . "\">" . $post['Title'] . "</a></h3>";
    	}
    }
    As the error states; the index you're using to access an element in the $post array is not set. This is occurring because you aren't selecting the ID column in the data being fetched; only the Title column.

  8. #8
    SitePoint Addict
    Join Date
    Feb 2013
    Posts
    289
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Serenarules View Post
    I suppose this is personal taste, but in my experience, a 'recent posts' list would simply list recent posts, while a category list would list categories. Most sidebars I've seen look like this.

    Recent Articles
    ----
    Some Post
    Another Post
    Older Post

    Categories
    ----
    Cat 1
    Cat 2
    Cat 3

    The reason I like this layout is because in most blogs, assigning a category(ies) to a post is optional, and a given post may not have a category. The recent post list would really only need to display the most recent posts, regardless of category. And then, lastly, a short list of categories used to spawn a 'search results' style page, with categorically related posts on them. Of course, this is just my two cents. Remember, keep things simple, not just for the users sake, but your own.
    Its something like that i want to create, i was a bit unclear in my threadstart. The thing is I dont understand how to make the correct posts show under(when clicking on) the correct category.

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2012
    Location
    United Kingdom
    Posts
    113
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ReGGaeBOSS View Post
    Its something like that i want to create, i was a bit unclear in my threadstart. The thing is I dont understand how to make the correct posts show under(when clicking on) the correct category.
    Could you just not select all of the posts pertaining to a certain category using the WHERE clause in SQL? It's hard to give you any sort of definitive answer without seeing more about your database set-up.

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Well, you altered your getTitles() function from your original posting, you now reference the ID as well as the title.

    So now you will need to explicitly select ID, Title from the table.

    Code:
    mysql_query("SELECT `ID`, `Title` FROM posts ORDER BY `ID` DESC");
    If you have not understood this fundamental premise then you really need to go and read some tutorials on the basics of SQL, you should do that next.

    Any decent tutorial will also cover how to make a JOIN, where you join data from two tables, and then select what you want from that join.

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ReGGaeBOSS View Post

    Code SQL:
    CREATE TABLE IF NOT EXISTS `categories` (
      `ID` INT(11) NOT NULL AUTO_INCREMENT,
      `Title` VARCHAR(100) NOT NULL,
      `Description` VARCHAR(250) NOT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
     
    CREATE TABLE IF NOT EXISTS `posts` (
      `ID` INT(11) NOT NULL AUTO_INCREMENT,
      `Title` VARCHAR(150) NOT NULL,
      `Author` VARCHAR(100) NOT NULL,
      `Content` text NOT NULL,
      `Category_ID` INT(11) NOT NULL,
      `Date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
    You should test your join by inputting it into your database with whatever db management tool you use, eg PHPMyAdmin is popular.

    Here is an untested attempt to grab the categories title with its posts.
    Code sql:
    SELECT c.Title AS category_title, p.Title AS post_title, p.ID AS post_id
    FROM categories AS c 
    LEFT JOIN posts AS p
    ON c.ID = p.Category_ID
    GROUP BY c.Title

    I've used aliases in this statement categories as c as a) a means of shorthand and b) because both tables contain fields called ID and Title, so you have to tell your DB which one you want to use - otherwise it will throw an error.

    ps what I have not dealt with here is if there is a Category which does not contain any posts yet, but looking at your auto-increment numbers it seems you only have some test data in there. In readiness for your eventual looping through these results, you should make sure that at least 1 of your categories has more that one single post.

  12. #12
    Resident OCD goofball! bronze trophy Serenarules's Avatar
    Join Date
    Dec 2002
    Posts
    1,911
    Mentioned
    26 Post(s)
    Tagged
    0 Thread(s)
    I don't mean to make things more complex, but you should probably have a 'published' bit in the 'posts' table to separate out those posts that are still in draft state. Just add a 'where p.draft = 0' to your SQL.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •