SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Question bout how best to implement categories in MySQL

    Hey everyone,

    I'm working on a new website that is basically a simple but very much glorified blog to publish articles. One thing that i need to do is employ a tag or category system, that will help organize the articles according to what tags are picked. I came up with a basic idea of how i am going to do this, however i'm not sure of this is the most effective way to get this done.

    Here is what i'm thinking:

    On the add article page give the user(me and my friend ) the ability to add tags (separated by commas) into an input text box, when the form is submitted put those categories onto a table in a database that holds the article and relevant info.

    Than on the article page explode the tags by commas, trim them accordingly and include them as links that link back to another page that will conduct a search where that tag is relevent on the database and retrieve the ids which it will than use to get the articles.

    i will also have a categories page that will search through the tags field and display the results as links that will link back to those pages as well. However, since im storing the tags as a string rather than separate strings in the database, i don't think this will work. Also basically i'm asking what you think is the best way of handling this.

    Thanks,
    MaxDream01
    PHP is FUN

  2. #2
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I would create a table just for the tags and their associated article ID. All you would really need is two fields... the tag, and the corresponding article ID.

    Articles:
    Code:
    ID | Title | Date
    ------------------
    2  | Some Title | 2012-05-05
    3  | Another Title | 2012-06-01
    Tag table:
    Code:
    Article_ID | Tag
    ------------------
    2 | A Tag
    2 | Another Tag
    3 | A Tag for article_id 3
    Then you can search for a tag, and get a list of all associated articles, or you can search for an article and get a list of all associated tags.

  3. #3
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh i see, so have the article id of some article as the id to the tags that way i can just use the article id to reference that articles tags as well as just use a particular tags id to reference an article, that's really helpful thanks kduv.
    PHP is FUN

  4. #4
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    No problem.

  5. #5
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, so i basically followed this idea of using a separate table to store tags and corresponding article ids, however i cant seem to get the tags table to insert new entries, i think this because of the way i am handling it, (nothing happens it just doesn't insert any entries), but i'm not sure what to do here is my code:

    also some notes $o_id is the id of the article, there may be multiple tags in the entry that should be separated by commas, im trying to break up the string based on the commas, get rid of all white spaces and update each tag separately into the database while still maintaining the article id, anyway here is my code, oh and im using a PDO to handle this $dbcon, but thats fine as i used it to insert an entry in the articles table which worked fine:
    PHP Code:
    $sqlTag 'INSERT INTO Tags (id, Tags) VALUES (:t_id, :tags)';

        try {
            for (
    $j 0$j < ($n 1); $j++){
                
    $stmtTag $dbcon->prepare($sqlTag);
                    
    $stmtTag->execute(array(
                    
    ':t_id' => $o_id,
                    
    ':tags' => $ftag[$j])
                    );
            }
        }catch (
    PDOException $e){
        echo 
    'could not insert tags into database';
        exit; 
    PHP is FUN

  6. #6
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Well, to begin with, you don't need the colon in the key fields of the execute array.
    A foreach may be a little more efficient as well.

    Try something similar to the following:
    PHP Code:
    <?php
    $sqlTag 
    'INSERT INTO Tags (id, Tags) VALUES (:t_id, :tags)'
    $ftag = array('tag1''tag2''tag3''etc');
    $stmtTag $dbcon->prepare($sqlTag);

    foreach (
    $ftag as $tag)
    {
        try
        {
            
    $stmtTag->execute(array(
                
    't_id' => $o_id
                
    'tags' => $tag
            );
        }
    }

    catch (
    PDOException $e)

        echo 
    'could not insert tags into database'
        exit; 
    }
    ?>

  7. #7
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks kduv, i tried your suggestion of using a foreach loop, i restructured the code a little though to fit my program, however it still, didnt work, i also caught no exception so i believe this is a logical problem. Here is how i edited the code i also included, how i formatted the string, something i should have shown in my last post:

    PHP Code:
    //create tags
    $tag explode(','$tags);
    $n count($tag);
    for ( 
    $i=0$i < ($n-1); $i++){ 
    $ftag[$i] = trim($tag[$i]);
    }
    //insert tags into table
    $sqlTag 'INSERT INTO Tags (id, Tags) VALUES (:t_id, :tags)';
    try {   
    $stmtTag $dbcon->prepare($sqlTag); 
        if (
    $stmtTag)
        {
            
    //run query
            
    foreach ($ftag as $thetag
            { 
                
    $stmtTag->execute(array( 
                    
    't_id' => $o_id,  
                    
    'tags' => $thetag)  
                    ); 
            }
        }
    }

    catch (
    PDOException $e
    {  
        echo 
    'could not insert tags into database:' $e->getMessage();  
        exit;  

    PHP is FUN

  8. #8
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    PHP Code:
    <?php
    //create tags 
    $tag explode(','$tags); 
    for ( 
    $i=0$i count($tag); $i++){  
        
    $tag[$i] = trim($tag[$i]); 
    }

    echo 
    "<pre>\no_id = {$o_id}\n";
    print_r($tag);
    echo 
    "</pre>";

    //insert tags into table 
    $sqlTag 'INSERT INTO Tags (id, Tags) VALUES (:t_id, :tags)'
    try {    
        if (
    $stmtTag $dbcon->prepare($sqlTag)) 
        { 
            
    //run query 
            
    foreach ($tag as $thetag)  
            {  
                
    $stmtTag->execute(array(  
                    
    't_id' => $o_id,   
                    
    'tags' => $thetag)   
                    );  
            } 
        } else {
            exit(
    "Couldn't prepare the statement.");
        }


    catch (
    PDOException $e)  
    {   
        echo 
    'could not insert tags into database:' $e->getMessage();   
        exit;   
    }  
    ?>
    Check the output of the print_r() and make sure all your tags are there as they should be. I changed the $n portion of your for loop as it would have left off the last variable.

    Lets first make sure the input variables are correct.

  9. #9
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok i tested that code and i got exactly what i expected(i just picked no and yes as tags when i submitted the form) but still it did not insert it into the tags table:
    Code:
    o_id = 0
    Array
    (
        [0] => no
        [1] => yes
    )
    PHP is FUN

  10. #10
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Hmm, could you post the entire file so I can see where the DB connection is being included/made ... of course star/blank out your username/password.

  11. #11
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    UPDATE
    I rewrote the code that was missing from the above post and included it in this one, i tried to run it and everything works except the part that inserts into teh tag table and also teh comments table does not get created(i had that problem before too, nothing new) I am really stumped as to why, because i feel taht all my logic is correct and my code is clean, maybe im just missing something stupid .

    Here is all the code:
    PHP Code:
    <?php
    //create variables
    $title $_POST['title'];
    $article $_POST['post'];
    $tags $_POST['tags'];
    $o_id $_POST['o_id'];
    $time $_SERVER['REQUEST_TIME'];
    $today date("l, M d o @ g:ia T"$time);
    $year date("Y"$time);
    if (empty(
    $title) || $title == ' '){
        echo 
    '<meta http-equiv="refresh" content="0; URL=addPost.php?error=noname">;';
        exit;
    }else if (empty(
    $article) || $article == ' '){
        echo 
    '<meta http-equiv="refresh" content="0; URL=addPost.php?error=nopost">;';
        exit;
    }

    //connect to database
    $dbuser '************';
    $dbpass '************';
    $dsn '************;';
    try {
        
    $dbcon = new PDO($dsn$dbuser$dbpass);
    } catch (
    PDOException $e) {
        echo 
    'could not connect to database';
        echo 
    'Connection Failed: '$e->getMessage();
        exit;
    }

    //insert new row
    $sql 'INSERT INTO Articles (o_id, Time, Year, Title, Post) VALUES (:o_id, :today, :year, :title, :post)';

    $stmt $dbcon->prepare($sql);

    //preform query
    try {
    $stmt->execute(array(
        
    ':o_id' => $o_id,
        
    ':today' => $today,
        
    ':year' => $year,
        
    ':title' => $title,
        
    ':post' => $article)
        );
    } catch (
    PDOException $e){
        echo 
    'could not insert article into database';
        exit;
    }

    //create tags 
    $tag explode(','$tags); 
    for ( 
    $i=0$i count($tag); $i++){  
        
    $tag[$i] = trim($tag[$i]); 
    }

    echo 
    "<pre>\no_id = {$o_id}\n";
    print_r($tag);
    echo 
    "</pre>";

    //insert tags into table 
    $sqlTag 'INSERT INTO Tags (id, Tags) VALUES (:t_id, :tags)'
    try {    
        if (
    $stmtTag $dbcon->prepare($sqlTag)) 
        { 
            
    //run query 
            
    foreach ($tag as $thetag)  
            {  
                
    $stmtTag->execute(array(  
                    
    't_id' => $o_id,   
                    
    'tags' => $thetag)   
                    );  
            } 
        } else {
            exit(
    "Couldn't prepare the statement.");
        }


    catch (
    PDOException $e)  
    {   
        echo 
    'could not insert tags into database:' $e->getMessage();   
        exit;   

    //create comments table
    $tablename 'comment'.$o_id;
    $sqlComment 'CREATE TABLE :tableName
        (
        id INT NOT NULL PRIMARY AUTO_INCREMENT,
        Name VARCHAR(9999) NOT NULL,
        Email VARCHAR(9999) NOT NULL,
        Site VARCHAR(99999),
        Comment VARCHAR(999999) NOT NULL
        )'
    ;
    try{
        
    $stmtComment $dbcon->prepare($sqlComment);
        if(
    $stmtComment){
            
    //run query
            
    $stmtComment->execute(array(
                
    ':tableName' => $tablename)
            );
        }else{
            
    //if problem with $dbcon->prepare
            
    exit('Could not prepare MySQL statement!');
        }
    }
    catch(
    PDOException $e){
        echo 
    'there was a problem creating the comment table'.$e->getMessage();
    }
        
    //manipulate file system
    $dir 'Articles/'.$year;
    $fname rawurlencode($title);
    if (!
    is_dir($dir)){
        
    mkdir($dir0777);
    }
    //create article specific file
    $location $dir.'/'.$fname.'.php';
    //check if file exists
    if (file_exists($location)){
        
    $location2 $dir.'/'.$fname.'2.php';
        
    $fp fopen($location2'x+');
    }else{
    $fp fopen($location'x+');
    }
    //create data to write

    $npage = <<<HERE

    HERE;

    ?>
    PHP is FUN

  12. #12
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Try changing this:
    PHP Code:
    try { 
        
    $dbcon = new PDO($dsn$dbuser$dbpass); 

    To this (it will make sure it's reporting errors a little better):
    PHP Code:
    try { 
        
    $dbcon = new PDO($dsn$dbuser$dbpass);
        
    $dbcon->setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);


  13. #13
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh thats great, i had'nt even thought of changing the error mode, anyway i ended up finding out that i misspelled a field (e.g. Tags => Tag), however after i fixed that i got another strange code that i think means i messed up my values somewhere, although i cant really tell what it means:

    SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    PHP is FUN

  14. #14
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    That means you prepared X number of fields in an SQL statement, but didn't bind the same number of variables in your execute() statement. It could be that you forgot to bind one, or that you misspelled the key name/field placeholder.

  15. #15
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Also, instead of creating a new table for comments for each post, I would do similar to the tags and create one comment table, associating each comment with the post ID (as well as giving the comment its own ID).

  16. #16
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    could that be caused by the field name being id but the placeholder being :t_id, or possibly by just having a separate auto increment field in the table?
    PHP is FUN

  17. #17
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I'd look at your create table code. I'm not positive you can use placeholders for the table name, but I could be wrong.

  18. #18
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    UPDATE:
    just Googled it your right, you cant use placeholders with table names but you can use string concentation

    I may end up doing that, but can i use a variable as the name to a table e.g.

    PHP Code:
    $sqlComment 'CREATE TABLE ' $tablename '
        (
    //and so on 
    also the error was being caught at the tags part, so wouldn't the error come form there?

    could not insert tags into database:SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    PHP is FUN

  19. #19
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Yup. Throw an echo statement right above the execute command to see what it's trying to put in there.
    PHP Code:
    foreach ($tag as $thetag)   
            {
                echo 
    "Binding {$o_id} and {$thetag}<br />\n";
                
    $stmtTag->execute(array(   
                    
    't_id' => $o_id,    
                    
    'tags' => $thetag)    
                    );   
            } 

  20. #20
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok this is my output:
    Code:
    o_id = 0
    Array
    (
        [0] => no
        [1] => yes
    )
    Binding 0 and no
    could not insert tags into database:SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
    this seems right, i think it catches the error before it has a second chance around for the other tag.
    PHP is FUN

  21. #21
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Lets see what happens if we change it around to this:
    PHP Code:
    //insert tags into table  
    $sqlTag 'INSERT INTO Tags (id, Tags) VALUES (:o_id, :tags)';  
    try {      
        
    //run query  
        
    foreach ($tag as $thetag)   
        {
            
    $stmtTag $dbcon->prepare($sqlTag); 
            echo 
    "Binding :o_id and {$o_id}<br />\n";
            
    $stmtTag->bindValue('o_id'$o_id);
            echo 
    "Binding :tags and {$thetag}<br />\n";
            
    $stmtTag->bindValue('tags'$thetag);
            
    $stmtTag->execute();   
        }  


  22. #22
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok i tried what you said (changed Tags to Tag, that was my error before), trying to bind the values however i got this error code this time
    Code:
    o_id = 0
    Array
    (
        [0] => no
        [1] => yes
    )
    Binding :o_id and 0
    Binding :tags and no
    Binding :o_id and 0
    Binding :tags and yes
    there was a problem creating the comment tableSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( id INT NOT NULL PRIMARY AUTO_INCREMENT, Name VARCHAR(9999) NOT NULL, Email ' at line 2
    which means taht the error is in teh comments, so ill check my database to see if it worked
    PHP is FUN

  23. #23
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    OK, either in phpMyAdmin, or your prefered MySQL client, run this command on that database: DESCRIBE Tags

    Then tell me what that says.

  24. #24
    SitePoint Zealot maxdream01's Avatar
    Join Date
    Feb 2011
    Location
    USA
    Posts
    149
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And sure enough it did work thanks kduv, ii think ill do what you said and also do this type of table for comments as well so that error can be ignored!
    srry uploaded just as you did
    PHP is FUN

  25. #25
    SitePoint Addict kduv's Avatar
    Join Date
    May 2012
    Location
    Maui, HI
    Posts
    211
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Cool. Glad it's working for you now.


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
  •