Question bout how best to implement categories in MySQL

[maxdream01]

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

[kduv]

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:

ID | Title | Date
------------------
2  | Some Title | 2012-05-05
3  | Another Title | 2012-06-01

Tag table:

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.

[maxdream01]

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.

[kduv]

No problem.

[maxdream01]

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:

$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; 

[kduv]

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
$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; 
}
?>

[maxdream01]

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:

//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;  
} 

[kduv]

Try this:

<?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.

[maxdream01]

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:

o_id = 0
Array
(
    [0] => no
    [1] => yes
)

[kduv]

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.

[maxdream01]

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
//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($dir, 0777);
}
//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;

?>

[kduv]

Try changing this:

try { 
    $dbcon = new PDO($dsn, $dbuser, $dbpass); 
} 

To this (it will make sure it's reporting errors a little better):

try { 
    $dbcon = new PDO($dsn, $dbuser, $dbpass);
    $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} 

[maxdream01]

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

[kduv]

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.

[kduv]

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).

[maxdream01]

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?

[kduv]

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.

[maxdream01]

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.

$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

[kduv]

Yup. Throw an echo statement right above the execute command to see what it's trying to put in there.

foreach ($tag as $thetag)   
        {
            echo "Binding {$o_id} and {$thetag}<br />\n";
            $stmtTag->execute(array(   
                't_id' => $o_id,    
                'tags' => $thetag)    
                );   
        } 

[maxdream01]

ok this is my output:

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.

[kduv]

Lets see what happens if we change it around to this:

//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();   
    }  
} 

[maxdream01]

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

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

[kduv]

OK, either in phpMyAdmin, or your prefered MySQL client, run this command on that database: DESCRIBE Tags

Then tell me what that says.

[maxdream01]

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

[kduv]

Cool. Glad it's working for you now.