imperialized Posted April 30, 2015 Share Posted April 30, 2015 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, tag3I 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! Quote Link to comment Share on other sites More sharing options...
requinix Posted April 30, 2015 Share Posted April 30, 2015 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. Quote Link to comment Share on other sites More sharing options...
imperialized Posted April 30, 2015 Author Share Posted April 30, 2015 How would I get the IDs for the inserted tags? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 30, 2015 Share Posted April 30, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
imperialized Posted April 30, 2015 Author Share Posted April 30, 2015 Ok, I understand what you mean now, Thanks Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 30, 2015 Share Posted April 30, 2015 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 Quote Link to comment Share on other sites More sharing options...
imperialized Posted April 30, 2015 Author Share Posted April 30, 2015 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 30, 2015 Share Posted April 30, 2015 $param++ That returns the value then increments (post-increment). Either use ++$param or start with a value of 1 instead of zero. Quote Link to comment Share on other sites More sharing options...
imperialized Posted April 30, 2015 Author Share Posted April 30, 2015 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted April 30, 2015 Share Posted April 30, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
imperialized Posted April 30, 2015 Author Share Posted April 30, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted April 30, 2015 Share Posted April 30, 2015 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(); } Quote Link to comment Share on other sites More sharing options...
imperialized Posted April 30, 2015 Author Share Posted April 30, 2015 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. Quote Link to comment Share on other sites More sharing options...
Solution imperialized Posted April 30, 2015 Author Solution Share Posted April 30, 2015 (edited) $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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.