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.

for a recent project I had to figure out how many days are between a given start and end date. My first very poor attempt was to split the dates and then get the monthes and then add up the days for each month. Very poor on performance.

The easy solution:

most modern programming languages offer Dates Objects that can handle timestamps, which makes our calculation very easy.

(Timestamp(endDate)-TimeStamp(startDate)) / 3600 / 24 = days

this will compute the amount of seconds between the start and end date, then divide by 3600 gives you get hours, divide by 24 and you have the number of days. This is especially nice since it automatically considers 29th of february.

note: if you use actionscript you have to again divide by 1000 since its timestamps are in milli seconds.

actionscript 3 screenshot

Well in general it is impossible to use them both. So http://www.yourdomain.com/yourpage.php?get=fubar#anchor1 is not going to work in most browsers, so we want to forget about that. I solved this problem with a little javascript workaround and an additional GET varibale. So step one is that your links would have to include a get variable “?a=YourAnchor”. Now we dont use the usual anchor but place a div with the id YourAnchor at the position we want the page to jump to. I use some javascript that is called when the page loads and the looks up the div with the id YourAnchor and scrolls the page to its position.

</pre>
<div id="YourAnchor"></div>
onload="go_to('<?php echo $_GET['a']; ?>')"

//in order to call the function simply place the following code in your body tag
<pre>

note that this is quite insecure though.  You should not just echo a $_GET variabel without checking since it can easily abused. I just used it to demonstrate the workaround.

</pre>
function go_to(a){

var targ = document.getElementById(a).offsetTop; // we get the y position of our anchor div

window.scrollTo(0,targ);	// let the page scroll to the y position
 }
<pre>

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 created this website during my internship in kiew for the technology company Akutron. its nothing really special, just some basic html css and a little php for the different languages, although the translations never got done. www.akutron.com

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