CHRISTOPHJPETERChristoph Peter

the setup

I am currently building a paltfrom to host my air applications on. this is plattform includes a feedback and bug tracking forum application where users can post their thoughts and problems. In order for this to be helpfull i needed to implement full text search. the forum constists of basically two tables.

• 1.) threads (title,date,author_id,forum_id,threadComment_id)
• 2.) comments (text, author, date,thread_id)

so the way this works is that a user can open up a thread , which stores a new thread and a comment in the database. the two are linked by adding the thread Id to the comment and the comments unique id to the thread. any futur comments that are posted to this thread will simply have the thread Id stored in them.  your table structure might look differnt but the general principle remains that tables are linked to each other by the unique ids of the tables rows.

full text search

the easiest way to search through text in mysql is by doing something like this

SELECT * FROM comments WHERE text LIKE %$SEARCH_QUERY%

this will give you all the comments that have a specific term in their text field. this will not help us much in our case since relevant information is spread out across two tables. I want to search the title and the text of the comment. so we will use this mySQL Statement

MATCH (field1,field2) AGAINST(query 1,query 2)

this uses a very sophisticated algorithm and ignores words like “so, with, and…”. check the mysql docs for details, but this is not the point here. We want to search across multiple tables and MATCH AGAINST only works on one table at a time. dam it I thought when I realized this. but there is a quite simple and fairly performant solution.

relevancy score

this concept makes it possible to assign a numeric value to each row of a table and then add up the linked table rows scores to a combined relevancy score that can be used to filter the search results. first you want to split up you search query. I will illustrate this on the setting I described above.

$tR=”MATCH(threads.title) AGAINST (‘$s’IN BOOLEAN MODE) “;

so first we create a score for the threads title by doing a simple match against. this will return me a numeric value. note: this is just a php string that will later be added to the final sql query.

$cR=”(SELECT SUM(MATCH(comments.text) AGAINST (‘$s’IN BOOLEAN MODE)) as icR FROM comments WHERE comments.threadId=threads.id)”;

now the magic happens. I go through the comments table and add the score of all those tables that match my current thread id. this chunk will also return me a numeric value.

$sql=”SELECT *,($tR*1.1+$cR) as R  FROM threads HAVING R>0.2 ORDER BY R DESC “;

this is not the final sql statment with everything put together. lets examine what happens:

($tR*1.1+$cR) as R

here I simply add the scores of the title and the scores of the comments of the thread to a single value R ( Relevancy), I can even weigh the scores differently by a factor. In my case I want the title to be more important than the comments.

HAVING R>0.2

by using this peace of code I will throw out all the records that have a score lower than 0.2 there by giving the user only results that are at least some what relevant to his search query.

conclusion

my example is a really simple one but it illustrates really nicely how to achieve a decent result with very little code. since the scores are calculated seperatly for each individual table I search in it can be used on way more than just two tables.

In this post I want to describe a very simple technique I use in order to read Mysql data into flash via a php-dynamically created xml file. So what do you need to get this work:

• a Mysql Database filled with Data of your choice
• a php capable webserver, if you dont want to upload everytime for testing try installing xampp as a localhost.
• Adobe Flash
• a text editor for writing the php file

Alright lets get started with the php by connection to the database. create a php file called config.php and fill it up with this content

define('MYSQL_HOST',     'localhost');
define('MYSQL_USER',     'user');
define('MYSQL_PASS',     'pass');
define('MYSQL_DATABASE', 'db');

    if(!@mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS)) {
        die("Connection could not be established");
    }
    if(!mysql_select_db(MYSQL_DATABASE)) {
        die("Error ".mysql_error());
    }
    $sql="SET NAMES 'utf8'";
	$result= mysql_query($sql) or die(mysql_error())</pre>
save the file. now create a file called data.php. There we want to have a function that gets a list of all the columns in our Mysql table and then reads and prints them line by line into a nice xml format which can then be read by flash.
<pre lang="php">include "../config.php";

global $table ;
$table ="your table"; // the name of the table you want to read
table_to_xml(view($table)); // this calls the functions

function get_fields($table){ // this function gives us an Array with all the collumns in the database
	$sql ="SHOW COLUMNS FROM ".$table;
	$result= mysql_query($sql) or die(mysql_error());
	$fields=array();
	while($row=mysql_fetch_assoc($result)){
		array_push($fields,$row);
	}
	return $fields;
}

function view($table){ // this is the actual sql call where we fetch the data from the database
	$sql="SELECT * FROM $table WHERE del != 'true'";
	$result= mysql_query($sql) or die(mysql_error());
	return $result;
}

function table_to_xml($result){ // this function will then print the data in simple xml format using properties with the fieldnames to store the values
	$fields=get_fields($table)
	echo ''; 	while($row=mysql_fetch_assoc($result)){ 			$f=0; 			echo '';	 	} 	echo '';
}

now we are almost done. create a new fla file and enter this code in the first frame.

data_xml:XML=new XML()
data_xml.ignorewhite=true
data_xml.onLoad=function(){ if(success){	 trace(this)} else{ 	trace("an error occured")} }
data_xml.load("http://localhost/yourfolder/data.php") // it is important to include the http://localhost because otherwise the php will not be

and now you are done. If you test the project you should get all your data traced. let me know if this was helpful, or if I have errors in my code.

I am working on a browser based flash UI  Module for a file system that should run aside the ftp access to my server that is sitting in my kitchen as a webstorage solution.Here is a list with some of its features:

• folder structure
• Drag and Drop
• Flash File upload
• Encrypted File Storage for security reasons
• sharable download links

Here is whats to come

• file renaming

 The project is almost finished. If you are interested in the Code, I am gladly willing to shar it.Heres a screenshot!  Requirments:

•  php
• Mysql
• Webspace + being able to set writing permissions for files via chmod