Jump to content

Building a Blog - Tag comparison issue


travisco87
Go to solution Solved by travisco87,

Recommended Posts

So I am building a blog from scratch and built everything and got it working. After some review I decided to add the ability to add tags and have run into a couple problems that I just cannot seem to find the right solution for. First I will explain what I am trying to do and what I am looking for(PS I do not want you to do all the work for me, I am looking for a nudge in the right direction. Otherwise how could I learn right?)

 

In the following code I am trying to first off insert a blog post and then if there are any tags to, pull the tags from the forms and put it into an array, next pull the tag's already in the database and compare them to the tags from the forms with the one's in the database. If they are NEW add the new tag to the tags and then update the tags table and the blog post tags table(This is used to connect the tag's and blog post together). If they are already in the system then update the number of times that tag is used and update the blog post tags table. 

 

 

Here is the code(PS I know it is bad and also that it is open to security problems, this will not be used in production until I have added in the appropriate code)

<?php
    if(!empty($_POST)) {
        include 'includes.php';
        if(mysql_safe_query('INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (%s,%s,%s,%s)', $_POST['title'], $_POST['post'], $_POST['author'], time()))
            echo 'Entry posted. <a href="post_view.php?id='.mysql_insert_id().'">View</a>';
            if(!empty($_POST['tags'])) {
                
                $tagString = str_replace(' ', '', $_POST['tags']);
                $tags = array();
                $tags = explode(',', $tagString);
                $queryTag = 'SELECT id, name FROM tags';
                $tagArray = array();
                $resultTag = mysql_query($queryTag);
                while(($row = mysql_fetch_assoc($resultTag))) {
                    $tagArray[$row['id']] = $row['name'];
                }
                foreach($tags as $tagArray['name']) {
                        $query = 'BEGIN TRANSACTION
                        INSERT INTO blog_post_tags (blog_post_id, tag_id) VALUES (' LAST_INSERTED_ID() . ', '  $tagArray['id'] . ')
                        UPDATE tags SET tag_count = tag_count + 1 WHERE id = ' . $tagArray['id'] . '
                        COMMIT';
                        $result = mysql_query($query);
                }
                
                }
            }
        else
            echo mysql_error();
    }
    
    
?>

This is where my issue's are and I need help being pointed in the right direction on how to accomplish this, 

 

First is how do I get the blog post id I just inputted into the system? I need this ID when I update the blog post tags(This is used to connect the tag's and blog post together). I tried LAST_INSERTED_ID() but could not get it to work, maybe I was inputting it incorrectly? 

 

Second, I am running into an issue when comparing the tags from the array and the tags from the database, I think it I might need to use a different format but I have tried, if, ifelse, while and so forth and cannot find a good fit. 

 

Third, any advice on structuring would be helpful, I am just starting out and need to fix my horrible coding habits ASAP. 

 

Thanks for your time and help with this! 

Link to comment
Share on other sites

So I am building a blog from scratch and built everything and got it working.

It works for now. Before you go any further, you need to re-write your code using Mysqli or PDO to connect to your database. The mysql_* functions are officially deprecated and slated for removal. Once that happens, your code will no longer work. I prefer PDO, so my examples will use that.

 

First is how do I get the blog post id I just inputted into the system?

Each API provides a function to return the last insert id. All you need to do is call that after your insert:

$sql = 'INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (?,?,?,?)';
$params = array($_POST['title'], $_POST['post'], $_POST['author'], time());
$stmt = $db->prepare($sql);
$stmt->execute($params);

$blogPostId = $db->lastInsertId();

Second, I am running into an issue when comparing the tags from the array and the tags from the database, I think it I might need to use a different format but I have tried, if, ifelse, while and so forth and cannot find a good fit.

First, run a SELECT query for the tag to try and locate it in the database and fetch it's existing ID. If it is not found, then run an INSERT to add it and use the lastInsertId method to get it's new ID. Save each ID to an array so you can later add the appropriate records to link the blog post.

 

$sql = 'SELECT id FROM tags WHERE name=?';
$fetchstmt = $db->prepare($sql);
$tags=array();
foreach (explode(',', $tagString) as $tag){
   $fetchstmt->execute(array($tag));
   $tagId = $fetchstmt->fetch(PDO::FETCH_COLUMN,0);
   $fetchstmt->closeCursor();
   
   if (!$tagId){
       //Do insert
       //Call lastInsertId
   }

   $tags[] = $tagId;
}

//Loop through $tags, add entry to blog_post_tags for each one

...then update the number of times that tag is used

There is no need to be keeping a count of the times a tag is used. You can determine that with a simple query on the blog_post_tags table with GROUP BY and COUNT().

SELECT tag_id, COUNT(*) as numTimeUsed FROM blog_post_tags GROUP BY tag_id
Link to comment
Share on other sites

I have rewritten it but I cannot figure out my issue now,

<?php
//If the post is not empty, continue
    if(!empty($_POST)) {
        include 'includes.php';
        
        //If the mysqli_safe_query returns valid grab the information and post it into a new blog post
        if(mysqli_safe_query) {
            $sql = 'INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (%s,%s,%s,%s)';
            $params = array($_POST['title'], $_POST['post'], $_POST['author'], time());
            $poststmt = $DBH->prepare($sql);
            $poststmt->execute($params);
            
            //Grab the new blog ID
            $blogPostId = $DBH->lastInsertId();
            
            //Grab the tags and compare
            if(!$_POST['tags']){
                //Select the tags from the database and store an id if found
                $sqlTag = 'SELECT id FROM tags WHERE name=' . $tag;
                $tagstmt = $DBH->prepare($sqlTag);
                $tagString = str_replace(' ', '', $_POST['tags']);
                $tags=array();
                
                //Take the tag's from the form input and explode them into an array after each comma
                foreach (explode(',', $tagString) as $tag){
                    $tagstmt->execute(array($tag));
                    $tagId = $fetchstmt->fetch(PDO::FETCH_COLUMN,0);
                    $tagstmt->closeCursor();
   
                    if (!$tagId){
                        $taginputsql = 'INSERT INTO tags SET name = ' . $tag;
                        $inserttagstmt = $DBH->prepare($taginputsql);
                        $inserttagstmt->execute($taginputsql);
                        
                        $newTagIds = $DBH->lastInsertId();
                        $tags[] = $newTagIds;
                        $tagPostition = count($tags);
                        
                        //Insert tag and blog tag id into the blog_post_tags table
                        $blogTagIdInsert = 'INSERT INTO blog_post_tags SET blog_post_id = ' . $blogPostId . ' tag_id = ' . $tags[$tagPosition];
                        $blogTagIdstmt = $DBH->prepare($blogTagIdInsert);
                        $blogTagIdstmt->execute($blogTagIdInsert);
                        }
                    else {
                        //If ID found insert into blog_post_tags table
                        $blogTagInsert = 'INSERT INTO blog_post_tags SET blog_post_id = ' . $blogPostId . ' tag_id = ' $tagId;
                    }
                $tags[] = $tagId;
                }
            }
        echo 'Entry posted. <a href="post_view.php?id='. $blogPostId .'">View</a>';
        }
        else {
            echo 'There was a porblem loading your page';
        }
    }
    
?>

I am getting this error,

 

 Parse error: syntax error, unexpected T_VARIABLE in C:\wamp\www\Rachels\post_add.php on line 130

 

 

Also when I switched everything over I keep getting an error when opening up the blog,

 

Here is my connection.php and include.php. 


include 'blogpost.php';
include 'connect.php';


function GetBlogPosts($inId=null, $inTagId=null)
{
    if (!empty($inId))
    {
        $query = mysqli_query("SELECT * FROM blog_post WHERE id = " . $inId . " ORDER BY id DESC");
    }
    elseif(!empty($inTagId))
    {
        $query = mysqli_query("SELECT blog_post.* FROM blog_post_tags LEFT JOIN (blog_post) ON (blog_post_tags.blog_post_id = blog_post.id) WHERE blog_post_tags.tagID =" . $inTagId . " ORDER BY blog_post.id DESC");
    }
    else
    {
        $query = mysqli_query("SELECT * FROM blog_post ORDER BY id DESC");
    }

    $postArray = array();
    
    if($query === FALSE) {
        die(mysql_error());
    }
    
    while ($row = mysqli_fetch_assoc($query))
    {
        $myPost = new BlogPost($row["id"], $row['title'], $row['post'], $row['author_id'], $row['date_posted']);
        array_push($postArray, $myPost);
    }
    return $postArray;
}

function mysqli_safe_string($value) {
        $value = trim($value);
        if(empty($value))           return null;
        elseif(is_numeric($value))  return $value;
        else                        return '"' . mysqli_real_escape_string($value) . '"';
    }
    
function mysqli_safe_query($query) {
        $args = array_slice(func_get_args(), 1);
        $args = array_map('mysql_safe_string', $args);
        return mysqli_query(vsprintf($query,$args));
    }
    
function redirect($uri) {
        header('location:'.$uri);
        exit;
    }
    
    
?>

connect.php File

<?php
    $config['db'] = array(
        'host'          => 'localhost',
        'username'      => '',
        'password'      => '',
        'dbname'        => 'nettus_blog'
    );

    try {
        $DBH = new PDO('mysql:host=' . $config['db']['host']. ';dbname=' .$config['db']['dbname'], $config['db']['username'], $config['db']['password']);
        $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMORE_EXPECTION);
        $DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $DBH->exec('SET CHARACTER SET utf8');
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();

    }
?>

It keeps saying the database is not there when it really is. 

 

Thanks again for your help, I am trying the best I can! If it is no where close to correct I will try and rewrite it again. 

Edited by travisco87
Link to comment
Share on other sites

Corrected the issue but I am getting this error dealing with the values

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[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 '%s,%s,%s,%s)' at line 1' in C:\wamp\www\Rachels\post_add.php on line 95

Do I need to store them in variables before I call the statement? OR can I try and do something like this 

$sql = 'INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (' . $_POST['title'] . ',' . $_POST['post'] . ',' . $_POST['author'] . ',' . time() . ')';

?

Link to comment
Share on other sites

Thank you! That was the article I needed to read. Ok so everything is working but the tag's input section,

<?php
//If the post is not empty, continue
    if(!empty($_POST)) {
        include 'includes.php';
        
        $sql = 'INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (?,?,?,?)';
        $params = array($_POST['title'], $_POST['post'], $_POST['author'], time());
        $poststmt = $DBH->prepare($sql);
        $poststmt->execute($params);
         
        //Grab the new blog ID
        $blogPostId = $DBH->lastInsertId();
           
        //Grab the tags and compare
        if(!empty($_POST['tags'])){
            //Select the tags from the database and store an id if found
            $sqlTag = 'SELECT id FROM tags WHERE name=?';
            $tagstmt = $DBH->prepare($sqlTag);
            $tagString = str_replace(' ', '', $_POST['tags']);
            $tags=array();
                
            //Take the tag's from the form input and explode them into an array after each comma
            foreach (explode(',', $tagString) as $tag){
                $tagstmt->execute(array($tag));
                $tagId = $tagstmt->fetch(PDO::FETCH_COLUMN,0);
                $tagstmt->closeCursor();
   
                if (!$tagId){
                    $taginputsql = 'INSERT INTO tags SET name = ' . $tag;
                    $inserttagstmt = $DBH->prepare($taginputsql);
                    $inserttagstmt->execute($taginputsql);
                        
                    $newTagIds = $DBH->lastInsertId();
                    $tags[] = $newTagIds;
                    $tagPostition = count($tags);
                        
                    //Insert tag and blog tag id into the blog_post_tags table
                    $blogTagIdInsert = 'INSERT INTO blog_post_tags SET blog_post_id = ' . $blogPostId . ', tag_id = ' . $tags[$tagPosition];
                    $blogTagIdstmt = $DBH->prepare($blogTagIdInsert);
                    $blogTagIdstmt->execute($blogTagIdInsert);
                    }
                 else {
                    //If ID found insert into blog post
                    $blogTagInsert = 'INSERT INTO blog_post_tags SET blog_post_id = ' . $blogPostId . ', tag_id = ' . $tagId;
                    $blogTagstmt = $DBH->prepare($blogTagInsert);
                    $blogTagstmt->execute($blogTagInsert);
                }
            $tags[] = $tagId;
            }
        }
     echo 'Entry posted. <a href="post_view.php?id='. $blogPostId .'">View</a>';
    }
    else {
        echo 'No Post';
    }
    
    $DBH = null;
    
?>

It gives me this error when I try to input some tag's into the system. It also inputs the blog post just not anything to do with the tags,


( ! ) Warning: PDOStatement::execute() expects parameter 1 to be array, string given in C:\wamp\www\Rachels\post_add.php on line 131
Call Stack
#	Time	Memory	Function	Location
1	0.0009	702216	{main}( )	..\post_add.php:0
2	0.0075	781992	PDOStatement->execute( )	..\post_add.php:131

( ! ) Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'passion' in 'field list'' in C:\wamp\www\Rachels\post_add.php on line 115
( ! ) PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'passion' in 'field list' in C:\wamp\www\Rachels\post_add.php on line 115
Call Stack
#	Time	Memory	Function	Location
1	0.0009	702216	{main}( )	..\post_add.php:0
2	0.0082	782552	PDO->prepare( )	..\post_add.php:115

Just let me know where to look to change it. 

Link to comment
Share on other sites

You need to re-read the PDO manual.

 

 

 
notes-reject.gif Description
public bool PDOStatement::execute ([ array $input_parameters ] )

Execute the prepared statement. If the prepared statement included parameter markers, you must either:

  • call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

  • or pass an array of input-only parameter values

 

Link to comment
Share on other sites

Alright so when I try and run it after rewriting it with the correct PDO statements hahaha I get an 

 

 

 Notice: Undefined offset: 2 in C:\wamp\www\Rachels\post_add.php on line 126

 

For 2, 4 and 6 and I do not understand how it is not defined. I tried looking up the error but it is saying that it means the same as it just not being assigned. Here is the new code 

<?php
//If the post is not empty, continue
    if(!empty($_POST)) {
        include 'includes.php';
        
        $sql = 'INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (?,?,?,?)';
        $params = array($_POST['title'], $_POST['post'], $_POST['author'], time());
        $poststmt = $DBH->prepare($sql);
        $poststmt->execute($params);
         
        //Grab the new blog ID
        $blogPostId = $DBH->lastInsertId();
           
        //Grab the tags and compare
        if(!empty($_POST['tags'])){
            //Select the tags from the database and store an id if found
            $sqlTag = 'SELECT id FROM tags WHERE name= ?';
            $tagstmt = $DBH->prepare($sqlTag);
            $tagString = str_replace(' ', '', $_POST['tags']);
            $tags=array();
                
            //Take the tag's from the form input and explode them into an array after each comma
            foreach (explode(',', $tagString) as $tag){
                $tagstmt->execute(array($tag));
                $tagId = $tagstmt->fetch(PDO::FETCH_COLUMN,0);
                $tagstmt->closeCursor();
   
                if (!$tagId){
                    $taginputsql = 'INSERT INTO tags SET name = ?';
                    $tagName = $tag;
                    $inserttagstmt = $DBH->prepare($taginputsql);
                    $inserttagstmt->execute(array(':name' => $tagName));
                        
                    $newTagIds = $DBH->lastInsertId();
                    $tags[] = $newTagIds;
                    $tagPostition = count($tags);
                        
                    //Insert tag and blog tag id into the blog_post_tags table
                    $blogTagNewInsert = 'INSERT INTO blog_post_tags (blog_post_id, tag_id) VALUES (?, ?)';
                    $blogTagNewstmt = $DBH->prepare($blogTagNewInsert);
                    $blogTagNewstmt->execute(array(':blog_post_id' => $blogPostId, ':tag_id' => $tags[$tagPostition]));
                    }
                 else {
                    //If ID found insert into blog post
                    $blogTagInsert = 'INSERT INTO blog_post_tags (blog_post_id , tag_id) VALUES (?, ?)';
                    $blogTagstmt = $DBH->prepare($blogTagInsert);
                    $blogTagstmt->execute(array(':blog_post_id' => $blogPostId, ':tag_id' => $tagId));
                }
            $tags[] = $tagId;
            }
        }
     echo 'Entry posted. <a href="post_view.php?id='. $blogPostId .'">View</a>';
    }
    else {
        echo 'No Post';
    }
    
    $DBH = null;
    
?>

Also it is always displaying the "No Post" as soon as a start the page, how would I get it to only show that when someone presses the "submit" button.

Link to comment
Share on other sites

Your query text is using ? placeholders, but your parameters array is using :name placeholders. You can't mix-n-match the two, you have to use one or the other in both places. Either change your queries to use the same :name placeholders, or remove them from your array and use just a simple numerically indexed array.

Link to comment
Share on other sites

Not sure what $blogTagParams has in it, but:

 

$blogTagInsert = 'INSERT INTO blog_post_tags (blog_post_id , tag_id) VALUES (?, ?)';
$blogTagParams = array($blogPostId, $tagId)
$blogTagstmt = $DBH->prepare($blogTagInsert);
$blogTagstmt->execute(array($blogPostId,$tagId));
Link to comment
Share on other sites

I think maybe the first one, with the variables making a little more sense is better?

//Insert tag and blog tag id into the blog_post_tags table
$query = 'INSERT INTO blog_post_tags (blog_post_id, tag_id) VALUES (:blog_post_tags, :tag_id)';
$stmt = $DBH->prepare($query);
$result = $stmt->execute(array(':blog_post_id' => $blogPostId, ':tag_id' => $tags[$tagPostition]));

but this one would surfice:

//Insert tag and blog tag id into the blog_post_tags table
$blogTagNewInsert = 'INSERT INTO blog_post_tags (blog_post_id, tag_id) VALUES (:blog_post_tags, :tag_id)';
$blogTagNewstmt = $DBH->prepare($blogTagNewInsert);
$result = $blogTagNewstmt->execute(array(':blog_post_id' => $blogPostId, ':tag_id' => $tags[$tagPostition]));
Edited by Strider64
Link to comment
Share on other sites

I want to you thank you all who helped with this so far! I have tested and found that there is only one bug I cannot fix and that is that the loop for the tag's does not seem to work. Here is the final code and once this is fixed I am done with this portion! 

 

post_add.php

<?php
//If the post is not empty, continue
    if(!empty($_POST)) {
        include 'includes.php';
        
        $sql = 'INSERT INTO blog_post (title,post,author_id,date_posted) VALUES (?,?,?,?)';
        $params = array($_POST['title'], $_POST['post'], $_POST['author'], time());
        $poststmt = $DBH->prepare($sql);
        $poststmt->execute($params);
         
        //Grab the new blog ID
        $blogPostId = $DBH->lastInsertId('blog_post');
           
        //Grab the tags and compare
        if(!empty($_POST['tags'])){
            //Select the tags from the database and store an id if found
            $sqlTag = 'SELECT id FROM tags WHERE name= ?';
            $tagstmt = $DBH->prepare($sqlTag);
            $tagString = str_replace(' ', '', $_POST['tags']);
            $tags = array();
                
            //Take the tag's from the form input and explode them into an array after each comma
            foreach (explode(',', $tagString) as $tag){
                $tagstmt->execute(array($tag));
                $tagId = $tagstmt->fetch(PDO::FETCH_COLUMN,0);
                $tagstmt->closeCursor();
   
                if (!$tagId){
                    $taginputsql = 'INSERT INTO tags SET name = ?';
                    $tagName = array($tag);
                    $inserttagstmt = $DBH->prepare($taginputsql);
                    $inserttagstmt->execute($tagName);
                        
                    $newTagIds = $DBH->lastInsertId('tags');
                    $tags[] = $newTagIds;
                    $tagPostition = count($tags);
                        
                    //Insert tag and blog tag id into the blog_post_tags table
                    $blogTagNewInsert = 'INSERT INTO blog_post_tags (blog_post_id, tag_id) VALUES (:blog_post_id, :tag_id)';
                    $blogTagNewstmt = $DBH->prepare($blogTagNewInsert);
                    $blogTagNewstmt->execute(array(':blog_post_id' => $blogPostId, ':tag_id' => $tags[$tagPostition]));
                    }
                else {
                    //If ID found insert into blog post
                    $blogTagInsert = 'INSERT INTO blog_post_tags (blog_post_id , tag_id) VALUES (:blog_post_id, :tag_id)';
                    $blogTagstmt = $DBH->prepare($blogTagInsert);
                    $blogTagstmt->execute(array(':blog_post_id' => $blogPostId, ':tag_id' => $tagId));
                }
            }
        }
     echo 'Entry posted. <a href="post_view.php?id='. $blogPostId .'">View</a>';
    }
    else {
        echo 'No Post';
    }
    
    $DBH = null;
    
?>

Its breaking down from line 122 and giving this error,


( ! ) Notice: Undefined offset: 1 in C:\wamp\www\Rachels\post_add.php on line 122
Call Stack
#	Time	Memory	Function	Location
1	0.0004	701984	{main}( )	..\post_add.php:0

( ! ) Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'tag_id' cannot be null' in C:\wamp\www\Rachels\post_add.php on line 122
( ! ) PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'tag_id' cannot be null in C:\wamp\www\Rachels\post_add.php on line 122
Call Stack
#	Time	Memory	Function	Location
1	0.0004	701984	{main}( )	..\post_add.php:0
2	0.0359	819272	PDOStatement->execute( )	..\post_add.php:122

Does this have something to do with $tags[$tagPosition]? its saying that the tag_id is undefined so I am assuming that is it.

Link to comment
Share on other sites

  • Solution

Nevermind! I figured it out! 

 

I changed this 

 if (!$tagId){
                    $taginputsql = 'INSERT INTO tags SET name = ?';
                    $tagName = array($tag);
                    $inserttagstmt = $DBH->prepare($taginputsql);
                    $inserttagstmt->execute($tagName);

To this and it work's! 

                if (!$tagId){
                    $tagInputInsert = 'INSERT INTO tags SET name = :tag_name';
                    $inserttagstmt = $DBH->prepare($tagInputInsert);
                    $inserttagstmt->execute(array(':tag_name' => $tag));    
Link to comment
Share on other sites

 

Nevermind! I figured it out! 

 

I changed this 

 if (!$tagId){
                    $taginputsql = 'INSERT INTO tags SET name = ?';
                    $tagName = array($tag);
                    $inserttagstmt = $DBH->prepare($taginputsql);
                    $inserttagstmt->execute($tagName);

To this and it work's! 

                if (!$tagId){
                    $tagInputInsert = 'INSERT INTO tags SET name = :tag_name';
                    $inserttagstmt = $DBH->prepare($tagInputInsert);
                    $inserttagstmt->execute(array(':tag_name' => $tag));    

Thanks, i actually needed that! Liked your comment. :tease-03:

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.