Commentary and insight on web development and the Internet at large written with a wry smile and a hungry look.
simple MySQL handler class - Mon Aug 31, 2009
MySQL has come a long way since its early days what with stored query support and such. That said, the usage you will find in the average web application is still going to be limited to basic INSERT, UPDATE and SELECT queries. While there are no shortage of MySQL abstraction classes, all of the ones I have come across tend to either add too much overhead or do not really make the process of retrieving data from the backend any quicker or easier. Thus the creation of this MySQL handler class.
The intent with this class is not to be pretty or completist. It's goal is to provide the core functions needed for the average web application while keeping it simple. Query results are returned as multidimensional array using associative column names. It offers a few "magic" values by default ("insert_id" and "numrows") and has some utility functions thrown in for dealing with the result set.
I do not claim this is the most beautiful MySQL class out there. I can say however that it makes life a lot easier in many situations compared to either dealing with the database directly or using a full scale database abstraction layer.
topics: MySQL, PHP, programming
filling up MySQL's fulltext search - Fri Aug 14, 2009
I pretty quickly noticed when I started using MySQL's fulltext search capability that a lot of things I was searching for seemed to be missing from results. It depends on your content of course but for example, a stock MySQL install will find its fulltext matching functions skipping over terms like "CMS" (too short) and "value" (a "stop word"). Luckily there is a simple way to keep using the nice features provided by fulltext indexes and make sure that important terms are not skipped over just by using up some more disk space.
The solution here is to keep two copies of your data - one for display purposes and a second massaged version which you search against. This second version needs to have the following done to it via a custom "searchify" method:
This searchify method also needs to be run against any search query so as to best match the massaged search data.
Here is example code showing this method in action.
If you wanted to search this blog for the text "PHP" the MySQL query would look something like this:
// example query showing how to use FULLTEXT search
SELECT
blog.id,
(MATCH(blog.data_search) AGAINST ("phpzzgghhn" IN BOOLEAN MODE)
) AS relevance
FROM blog
WHERE
(
MATCH(blog.data_search) AGAINST ("phpzzgghhn" IN BOOLEAN MODE)
)
GROUP BY blog.id
ORDER BY relevance DESC, blog.created
In the above query, the search string "php" has been padded with characters to get it around the default minimum 4 character limit. I should also note that the above query could be expanded on to add in better relevance sorting by taking advantage of MySQL's boolean fulltext search options. The obvious option is to add in extra weighting for all words matching and phrase detection (that's for another blog posting).
For reference, here is MySQL's list of stop words.
topics: MySQL, PHP, programming
Experienced PHP web developer.
$35 an hour, no job too small.
There is no replacement for integrity and experience.