PHP programming: smilie search

[JoeP]

Inspired by minorityreport's excellent post on approaching programming, and by the fact that vm's set up a play test system and pointed me to enough documentation that I can actually see how this thing might be possible, I'm going to start discussing a sample programming task.

This won't be as clear as minorityreport's, because we don't have the solution yet. Programming always seems simpler in retrospect.

The task is to add a facility to this board to find smilies by general keywords as well as (calm down liv) the categories.

The long-term goal is to turn vm into a sought-after and highly-paid programmer. But this is a team effort. The role of master will be played by JoeP . The role of novice (soon to surpass the master) will be played by viscousmemories. The role of Laughing Buddha will be played by Farren. Other programmers make up the Chorus, and everyone else makes up the pilot user base - important, because we want to make this a killer add-on that all other vBulletin sites (OK, those with over 900 smilies ) will have to have.

Define the problem

It's sometimes hard to find the smilie you want for a specific purpose, even when you know it exists. Even if you browse all the categories you can overlook the one. You can guess but it doesn't always :work:.

Is this a real problem?

If only one person cares about this we aren't going to end up with a killer app. Are other people interested? It's obvious to me, but (a) you shouldn't just trust your own opinion and (b) I don't see anything like this requested on the vbulletin forums. I could set up a poll, but you can just reply here. My one piece of independent evidence is pescifish's elegant request:

Originally Posted by pescifish

I'm not sure if this is a request, a complaint or a plea for help due to my overall gimpiness.

But... in the vast repository of smilies, I find several on-the-lips romance kissy ones and several bump'n'grind contact ones. Is it my bad eyes and lazy visual scanning that is causing me to miss a kiss-on-the-cheek like and plain ol' friendly hugs like or ?

We have such a great selection, I'm certain they are in there somewhere, but I'm just not seeing them (I blame my vision, but it's more likely my age addled forgetfulness!)

Outline the solution

Thinking like a user, I imagined something like this: in the smilies panel beside the post/reply edit box, a clickable link 'Search' (also linked from the category popup, but we're getting ahead of ourselves).

The resulting popup page would have a input box where you can type one or more keywords - like 'cool' - and a search button. Clicking search would populate the page with all smilies that include the word 'cool' in their description (and that needs to be defined better, but hold on). Then, as usual, you click the one you want and it appears in your post.

Sounds good to me, but we haven't done anything like programming yet.

Is it viable? Does it have a snowball's chance in hell of working?

This is where I've made some progress recently. I needed access to documentation, admin access to a test system (of vbulletin because that's the system used here) and access to developers' discussion forums. (Plus some experience of knowing what kind of things to look for.)

How do these smilies work? How are they stored? Answer: the vb system replaces text matching a given text-to-replace string with a corresponding image held on the server. : D maps to images/smilies/1_grin.gif which actually appears in the html page as [ IMG]http://www.freethought-forum.com/forum/images/smilies/1_grin.gif[ /IMG] (hmm, how do I quote bbcode tags in a post?) and on screen as .

How are the text and images associated? There's a table in the mySQL database driving the whole thing called smilies. Good. Trust me.

Can we add new dialogues/pages/popups/whatever? Yes, vb allows you to add and customise everything - through things called templates. This is where we'll use php code to do all our clever stuff.

Design the solution in a bit more detail.

This is the last chunk I'm going to write today because I kind of have to sleep.

We'll create a new template that shows a page with the keyword-entry box and the 'search' button (as described above), and possibly another one for the search results. We'll write some code that queries the smilies table for the keywords the user specifies, and populates them into the results template. Oh, and we'll have to modify some post templates to link to our new page.

The search itself needs thought. For the first attempt, we can just do a search for any match anywhere in any of the fields (the filename, the replace text, or the description - that's the popup tip - BigGrin in the above case). What we really need is a new field in the table for keywords, and a way of setting up and adding useful keywords to each smilie. But that can wait.

Now it's just a small matter of programming

joe

[livius drusus]

This. Is an absolutely gorgeous OP. I love every word of it even though I will be of no help to you except as cheerleader.

I wanted to toss one issue out there for y'all to chew on: many of the smilies have meaningless names (numbers or parts of french words), from the filename to the replacements and even the description on account of all the different places they've been.

Any changes we make to the filenames or replacements will fuck up existing smilies, so I'd rather not do that; that means i have to at least fix the descriptions for phase one to work. Not a problem in the least, of course, as I will love every minute of it, but it's something to consider when you're talking about public releases.

You rock JoeP. You and vm should use this thread like a journal and then write up the story for the Articles section when you're through. God this is so going to kick ass.

[viscousmemories]

Wow, great post Joe. I think this will be a great experience.

Originally Posted by JoeP

Inspired by minorityreport's excellent post on approaching programming, and by the fact that vm's set up a play test system and pointed me to enough documentation that I can actually see how this thing might be possible, I'm going to start discussing a sample programming task.

If anyone else wants to work with us on this just let me know, there's plenty of access to the test server to go around. In the meantime, feel free to peruse the vBulletin documentation yourself.

Thinking like a user, I imagined something like this: in the smilies panel beside the post/reply edit box, a clickable link 'Search' (also linked from the category popup, but we're getting ahead of ourselves).

The resulting popup page would have a input box where you can type one or more keywords - like 'cool' - and a search button. Clicking search would populate the page with all smilies that include the word 'cool' in their description (and that needs to be defined better, but hold on). Then, as usual, you click the one you want and it appears in your post.

Sounds good to me, but we haven't done anything like programming yet.

Good ideas. Probably we can just use the "more" link that's there already, and modify the existing pop-up so it displays the category list and a search box instead of all the smilies in the first category (as it is now). That way the box would load faster, and it could serve two birds with a single piece of popcorn.

How do these smilies work? How are they stored? Answer: the vb system replaces text matching a given text-to-replace string with a corresponding image held on the server. : D maps to images/smilies/1_grin.gif which actually appears in the html page as [ IMG]http://www.freethought-forum.com/forum/images/smilies/1_grin.gif[ /IMG] (hmm, how do I quote bbcode tags in a post?) and on screen as .

There are three bbcode tags that make it easier for you to post code. They are: code, php and html.

How are the text and images associated? There's a table in the mySQL database driving the whole thing called smilies. Good. Trust me.

Can we add new dialogues/pages/popups/whatever? Yes, vb allows you to add and customise everything - through things called templates. This is where we'll use php code to do all our clever stuff.

More precisely we'll use HTML in the templates with calls to php functions in files.

We'll create a new template that shows a page with the keyword-entry box and the 'search' button (as described above), and possibly another one for the search results.

As mentioned above, we can probably modify the existing pop-up template. Not sure if we'll need any other templates or template "bits", but perhaps.

We'll write some code that queries the smilies table for the keywords the user specifies, and populates them into the results template. Oh, and we'll have to modify some post templates to link to our new page.

I know a tiny bit about writing MySQL queries, but not much. I do know that keeping queries to a minimum is key, though. Most webhost providers limit maxqueries for their clients, so people avoid hacks that add a lot of queries.

The search itself needs thought. For the first attempt, we can just do a search for any match anywhere in any of the fields (the filename, the replace text, or the description - that's the popup tip - BigGrin in the above case). What we really need is a new field in the table for keywords, and a way of setting up and adding useful keywords to each smilie. But that can wait.

Good idea. The new field would be easy enough to make too, but I suspect this is all a bit more complicated than it looks on the surface. For me anyway.

Now it's just a small matter of programming

Woohoo! Bring it on! I did conquer my very first little coding challenge by coming up with a user option to disable smilies (as I mention here) so I'm not totally clueless. The mechanism to add a user profile option is built in to the ACP, so then I just had to edit one line in a single file to check that setting before parsing the smilies. Piece of cake (despite it taking me hours to find the block of code that parses the smilies... )

I yearn for more challenge!

[Farren]

I'd like to volunteer but last time I did (on HH) I got seriously distracted by other shit and didn't get past a mockup. Right now I just got a ton of new work and am doing some hobby programming and I'd probably just volunteer then feel bad when I didn't get round to it. But I wish you guys the best of luck and will try make helpful suggestions from a programming perspective when I can.

Aside from that I suppose I'll have to live up to Joe's characterization, so I'm going to sit here, all serene like, and snicker

[JoeP]

Originally Posted by livius drusus

I wanted to toss one issue out there for y'all to chew on: many of the smilies have meaningless names (numbers or parts of french words), from the filename to the replacements and even the description on account of all the different places they've been.

Any changes we make to the filenames or replacements will fuck up existing smilies, so I'd rather not do that; that means i have to at least fix the descriptions for phase one to work. Not a problem in the least, of course, as I will love every minute of it, but it's something to consider when you're talking about public releases.

You're right, and first of all we'll just build the search system. It won't have a chance of getting all relevant smilies for a given keyword until we can add an extra keyword field to the table. Watch out for a post on this shortly.

Once we've built the tool, you will have to add the information to make it work. Art of programming #2: the data is the user's responsibility.

(You: liv, collector and guardian of the smilie hoard (I see you as a dragon, gorgeous of course, scouring the countryside for gold and treasures and sleeping on them), or a collective effort from everyone. But that will be the subject of a future digression. Possibly even a version 3 kind of thing.)

Originally Posted by livius drusus

You rock JoeP.

Originally Posted by livius drusus

You and vm should use this thread like a journal and then write up the story for the Articles section when you're through.

That sounds like work ... art of programming #3: documentation is always left for somebody else to do.

[JoeP]

Originally Posted by Farren

I'd like to volunteer but last time I did (on HH) I got seriously distracted by other shit and didn't get past a mockup. Right now I just got a ton of new work and am doing some hobby programming and I'd probably just volunteer then feel bad when I didn't get round to it. But I wish you guys the best of luck and will try make helpful suggestions from a programming perspective when I can.

Aside from that I suppose I'll have to live up to Joe's characterization, so I'm going to sit here, all serene like, and snicker

Cool beans.

Do you have experience in php and/or perl?

[JoeP]

Originally Posted by viscousmemories

There are three bbcode tags that make it easier for you to post code. They are: code, php and html.

I thought I'd tried each of these and substitutions still took place, but the php one works:

PHP Code:

This text 
:D
expands to this code
[IMG]http://www.freethought-forum.com/forum/images/smilies/1_grin.gif[/IMG] 


Although the colouring isn't right.
Of course, to be fussy, they all produce an indented box - I'd like something I can quote inline.

More precisely we'll use HTML in the templates with calls to php functions in files.

SmartypantsWell pointed out

More replies to follow...

[JoeP]

Originally Posted by viscousmemories

Good ideas. Probably we can just use the "more" link that's there already, and modify the existing pop-up so it displays the category list and a search box instead of all the smilies in the first category (as it is now). That way the box would load faster, and it could serve two birds with a single piece of popcorn.

As mentioned above, we can probably modify the existing pop-up template. Not sure if we'll need any other templates or template "bits", but perhaps.

Modifying the existing templates will work, but these templates are the "smilie category hack", not base vb. So we have two issues that matter when we get to releasing a mod to the general community: 1: will the author of that mod allow us to change his code and more importantly, include it in a new mod? And 2: do we want our smilie keyword search mod to require & depend on that mod being used? There may be a case for making it separate and independent. Or even making two versions. I think we can safely leave this till later: if we have to split it out, we can create the new templates then. For this board, I'd agree modifying the same template is the right approach.

Originally Posted by viscousmemories

I know a tiny bit about writing MySQL queries, but not much. I do know that keeping queries to a minimum is key, though. Most webhost providers limit maxqueries for their clients, so people avoid hacks that add a lot of queries.

That could be a worry. I can't see how to do this without one SQL query per search, but we'll keep an eye out for php ways around it.

joe

[JoeP]

Thoughts on searching and getting the right results

In my OP I mentioned there was some more thinking to be done on the searching itself. Here is some of that thinking.

The user will want to enter keywords and get the best match. There are two major topics here: the rules for searching, and the data that allows smilies to be found. In reverse order:

The data to match

As liv pointed out, some of the smilies don't have any useful words in their filenames. I think most have useful 'descriptions', and of course they are all in useful categories. But there's not enough to be able to find all 'fear' smilies just using the search word 'fear'. And we mustn't change the existing filenames. We could make the descriptions longer ... that might not be a bad idea ... but the best approach will be add a new field in the smilies table called 'keywords'. So the row for our old friend could be

PHP Code:

'smilieid','title','smilietext','smiliepath','imagecategoryid','displayorder','keywords'
'3','Big Grin',':D','images/smilies/1_grin.gif','1','1','smile big grin happy pleased' 


Adding fields should not mess up any code that using the existing fields, but does imply we'll have to update the admin panels that allow management of the smilie list, and that needs to be rock-solid. Hence, I want to defer this until we've got the basic search stuff in place.

Rules for searching

What fields of the table are searched? Obviously 'title' (description), probably 'smiliepath' (filename) and 'smilietext' (replacement text), plus 'keywords' if it exists. Searching multiple fields could be slower (depends on the SQL engine), but we could pile everything into the 'keywords' field (say, with an install routine that copies text from the other fields into it). At some point we may want an admin option to control which fields are searched by default.

I think an important thing on a board like this with meaningful categories set up is to search the category names too. However, they are in a different table afaik, and this in principle means a SQL join. That may or may not be an issue.

How are keywords applied to a search? The user entering one keyword like 'fear' and clicking search is trivial. More complex searches need to be interpreted: should 'fear roll' return only smilies that have both those words, or either (and vs or in boolean terms)? What about partial matches so 'roll' matches 'rolls'? In the long run, technology like regular expressions and root forms ('smile' matches 'smiling' etc) are all obvious things to consider, although not as important when searching a few thousand records as when googling a trillion web pages.

At this point in time we can pick one approach and add the extra stuff later. But, and here is the lesson grasshopper, we must build everything we do now with the likelihood of developing it further in future. One of the techniques for this is modularising the code. If we recognise that any one search may have "search type options" and "fields to search" selected in future by the user, as well as the search keywords, we can structure our code to allow for them. (Although I don't see it being a big deal in this task.)

Don't panic!

joe

(btw: are there bb codes for numbered and formatted headings in a post or an article? Or is that manual?)

[viscousmemories]

Originally Posted by JoeP

Modifying the existing templates will work, but these templates are the "smilie category hack", not base vb. So we have two issues that matter when we get to releasing a mod to the general community: 1: will the author of that mod allow us to change his code and more importantly, include it in a new mod? And 2: do we want our smilie keyword search mod to require & depend on that mod being used?

Ah, excellent point I'd overlooked.

There may be a case for making it separate and independent. Or even making two versions. I think we can safely leave this till later: if we have to split it out, we can create the new templates then. For this board, I'd agree modifying the same template is the right approach.

According to vB.org there are 30 other people using the category hack, so I'm sure both versions would be a big hit.

That could be a worry. I can't see how to do this without one SQL query per search, but we'll keep an eye out for php ways around it.

Actually I don't know this for sure, but I think people are only concerned by queries that run every time a particular page is loaded. It's probably something we don't have to worry too much about. I think.

[livius drusus]

Originally Posted by JoeP

You're right, and first of all we'll just build the search system. It won't have a chance of getting all relevant smilies for a given keyword until we can add an extra keyword field to the table. Watch out for a post on this shortly.

Oh, I'm watching.

Once we've built the tool, you will have to add the information to make it work. Art of programming #2: the data is the user's responsibility.

Color me symbiotic, my friend, because I know all about adding information. The data is not only this user's responsibility, but her fondest compulsion.

(You: liv, collector and guardian of the smilie hoard (I see you as a dragon, gorgeous of course, scouring the countryside for gold and treasures and sleeping on them), or a collective effort from everyone. But that will be the subject of a future digression. Possibly even a version 3 kind of thing.)

I think that's a lovely vision. I'm touched, really.

Originally Posted by JoeP

That sounds like work ... art of programming #3: documentation is always left for somebody else to do.

Well allow me...

[JoeP]

Originally Posted by viscousmemories

Actually I don't know this for sure, but I think people are only concerned by queries that run every time a particular page is loaded. It's probably something we don't have to worry too much about. I think.

I see there's a query run for each smilie popup in the current code anyway. I think this is a requirement we can safely mandate.

Since we're talking about it, here is the query run to fetch all smilies:

PHP Code:

"SELECT smilietext AS text, smiliepath AS path, smilie.title, 
     smilieid, imagecategory.title AS category 
FROM " . TABLE_PREFIX . "smilie AS smilie 
     LEFT JOIN " . TABLE_PREFIX . "imagecategory AS imagecategory 
     USING(imagecategoryid) 
ORDER BY imagecategory.displayorder, smilie.displayorder" 


As nice a piece of SQL as I've seen.

To select for a given keyword in the title field, all we need is this:

PHP Code:

"SELECT smilietext AS text, smiliepath AS path, smilie.title, 
     smilieid, imagecategory.title AS category 
FROM " . TABLE_PREFIX . "smilie AS smilie 
     LEFT JOIN " . TABLE_PREFIX . "imagecategory AS imagecategory 
     USING(imagecategoryid) 
WHERE smilie.title LIKE '%" . $userkeyword . "%'
ORDER BY imagecategory.displayorder, smilie.displayorder" 


in which the LIKE syntax, esp use of % as wildcard, stills needs to be checked as to how mysql does it.

To match multiple keywords and search in multiple fields, that WHERE clause just needs to get bigger. And bigger. Built by code.

[JoeP]

'kay ... if it seems like I've been posting today it's an illusion. I'm actually slogging away preparing for a workshop tomorrow where a bunch of people who won't have done any work and don't share the same vision of where we're going need to be motivated to get cracking. So I won't have much time on this until the weekend (and even then I may be watching the US Open. Liv: no tennis smilie in The Sporting Group?)

Meanwhile, this is your assignment :

• Modify the smilie popup template (on the test system of course) to add another form with the keyword input box and the search button
• Copy the smilie category function to a new smilie search function ... we could put this in misc.php or in a new php file to isolate it for testing; not sure if that matters
• Set the form action to the smilie search function

I don't think you'll have any problem with this!

I'm thinking the $new_smilies content could be exactly as is now. We just fill it on a different basis with the new function.

Then ... make sure the smilie search function can read the user input ($userkeyword in my sql sample above). At least write this to a test message on the resulting page!

Then we are in SQL land, and slapping in the sample above will do something. Possibly involving the total annihilation of our universe and its replacement with something even more bizarre and inexplicable, but hopefully more useful.

joe

[livius drusus]

Look again, babeh.

[JoeP]

That's more like it (I must have overlooked that one ). 'Course, that's not just a smilie, it's close to being a full-screen netcam. Or a SimCity tile.

I also want one which is a regular smilie that spins round revealing tennis ball seams on the back ... a picture would be worth 13 words here ...

And one where the ball being hit between two racquets is a face (most likely not smiling).

And one ... more ideas bubbling up here

[JoeP]

On the test board installation, a search query has been launched, and returned safely to Earth with matching smilies intact. The Eagle has landed.

There are several things that have to be sorted out before we can consider loading it on this board, but they aren't major things. It's just that I'm going to bed now!

This is going to be good, though I say it myself.

[livius drusus]

I'll be damned, Joe. That's just crazy fast work. I guess I need to get cracking on revamping the descriptions.

[Goliath]

Hey guys, while we're still looking for smilies to enter in the show, do you think we could sign on Ed MacMahon?

"Hahaha...YESS!!"

[viscousmemories]

Originally Posted by JoeP

There are several things that have to be sorted out before we can consider loading it on this board, but they aren't major things. It's just that I'm going to bed now!

I do believe I've nailed a couple of those bugs.

This is going to be good, though I say it myself.

It's already good, Master.

[JoeP]

Originally Posted by livius drusus

I'll be damned, Joe. That's just crazy fast work. I guess I need to get cracking on revamping the descriptions.

The time-consuming stuff was getting to understand how the vbulletin system is put together, how templates work, and what the smilie table and getsmilies function do. Plus a bit of learning PHP.

The actual search bit is just a few extra lines of code. I'll knock up a progress report this afternoon, in this thread.

btw I thought the smilie was someone dispensing yucky medicine until I checked the title "kiss the ring"!

[Ymir's blood]

Originally Posted by viscousmemories

I do believe I've nailed a couple of those bugs.

Ewww!

[JoeP]

Originally Posted by Ymir's blood

Ewww!

Save the yuck icon for when you find out what he did with the bugs after nailing them. But this is a derail, we don't want this thread moved to "food & drink"

[viscousmemories]

Originally Posted by Ymir's blood

Ewww!

I did not have sexual relations with that

[JoeP]

vm, are you a sought-after and highly-paid programmer yet?

In other respects we have made good progress although there's still plenty of useful things to do. In my OP I said "Programming always seems simpler in retrospect." So true. The vBulletin system seemed completely confusing to me to start with, but in fact the changes have been relatively simple:

• We have not had to change any SQL. And the code doesn't issue any more SQL calls than the basic vbulletin smilie popup.
• We haven't had to change any templates (at least yet). All the pieces we need are already there.
• The outline solution I described was about right. vm's idea to combine keyword search into the category selection turned out best - there's no logic in creating a new different popup.

The solution so far consists of two key parts:

• When building the smilie popup, add some new elements to the html form part to allow entry of keywords.
• If keywords were specified, iterate through the list of smilies and include ones which match. Showing the smilies in the popup, and making them clickable to insert into posts, uses existing code and templates.

There are some extra gotchas like not displaying all 1037 smilies (and locking up the browser) if the user clicks search with no keywords. We put in an arbitrary limit of 50. (Btw, I think there were around 900 smilies when I first asked if this feature existed. 2000 smilies by the end of the month? )

The short-term outstanding task is to allow multiple keywords. Currently all text entered is treated as a single string. This is just a matter of some more coding in iteration test - fiddly but not difficult.

The longer-term outstanding problem is to consider an additional "keywords" field in the underlying table. Adding the field is easy, searching it is easy, but being able to add data to this field (liv!) may means changes to a number of admin control panel templates and functions.

joe

[viscousmemories]

I don't think anyone's seeking me and I'm far from highly paid, but I've enjoyed watching you write this hack. Btw I've taken your advice and implemented it here in the wild for our guinea pigs members to beta test it.