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.
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.
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.
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:
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:
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.
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:
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>\
o_id = {$o_id}\
";
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;
?>
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
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.
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).
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?
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.
.