June 16, 2010, 6:59 pm
While working with tagging application my favourite is freetag by Gordon Luk. It is a comprehensive open source tagging and folksonomy code in php. The source code is also hosted in Google Code. The best feature of it is you can use this code with little modification to fit your requirement. First time I used it in advaitaashrama.org for their book store application. Currently I am using it in another website which is under development.
I am having an issue of compatibility with this code and I am writting this post for it. If you look at the code you will find that it uses the ADODB Library for database operations. In my case I am happy with PEAR::DB. Now for me to use this code I need to use 2 different database component as PEAR::DB and ADODB Library. It seems useless to me. So I decided to make the code compatiable with PEAR::DB. While working upon the compatibility modification I also made 3 changes as
- I have written a function as show_debug_text() which is a replacement of debug_text().
- I have removed the silly_list() function from my code as it was declared as deprecated.
- I have renamed the main class file from freetag.class.php to freetag.db.class.php
Here is the sample code for use this
-
<php?
-
require_once("/path-to/freetag.db.class.php");
-
$DbObj = DB::connect($dsn, $options); //this is the database connection object using PEAR::DB
-
-
‘table_prefix’ => ‘mytags’,
-
);
-
$TagObj = new freetag($DbObj, $OptionArray);
-
// Use this object to call tag related functions .
-
?>
Here is the code modified by me.
If you use this code please let me know if you have any problem.
April 30, 2009, 9:21 am
Here are some tricky Sql statements that can be used in some specific cases. I figured them out in certain cases where I needed to do data manipulation directly on the database instead of writing script to accomplish them. I will describe them in case study style.
Case 1:
In one of my project there is a user table containing fields like userId, UserName, UserLoginID, Password etc. The UserLoginID field is basically the email address. Almost 95% of the email addresses belongs to a specific domain that is the domain of the project. Let’s say testproject.com. The table contains approx 12,000 records. In that project client decided to shift their main tld from .com to .org like testproject.com to testproject.org. Now they asked me to change the all the users’ email from .com to .org. That means user1@testproject.com to user1@testproject.org I needed to do this job within a shortest span of time. So I decided to run a sql command directly in the database to do the modification. A simple UPDATE sql won’t work in that situation as I needed to change some part of a field and also update statement shouldn’t affect those rows which do not contain testproject.com. So I tried with different types of update sql to do the job but finally derived the following one :-
-
UPDATE tblUser SET UserLoginID = REPLACE(UserLoginID, ‘ testproject.org’, ‘ testproject.com’);
The beauty of this statement lies in the usage of replace function. This replace function will synchronously select records having ‘testproject.com’ implementing functionality of the Where clause and generate the value to be set for the field UserLoginID as @testproject.org
Case 2:
This case was relatively easy. I need to find out unique entries from a table. It was not a hard job for me but still I googled a bit for this purpose. Now after some study I come to a position where I could do that using two different sql statements. They are :-
-
SELECT product_code FROM tblproductdetails GROUP BY product_code;
and
-
SELECT DISTINCT product_code FROM tblproductdetails;
While looking at the results I found both of them produces same result but the only differnce is Select DISTINCT does not sort the result set like GROUP BY does. So I wasn’t sure which one should used. SELECT DISTINCT vs GROUP BY – udbug | Google Groups helped me to chose the correct statement as Select DISTINCT. So I used the following :-
-
SELECT DISTINCT product_code FROM tblproductdetails ORDER BY product_code
Case 3:
This is also related with the earlier case of unique entries. This time I need to find out the ids which have duplicate entries. Almost the opposite job than the previous one. Well this statement also uses distinct but along with Group by clause gives the entries which have duplicates in the table. Here is the statement.
-
SELECT DISTINCT regn_id FROM tblstudentdetails GROUP BY regn_id HAVING count(regn_id) > 1;
This statement is very useful to find out entries which have duplicates in the tables.
Somebody asked me earlier how do I learn php and database? My answer was – learn the basic, find some real-life project to work for, take the challenge to complete it. You would face lots and lots problem while doing that. Don’t try to get rid of them but try to solve them. When you would finish the job you will understand the difference in you in terms of practical knowledge. I hope you all agree with me. 
