Easy Fuzzy Logic with MySql – The end of “no results found”

As a web programmer I ran into the problem when running a complicated (user) search on Mysql that the results are too strict, and thus giving the well known error “no results found”. While good (although not perfect) results exist!

The problem

When a traditional search query is initiated, sql queries are being generated in the terms of:

User search: where tv_manufacturer=”sony” and tv_description =”%widescreen%” and tv_price < 1000;

A user is asking for a Sony television AND that is widescreen AND less then 1000 dollar. This will show very accurate results. But limits the opportunities when (a best matching) TV is $1050. The users would be okay with paying $50 more in real life. But our query won’t allow it. We want to have that (almost perfect match) results shown!

This query can be rewritten by replacing the AND with OR in the query, but by using OR we get inaccurate results because results will show any TV below 1000 dollar OR any Sony OR any widescreen - useless.

The good news is that we can solve this without having to ask a user the factual and nerdy: WIDESCREEN AND (SONY OR 1000 DOLLAR) – way to difficult.

The answer is in what is named ‘fuzzy logic’. Fuzzy logic is more natural and (semi-) intelligent by mathematical logarithms:

User search: a preferably Sony TV with widescreen support for more or less a 1000 dollars, I prefer less. Please.

A few specialists software company’s offer fuzzy logic software, but this is highly tailored to the specific needs of the system.
But Mysql has a solution, with a few hacks will result in accurate results.

The solution:

The solution is to be found in the “MATCH AGAINST” function of Mysql. It is a text matching system where you can add your preferences, and the query gives points to indicate the score in matching.
Very few people use this, maybe because they are disappointed that it is only matching text. But in this post I will show you how to also integrate a (in the real world less strict) demand like: less then $1000.

We do this by encoding the numbers to a word. In this case the TV price of our tv in the database will be encoded to unique words like “pricemaxthousand”, etc.

All the features of the TV are being stored in a new (text only) column named encodedsqlrow.
So we get this: encodedsqlrow = “sony widescreen pricethousandtotwothousand diagonalthirtyinch”.

With the match against function we can also search “IN BOOLEAN MODE”. This will add ‘preferences’ to every search demand (word) in our query.

The preferences you can give to a demand (word) are in the order of:
+ = Obligated
> = Important
~ = More or less important
- = Without

And last but not least, we can retrieve a score with every results. So the most accurate results can be listed at the top.

With all this together we (a user) can create a search query that will results in more natural human-like picked results.

Creating our query:


< 1000)
$encodedsearch = “>

sony +widescreen ~pricemaxthousand”;

Getting the score:

Select tv_manufacturer, MATCH (encodedsqlrow) AGAINST (’$encodedsearch’ IN BOOLEAN MODE) as score

Setting the match search:

WHERE MATCH (encodedsqlrow) AGAINST (’$encodedsearch’ IN BOOLEAN MODE) ORDER BY score DESC

Example Page – integrated:
For a dutch website I made this function so it matches all studies (1800) against the many demands of a to-be-student. Like he could say: I am searching for a study obligated in Amsterdam with more or less important in the economic field with important average workload important mostly female on a more or less important university.
Many demands, and this will result in accurate results that include studies in Amsterdam although it has mostly male students.

Have any questions or want to bash this text: email address is on the right hand side of your screen.

Note: the database column (encodedsqlrow) must have an FULLTEXT index (via phpMyAdmin the blue “T” the at ‘actions’. This will make it searchable for the MATCH AGAINST function. Else it won’t work.



Posted by

Easy AJAX inline text edit 2.0

As everybody knows, refreshing pages is so 1999. AJAX, DOM, whatever you call it makes it possible to let people edit a piece of text inline without having to use a submit button.
You say: but that ain’t new at all! I say: But all of this has been made easy to use and implement: 2.0!

Example page: inline edit (no JS knowledge needed) [source ] | Inline example: Please edit me!

How you can make it work (5 easy steps for integration)

  • Download this javascript file: InstantEdit 2.0 JS
  • Create a update file that handles the input. For example this PHP: update file
  • In your page add the javascript:
  • Optional: Set fixed vars (like hidden elements in a field post). These will be posted with the editable field so you can identify a user/session.
  • Last step: in your HTML for any editable field add a SPAN around it.

You’re done!

How it works

A small piece of javascript reads all SPAN tags, checks if it has class=“editText” and a id=. If that is true, it adds a onclick function. That onclick function will create a textfield or input (depending on the size of the editable text). Someone has the ability to edit the field. When the text field is blurred, it will read the contents, and starts a XMLHttpRequest and ‘sends’ the content + fieldname + any set vars to an update file. That file will update your database, and reply with the newly set text and the textfield will disappear again.


This script works in Internet Explorer, Firefox, Chrome, Opera and Safari.

Update hack

If you want to force a textarea over a textfield (for example to edit a piece of HTML) use class=“editText” offsetHeight=“10”.

If you want to PUSH an ID to your script I use: id=“edit_userID_$userID”. In your update script, strip the text, and keep the $userID. Et voila.

Posted by

What is going wrong with msn search

Microsoft bashing is easy… but when they say stuff like “Microsoft (search) will be better then Google within 6 months”, I say… bullsh*t.

Everybody knows this screen when entering a non-existent url:

Think one second and wonder if you ever had any relevant results? Well I’ve never had any in the past three years. How easy is it to make this function work? They just don’t care: the only way I get in a MSN network site is by this hijack, and it’s never been helpful. First impression really count… and by the way, the MSN new search results still suck: they already had their update a few months ago, so why didn’t they do it right the first time if it was so easy to beat Google?

Posted by

SERP correct domain 302 redirect website solution

A lot of talk in the SEO world on what to do with the problems that search engines have with the same site (page) having listed multiple url versions in the SERPS. For example:

  • https://yvoschaap.com
  • https://yvoschaap.com
  • https://yvoschaap.com/index.php

Could all be viewed as different pages by search engines, while it’s not. This resulted for some sites in a “duplicate content” penalty. I advise you guys to read more about this here or here.

My general solution to this is this simple and small piece of PHP. So NO htaccess stuff, or manual redirecting, etc.

$domain = "www.yvoschaap.com";
if($_SERVER['HTTP_HOST'] != $domain || preg_match("#index.php#i",$_SERVER['HTTP_HOST'])){
  header("HTTP/1.1 301 Moved Permanently");
  header("Location: http://".$domain.$_SERVER['REQUEST_URI']);

It says this: if host isn’t the “www” version OR if it is the /index.php version. Show an official “moved” page, with the right location: https://yvoschaap.com/
Just put it in the top of your index.php file, et voila. No rocket science…

Posted by

Got myself some gadgets...

Today I felt like spending some money and that brought me to buy a home cinema projector (CONTRAST 5000:1, 1100 ANSI-LUMEN, HD RES) and with that an Xbox360. And WOW, thats crazy cool.

If you bring A DVD or XBOX game you can come and watch or play on my new 3 meter screen.

And Microsoft did a great job on the Xbox. I don’t have games yet, and was hoping for build-in WiFi, but having the Xbox (with the full screen HD projector) control your media files on your computer works! And easy! No weird settings, all plug-and-play, and within a minute I am listening to some album on my PC, through the local network, via the Xbox to my stereo, controlled on the toilet remotely. Take that…

Update: Okay, I have to get back on the Xbox… somehow it has some big ass glitches: sync (video: audio) with playing DVDs is almost always wrong! Xbox live? It has never really worked with need for speed. It has no good connection, and it freezes up. And last but not least, when playing a DVD in HDTV format you get some weird stripes throughout every movie you try to play.

Posted by

CSS: Star Rater Ajax Version

So, I found this great star rater script made in css. But I missed the web 2.0 stuff. So I played around with it to make it work on a database without having to refresh any pages (but updating the database with AJAX). In this version I use it for rating a image (with unique ID = imgId).

not a beginners tutorial - and just follow the steps

1) The star image:

Star rate image (use save as..)

2) Javascript part (to do the dynamic stuff):

Javascript with AJAX module + rateImg function. (use save as..)

3) PHP part: Create a update.php file (to do the database update with the user rating):

if($_GET[‘rating’] && $_GET[‘imgId’]){
$dbh=mysql_connect ("localhost", "#######", ""#######", ") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db (""#######", ");
$imgId = $_GET['imgId']; //clean up variable from exploits (e.g. via is_numeric(), or addslashes())

//adds the rating to imgID in the database
$update = "update vote set voteNr = voteNr + 1, voteValue = voteValue + ".$_GET['rating']." WHERE imgId = '".$imgId."'";
$result = mysql_query($update);
if(mysql_affected_rows() == 0){
//creates a new row if imgID has no own row yet
$insert = "insert into vote (voteNr,voteValue,imgId) values ('1','".$_GET['rating']."','$imgId')";
$result = mysql_query($insert);
//Assume OK, return some text . current rating?
//OK return some text / current rating?

4) Mysql part: (to create a table).

        `voteNr` int(8) NOT NULL default ‘0’,
        `voteValue` int(8) NOT NULL default ‘0’,
        `imgId` varchar(100) NOT NULL default ‘’,
        UNIQUE KEY `imgId` (`imgId`)

5) CSS part
Stylesheet part (creates onmouseover stars):

Stylesheet to create stars + mouseover. (use save as..)

An example php code to retrieve the current rating from the database: $rating = getCurrenRating(‘12’);

function getCurrenRating($imgId){

$sql= "select * from tblVote WHERE imgId= '".addslashes($imgId)."' LIMIT 0, 1";

return @round($rs[voteValue] / $rs[voteNr],1);


$rating is the rounded rating taken from database.

$imgId is the unique id for this (image) item. This is used in the javascript, passed to the update.php file to update the database.

And don’t forget to include the rating.js file.

<div id="rating">
<ul class=‘star-rating’>
<li class=‘current-rating’ id=‘current-rating’ style=‘width: <? $ratingpx = $rating *25; echo $ratingpx;?>px’><!—Currently <? echo $rating ?>/5 Stars.—></li>
<li><a href="javascript:rateImg(1,’<? echo $imgId ?>’)" title=‘1 star out of 5’ class=‘one-star’>1</a></li>
<li><a href="javascript:rateImg(2,’<? echo $imgId ?>’)" title=‘2 stars out of 5’ class=‘two-stars’>2</a></li>
<li><a href="javascript:rateImg(3,’<? echo $imgId ?>’)" title=‘3 stars out of 5’ class=‘three-stars’>3</a></li>
<li><a href="javascript:rateImg(4,’<? echo $imgId ?>’)" title=‘4 stars out of 5’ class=‘four-stars’>4</a></li>
<li><a href="javascript:rateImg(5,’<? echo $imgId ?>’)" title=‘5 stars out of 5’ class=‘five-stars’>5</a></li>

Completely confused? See example here (scroll down) http://www.guidetobuy.info/product6-beamers.html.

NEW: Simple inline text edit!

Posted by

Mercedes cls

Would be a nice goal to set in 2006: mercedes cls (55amg), together with no more smoking.
Wow: fast, classy, expensive, comfortable, and very beautiful.

Posted by


Some final year stats after this - more or less - successful year. Over 17,000,000 ads were shown (yes, those are 6 zeros) resulting in around a x digit income. Kiesjestudie.nl was launched, and shows some nice growth, and more important, good feedback (with some criticism) about the site. Knowing that the head of the ministry of education knows about your site is also a fun fact. On the subject check their project out: http://www.studiekeuze123.nl/. Semi-competition is always good.

Seeing people involved and using your developed product is nice. And it is one of my top projects. Perfect in SEO, function, and content. Working on something like Institution Location made me a master in javascript, geolocating, and a more then frequent visitor of the google api help guide.

Next year I’ll have to see what to do. I will be looking for nice projects to develop (full projects from AJAX, CSS to SEO and marketing). Some contacts have already been made. My thoughts are on non-commercial projects. To do something back to the ‘world’. In a quick brainstorm it would be something like a platform where people who have voluntary projects to advertise/market it to a bigger audience through this platform. (creating global/local awareness about something we don’t see daily).

So on to 2006, let it be good for all. I’ll be spending some of the ‘click-money’ in the mountains of Swiss next week.

For the record, I also managed to get some ECTS in univeristy.

Wow, I should learn how to write. This is painful for the brains to read

Posted by

1 2 3 4 5