i want to store jokes, with rating and authors’ name/URL.
It contains
- the JOKE where i stored as TEXT data type:–
joke TEXT NOT NULL default ''
- author name or original URL where i found the joke:–
from_who VARCHAR(255) default NULL,
-
rating of the joke from 1 to 4, hence INT data type:–
i) i need total number of visitors that rate the joke
total_visitor INT(20) UNSIGNED NOT NULL default 0
ii) total rating(A rates 2, B rates 1, C rates 4, so total rating is 2 + 1 + 4 = 7)
-
total_rating BIGINT(20) UNSIGNED NOT NULL default 0
iii) Average rating(example with above A,B,C 7/3 = 2.3333… rounded to 2)
-
ave_rating TINYINT(1) UNSIGNED NOT NULL default 0
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT
Ok, when a joke is inserted, I must check for duplicated joke,
//check if joke already there
$sql = mysql_query("SELECT joke,from_who FROM table_joke WHERE joke='$theJoke' AND from_who='$fromURL'");
if (mysql_num_rows($sql) == 0){
mysql_query("INSERT INTO table_joke VALUES('','$theJoke','$fromURL','','','')");
}
now you get the idea, putting all and create a table and i want to index these fields, ‘joke’ as TEXT and ‘ave_rating’ as TINYINT
"CREATE TABLE table_joke (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
joke TEXT NOT NULL default '',
from_who VARCHAR(255) default NULL,
total_visitor INT(20) UNSIGNED NOT NULL default 0,
total_rating BIGINT(20) UNSIGNED NOT NULL default 0,
ave_rating TINYINT(1) UNSIGNED NOT NULL default 0,
INDEX(joke,ave_rating),
PRIMARY KEY(id))TYPE=MyISAM";
but instead of installing the table, i got “BLOB/TEXT column ‘joke’ used in key specification without a key length” error message??
giving
joke TEXT(65535) NOT NULL default ''
will produce same error.
how can i index TEXT data type with Unicode support??