checking array of data against a database

[imperialized]

Ok, I will try to clarify this as best as I can. 

 

I am creating a blog script. Basically, what I am trying to do is check the tags.

The form has a tag input (comma delimited)

ex: tag1, tag2, tag3

I explode the data into an array then check each one to see if it is in the database. If it is, get the ID and add it to the ID array.
If it isn't, add it to the table and get the last inserted ID. 

This code: 

$this->blogTags = explode(',', $blogTags);
        
        foreach($this->blogTags as $tag){
            //Check if the tag is in the database already, if so, get the ID
            $tag_query = $this->db_connect->prepare("SELECT * FROM blogTags WHERE tag = :tag LIMIT 1");
            $tag_query->bindValue(':tag', $tag, PDO::PARAM_STR);
            $tag_query->execute();
            if($tag_query->rowCount() > 0){
                $result = $tag_query->fetch(PDO::FETCH_ASSOC);
                $this->blogTagIds[] = $result['id']; //Tag already exists, add the ID to our array
            } else {
                //Tag doesn't exist
                $add_tag_query = $this->db_connect->prepare("INSERT INTO blogTags (tag) VALUES (:tag)");
                $add_tag_query->bindValue(':tag', $tag, PDO::PARAM_STR);
                $add_tag_query->execute();
                $this->blogTagIds[] = $this->db_connect->lastInsertId();
            }
        }

This code works. I, however, am asking if that is the proper way to achieve the result. I'm sure that there is a better way. Any help would be appreciated. 

 

Thanks in advance!

[requinix]

There is an easier way: make sure the tag column is unique and do an INSERT IGNORE with all the tags at once.

INSERT IGNORE INTO blogTags (tag) VALUES ("tag1", "tag2", "tag3")

which will be easier to do without prepared statements.

[imperialized]

How would I get the IDs for the inserted tags?

[mac_gyver]

you don't need to select data first, before storing unique values. in fact, there is a race condition with concurrent access where you could have two (or more) instances of your script trying to insert the same tag, and the one(s) who run the insert query last will generate an error because the value was already inserted by the insert query that ran first.

 

your tag column should be defined as a unique index. then, you should just form and run one multi-value INSERT query with the IGNORE keyword in it.

 

then, just run one select query using a WHERE tag IN (....) term to retrieve all the ids at once.

Edited April 30, 2015 by mac_gyver

[imperialized]

Ok, I understand what you mean now, Thanks

[mac_gyver]

some sample code (untested) - 

$this->blogTags = array_filter(array_map('trim',explode(',', $blogTags)),'strlen'); // explode, trim, and remove empty strings
 
// insert any new tags
$holders = array_fill(0,count($this->blogTags),'?');
$query = "INSERT IGNORE INTO blogTags (tag) VALUES (".implode('),(',$holders).")";
$stmt = $this->db_connect->prepare($query);
$parm = 1;
foreach($this->blogTags as $tag){
    $stmt->bindValue($parm++, $tag, PDO::PARAM_STR);
}
$stmt->execute();
 
// retrieve ids
$query = "SELECT id FROM blogTags WHERE tag IN (".implode(',',$holders).")";
$stmt = $this->db_connect->prepare($query);
$parm = 1;
foreach($this->blogTags as $tag){
    $stmt->bindValue($parm++, $tag, PDO::PARAM_STR);
}
$stmt->execute();
 
$this->blogTagIds = $stmt->fetchAll(PDO::FETCH_COLUMN); // fetch just the ids as an array

[imperialized]

Thanks for the replies. I was working to try to work with the code you gave me. At first I couldn't understand how it worked, based on the idea of replacing the '?' marks, but further research showed me that they are numbered. So each ? mark was correspondent to the the order that it was. So 3 (?) = 1 2 3 as far as the binding of the parameters went. After further toying around with it, I am running into problems.

 

This Code: 

    $tagHolder = array();
    
    if($this->db_connect()){
        $this->blogTags = array_filter(array_map('trim', explode(',', $blogTags)), 'strlen'); //Remove Empty Values, Trim Whitespace;
        
            $tagHolder = array_fill(0, count($this->blogTags), '?');
            
            print "holderCount: ".count($tagHolder);
            print "<br />tagCount ".count($this->blogTags);
            print "<br /><br />";
            //Insert any NEW tags
            $query = "INSERT IGNORE INTO blogTags (tag) VALUES (".implode('),(',$tagHolder).")";
            $stmt = $this->db_connect->prepare($query);
            $param = 0;
            foreach($this->blogTags as $tag){
                $stmt->bindValue($param++, $tag, PDO::PARAM_STR);
                print "param: $param<br /> tag: $tag<br />";
            }
            
            $stmt->execute();
            exit;

Produces this result (when 5 'tags' were inputted): 

holderCount: 5
tagCount 5


Warning: PDOStatement::bindValue(): SQLSTATE[HY093]: Invalid parameter number: Columns/Parameters are 1-based in /home/imperialized/public_html/new_login/createBlogPost.php on line 52
param: 1
tag: abc
param: 2
tag: def
param: 3
tag: ghi
param: 4
tag: jkl
param: 5
tag: mno

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in /home/imperialized/public_html/new_login/createBlogPost.php on line 56

I tried playing with it before coming back here to ask for help, but it doesn't seem to make sense. It has 5 placeholders for values, runs the bindValue loop 5 times, but still says there are too many/not enough. 

Thanks again

[Barand]

 

 

$param++

 

That returns the value then increments (post-increment).

 

Either use ++$param or start with a value of 1 instead of zero.

[imperialized]

Still returns the same error. 

Changing param to 1 and leaving it as is.

Or leaving param at 0 and changing it to ++$param does the same thing.

[mac_gyver]

i recommend that you start over and study the code i posted. the code you have has all kinds of variables mixed up and is nothing like what i posted.

Edited April 30, 2015 by mac_gyver

[imperialized]

No, it works. My ftp program wasn't working properly to the changes. All I did was change some variable names (when I retyped it). 

 

Changing it to ++$param was the problem.

Edited April 30, 2015 by imperialized

[Barand]

Not sure prepare and multiple insert syntax work together.

Try

$query = "INSERT IGNORE INTO blogTags (tag) VALUES (?)"; 
$stmt = $this->db_connect->prepare($query);
foreach($this->blogTags as $tag){
    $stmt->bindValue(1, $tag, PDO::PARAM_STR);
    print "param: $param<br /> tag: $tag<br />";
    $stmt->execute();
}

[imperialized]

 

Not sure prepare and multiple insert syntax work together.

Try

$query = "INSERT IGNORE INTO blogTags (tag) VALUES (?)"; 
$stmt = $this->db_connect->prepare($query);
foreach($this->blogTags as $tag){
    $stmt->bindValue(1, $tag, PDO::PARAM_STR);
    print "param: $param<br /> tag: $tag<br />";
    $stmt->execute();
}

I didn't test this as the other code appears to be working. Now I am just playing with the other half of it, haha. Pulling the IDs and putting them in the array.

[imperialized]

        $tagHolder = array();
        $this->blogTags = array_filter(array_map('trim', explode(',', $blogTags)), 'strlen'); //Remove Empty Values, Trim Whitespace;
            //Insert New IDs
            $tagHolder = array_fill(0, count($this->blogTags), '?');
            $query = "INSERT IGNORE INTO blogTags (tag) VALUES (".implode('),(',$tagHolder).")";
            $stmt = $this->db_connect->prepare($query);
            
            $param = 0;
            foreach($this->blogTags as $tag){
                $stmt->bindValue(++$param, $tag, PDO::PARAM_STR);
            }
            $stmt->execute();
            
        
            //retrieve all tag ids
            $query = "SELECT id FROM blogTags WHERE tag IN (".implode(',',$tagHolder).")";
            $stmt = $this->db_connect->prepare($query);
            $param = 0;
            foreach($this->blogTags as $tag){
                $stmt->bindValue(++$param, $tag, PDO::PARAM_STR);
            }
            $stmt->execute();
 
            $this->blogTagIds = $stmt->fetchAll(PDO::FETCH_COLUMN); // fetch just the ids as an array

Final working code. Thanks Mac_Gyver and Barand 

Edited April 30, 2015 by imperialized