SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Array Categories from MySQL query

    I'm working on a function for my blog script that will get category ids from a field on my blog table. The ids will be in a list like "1;2;3;4" and I've got the code to explode the ids and make this into an array, I just don't know how to make a query for each ID to get the category name to show on each blog entry. This is a work in progress so some code in the end may not fit in at all.

    PHP Code:
    function getBlogCategories() {
        
    $getcatids  "SELECT cat_ids FROM blog WHERE blog_id = '".$id."'";
        if (!
    $catids mysql_query($getcatids)) {
             die(
    "Query problem: $getcatids." mysql_error());
        }
        if (
    mysql_num_rows($catids) > 0) {
            
    $mcategories explode(';'$catids);
            
    $cat_sql_array = array();
            
    $output '';
            foreach(
    $mcategories AS $categoryid) {
                
    $categoryid = (int)$categoryid;
                if (
    $categoryid != 0) {
                    
    # SELECT cat_id,cat_name FROM blog_categories WHERE cat_id = '$categoryid'
                    
    $cat_sql_array[] = "";
                    while (
    $cat mysql_fetch_object($catids)) {
                        
    $output[] = '<a href="http://creativeburst.org/category.php?id='.$cat_id.'">'.$cat_name.'</a>';
                    }
                }
            }
        echo 
    implode(', '$output);
        )

    ~ Hannah

  2. #2
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So you're having category ids saved in one field as a colon-separated list? Lack of normalization there, and as I can see you're getting into trouble with running queries in a loop. Consider redesigning the db.

    Anyways, you forget to fetch the data;
    Code php:
    $mcategories = explode(';', mysql_result($catids,0));
    Saul

  3. #3
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why redesign the database?
    ~ Hannah

  4. #4
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Because you have category ids for each blog saved in a colon-separated list. As you can see, it's already giving you trouble. You have to explode the list and run a query for each id to get category data. For this, one query should be enough.

    Consider having a table blogs_categories (blog_id,category_id), which would allow selecting the blog categories in one query:
    Code sql:
    SELECT blog_categories.cat_id
         , blog_categories.cat_name
       FROM blog_categories
    JOIN blogs_categories
      ON blogs_categories.categories_id=blog_categories.cat_id
    JOIN blogs
      ON blogs.blog_id=blogs_categories.blog_id
       WHERE blog_id=123
    Saul

  5. #5
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay... but it'd actually take more work. At the moment, I'm manually inserting into the database.
    Last edited by stymiee; Jul 22, 2007 at 12:20.
    ~ Hannah

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Not really. You save in the long run. It just a matter of adding a two field record to blogs_categories, whenever you need to add a blog to category anyway.
    Saul

  7. #7
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, so could you repeat to me the structure I should have for my database in order to do that query- just so I can be sure I've got it right?

    And what would I enter in categories_id on blogs_categories?
    ~ Hannah

  8. #8
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Your other tables are fine, only you don't need the cat_ids in blogs. Then blogs_categories only need two fields: categories_id and blogs_id (both serving as a primary key). Look at it as a mapper between two tables -- it's the only way to eliminate many-to-many relationship and comply with 1nf (and 1nf is the least what you should aim for).

    Then to add a blog to a category, you add a respective pair of categories_id and blogs_id. And that's about it.
    Saul

  9. #9
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help!
    ~ Hannah

  10. #10
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geeklove View Post
    Okay, so could you repeat to me the structure I should have for my database in order to do that query- just so I can be sure I've got it right?

    And what would I enter in categories_id on blogs_categories?
    Wait... PHPMyAdmin won't let me have more than one 'primary' key.
    ~ Hannah

  11. #11
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geeklove View Post
    Wait... PHPMyAdmin won't let me have more than one 'primary' key.
    Yes, but allows you to have several columns for one key.
    Saul

  12. #12
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by php_daemon View Post
    Yes, but allows you to have several columns for one key.
    So what now? I've never dealt with such a complicated query before.
    (I have the query divided between separate lines to make it easier for me)

    PHP Code:
    function getCategories {
        
    $query "SELECT blog_categories.cat_id , blog_categories.cat_name FROM blog_categories ";
        
    $query .= "JOIN blogs_categories ON blogs_categories.categories_id=blog_categories.cat_id ";
        
    $query .= "JOIN blogs ON blogs.blog_id=blogs_categories.blog_id WHERE blog_id=".$id;
        if (!
    $result mysql_query($query)) {
             die(
    "Query problem: $query.".mysql_error());
        }
        while(
    $row mysql_fetch_array($result)) {
            
        }

    ~ Hannah

  13. #13
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Go on with the output:
    Code php:
    function getCategories($id) {
     
        $query = "SELECT blog_categories.cat_id , blog_categories.cat_name FROM blog_categories ";
        $query .= "JOIN blogs_categories ON blogs_categories.categories_id=blog_categories.cat_id ";
        $query .= "JOIN blogs ON blogs.blog_id=blogs_categories.blog_id WHERE blog_id=".(int)$id;
        if (!$result = mysql_query($query)) {
             die("Query problem: $query.".mysql_error());
        }
     
        while($row = mysql_fetch_array($result)) {
             $output[] = '<a href="http://creativeburst.org/category.php?id='.$row['cat_id'].'">'.$row['cat_name'].'</a>';
        }
        return $output;
    }
    Saul

  14. #14
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geeklove View Post
    So what now? I've never dealt with such a complicated query before.
    (I have the query divided between separate lines to make it easier for me)

    PHP Code:
    function getCategories {
        
    $query "SELECT blog_categories.cat_id , blog_categories.cat_name FROM blog_categories ";
        
    $query .= "JOIN blogs_categories ON blogs_categories.categories_id=blog_categories.cat_id ";
        
    $query .= "JOIN blogs ON blogs.blog_id=blogs_categories.blog_id WHERE blog_id=".$id;
        if (!
    $result mysql_query($query)) {
             die(
    "Query problem: $query.".mysql_error());
        }
        while(
    $row mysql_fetch_array($result)) {
            
        }

    Ahh, I'm getting errors!

    Filed under Query problem: SELECT blog_categories.cat_id , blog_categories.cat_name FROM blog_categories JOIN blogs_categories ON blogs_categories.categories_id=blog_categories.cat_id JOIN blog ON blog.id=blog_id WHERE id=6.Unknown column 'blog_id' in 'on clause'

    EDIT: Never mind! I had to remove all the blog_categories., blogs_categories., and blog. things.
    ~ Hannah

  15. #15
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But wait, what format do I use when entering multiple category ids in categories_id on blogs_categories? 1,2,3 or 1;2;3? Or what?
    ~ Hannah

  16. #16
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    No, just a single id. For example if you need to add blog #1 to categories #1, #2 and #3, add three records to blogs_categories: 1,1; 1,2 and 1,3.

    Code sql:
    INSERT INTO blogs_categories (blog_id, cat_id) VALUES (1, 1);
    INSERT INTO blogs_categories (blog_id, cat_id) VALUES (1, 2);
    INSERT INTO blogs_categories (blog_id, cat_id) VALUES (1, 3);
    Saul

  17. #17
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sweet, thanks.
    ~ Hannah

  18. #18
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay now the array isn't working...

    Notice: Undefined variable: category_output in /home/.luther/creativeburst/CreativeBurst/blog.php on line 41

    Warning: implode() [function.implode]: Bad arguments. in /home/.luther/creativeburst/CreativeBurst/blog.php on line 41
    Filed under:
    Comments [8] &#187;
    PHP Code:
                while($row mysql_fetch_array($cat_result)) {
                    
    $category_output[] = '<a href="http://creativeburst.org/category.php?id='.$row['cat_id'].'">'.$row['cat_name'].'</a>';
                }
              echo 
    'Filed under: '.implode(", "$category_output);
                echo 
    '<br />'
    ~ Hannah

  19. #19
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    $category_output might be not created, do print_r($category_output) to check.
    Saul

  20. #20
    SitePoint Enthusiast geeklove's Avatar
    Join Date
    Jun 2007
    Location
    Iowa, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't have any values in the blogs_categories, and now I do, so that fixed my problem.
    ~ Hannah

  21. #21
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yep. You might wanna do a check in case there's no categories but you try to output:

    Code php:
    $category_output=array();
    while($row = mysql_fetch_array($cat_result)) {
        $category_output[] = '<a href="http://creativeburst.org/category.php?id='.$row['cat_id'].'">'.$row['cat_name'].'</a>';
    }
    if(count($category_output)>0){
        echo 'Filed under: '.implode(", ", $category_output);
        echo '<br />'; 
    }
    Saul

  22. #22
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm...

    > For example if you need to add blog...

    Remember to do that within a transaction so if there is ever a problem then you can rollback the operation on your database. Without using transactions you could have issues with your data integrity.

    Imagine your database begins to insert the first row of data, and then for some reason hangs? Your remaining inserts are not executed...

    Modify your database schema to use InnoDb,

    Code:
    create table ... ( ... ) engine=innodb, charset=utf8, auto_increment=1;
    Read the manual for more information on concurrency and ACID (Atomic, Consistency, Isolation and Durability) conformance.

  23. #23
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Yes, of course, transactions are to be used whenever possible.

    Although in this particular case that would be a loss of information, not data integrity (a lost category would not hurt the integrity).
    Saul

  24. #24
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm...

    > a lost category would not hurt the integrity

    I would beg to differ; Maybe you can live with this fault but hell... My clients would want my head on a plate if there was an issue with their relational data. I would think that it's pretty much the same with other developers, and their clients?

    Prevention is way better than a cure.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by php_daemon View Post
    Yes, of course, transactions are to be used whenever possible.
    transactions cause bottlenecks

    transactions are to be avoided whenever possible

    try this instead --
    Code:
    INSERT 
      INTO blogs_categories 
         ( blog_id, cat_id ) 
    VALUES 
         ( 1, 1 )
       , ( 1, 2 )
       , ( 1, 3 )


    other than this, php_daemon, may i say what an outstanding job you have done explaining the many-to-many structure and helping to resolve the problems along the way clearly and patiently
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •