Building a Blog - Tag comparison issue

[travisco87]

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! 

[kicken]

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

[travisco87]

I will rewrite it and also put those into functions! Thanks for the help! 

[travisco87]

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 September 17, 2013 by travisco87

[travisco87]

I was one letter off in my database name so I solved that issue. I am still getting a 

 

 

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

 

I am thinking I am not putting the $tagId into a valid array so that I can use it? 

[kicken]

 

                        $blogTagInsert = 'INSERT INTO blog_post_tags SET blog_post_id = ' . $blogPostId . ' tag_id = ' $tagId;

 

You're missing a . in there.

[travisco87]

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() . ')';

?

[kicken]

Placing a %s in your query is not the proper way to indicate where a value should be substituted at. [Re-]read the manual on PDO::prepare and [Re-]read my example code above.

Edited September 17, 2013 by kicken

[travisco87]

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. 

[jcbones]

You need to re-read the PDO manual.

 

 

 

 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

 

[travisco87]

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.

[kicken]

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.

[travisco87]

SO like this

$blogTagInsert = 'INSERT INTO blog_post_tags (blog_post_id , tag_id) VALUES (?, ?)';
                    $blogTagParams = array($blogPostId, $tagId)
                    $blogTagstmt = $DBH->prepare($blogTagInsert);
                    $blogTagstmt->execute($blogTagParams);

[jcbones]

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

[Strider64]

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 September 18, 2013 by Strider64

[travisco87]

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.

[travisco87]

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

[moja]

 

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.