Jump to content

checking array of data against a database


imperialized
Go to solution Solved by imperialized,

Recommended Posts

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!

Link to comment
Share on other sites

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 by mac_gyver
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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();
}
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

  • Solution
        $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 by imperialized
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.