Topic: hashtagsAll Posts

closeCursor()

I haven't been using #PDO and #MySQL for a very long time, so things are bound to pop up from time to time that confuse me a little. While developing the system for storing #hashtags in a table, I tried my hand at using a MySQL variable for the first time. In hindsight, it looks as though it wasn't necessary, but it's good practice anyway.

So, I wrote a statement that looked like this:
$stmt = $dbh->prepare('
INSERT INTO blog_posts (title, safe_title, body, created, author) VALUES (?, ?, ?, NOW(), ?);
SELECT LAST_INSERT_ID() INTO @post_id;
'.$hashtags['tag_insert'].';
');

$hashtags['tag_insert'] is another insert statement generated by the function that parses for tags in the post. It uses the @post_id variable. So, I'm not actually selecting anything in this statement, other than into the variable. But, trying to get the LAST_INSERT_ID() from this statement, either by adding an additional line to SELECT it, or by SELECTing it with another statement, does not work.

If you SELECT it with the same statement, fetch() doesn't return anything, but if you create a new statement, you get an error saying you "Cannot execute queries while other unbuffered queries are active" and that you should call fetchAll(). But there's nothing to fetch, so it doesn't work. So we need to deactivate that query some other way.

The solution is to use $stmt->closeCursor();. Whatever PDO thinks you might want to do with the statement, it just drops it. Now I can create another statement for the LAST_INSERT_ID(). And it still remembers the value of the variable @post_id, so I can just use that.

Here's a link to the Stackoverflow question that I read to solve my problem.

Weird. But I imagine it only seems that way because there's some insight into MySQL that I'm currently missing.

Posted 30 Jan 2019 by Brian

Hash Tags

Here they are! #Hashtags. Most blogs have a system for tagging posts with various tags or categories to help with searching for things, but I really like the tags to be built into the body of the post like many social media. It makes the information more cohesive or something. But I also like not having to put a separate box on the new post form for tags. And the regex to find them in the body is simple enough. Although I did get a bit of help from a guy on the freenode regex channel. Maybe I'll make a post about how it works. #NewFeature

EDIT: Hehe, need to add the table for tags. :P
EDIT: Fixed.

Posted 25 Jan 2019 by Brian

About

You found me! I'm Brian Bogedin. This is my website and blog. Obviously. Anyway, I'm a full stack web developer and game programmer operating in Southeast Michigan. I do freelance, too, so feel free to contact me with business inquiries. Thanks for stopping by!

Ping me: brian@bogedinbr.com

Projects

This blog!

I'm making this blog from scratch. No CMS, frameworks or libraries! Just MySQL, PHP, JS, HTML and CSS. Is it necessary to create a blog from scratch? No, but it is great for learning, and customization. Does your blog have hashtags?

DrawingWiffWaffles.com

Website for a youtube channel that creates instructional and entertainment videos about illustration. I built the site and manage giveaways there from time to time. Currently working on a miniature custom CMS so that the site owner can administer the giveaways without my assistance.

SECO Tools

I do some work here and there for a company that sells mill tooling. Most recently built a batch processor for their tool converter. Basically, it takes a bunch of competitor product numbers and returns the information on tools offered by SECO with similar specs. Still needs some user friendliness enhancement, but it's currently only for internal use.

Geospin

This is a small couch multiplayer game made in gamemaker for a game jam I organized with some friends. Up to 6 players use controllers or the keyboard to fire rockets on the side of their city to rotate a planet and avoid incoming missile. The trouble is, everyone else is trying to rotate the same planet! Download it on itch.io!

Showpony

Mulitimedia engine being developed by my friend, Josh Powlison. Plays audio, video, comics, text, and kinetic novels. Cool stuff. I'm helping out a tiny bit. See the demo here.