i have a problem for aplication of apriori algorithm in datamining..my problem is how to make an additional field in database?
for example: I have some data like shown below:
-------------------------------------
id_session | id_hyperlink|
------------------------------------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
2 | 1
2 | 2
2 | 3
2 | 4
2 | 5
3 | 1
3 | 2
3 | 3
3 | 4
3 | 5
-----------------------------
first, we must count the number for each id_hyperlink as new candidat for next tabel with a condition that the id_hyperlink which filled minimum support can be a new candidat....and after that we must loop the new candidates of id_hyperlink which have minimum suport to find a new candidates again....but here, i just can find untill the second iteration...
so, how to add new field (automatically by using database or php code by looping the data) of the tabel that save the value of the next new candidate?
here's the source code i've write for this time
//THE DATABASE:
CREATE TABLE `session` (
`id` int(11) NOT NULL auto_increment,
`id_session` int(11) NOT NULL,
`id_hyperlink` int(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=76
//--------------------------------------------------------------------------------------------------
CREATE TABLE `apriori` (
`id` int(11) NOT NULL auto_increment,
`id_link1` int(11) NOT NULL,
`supp` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=10 ;
//-------------------------------------------------------------------------------
CREATE TABLE `temp` (
`id` int(11) NOT NULL auto_increment,
`id_link1` int(11) NOT NULL,
`id_link2` int(10) NOT NULL,
`num` int(10) NOT NULL,
`supp` float default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=25 ;
================================================== =
//THE PHP CODE//
$sql= 'SELECT MAX(id_session) as id_session FROM session';
$hasil = mysql_query ($sql);
$row = mysql_fetch_array($hasil);
$id = $row['id_session'];
$supp = 0.5;
$jumlah = $supp * $id;
//----------------------------------------------------------------------
//iterasi 1
$sql1 = "insert into apriori (id_link1, supp)
SELECT id_hyperlink as id_hyp, count(*) as banyak
FROM `session` group by id_hyperlink having banyak >= $supp";
$hasil1 = mysql_query ($sql1);
//------------------------------------------------------------------------
//iterasi2
$sql2 ="
INSERT INTO temp(id_link1, id_link2, num, supp)
SELECT a1.id_link1 AS id1, a2.id_link1 AS id2, count(*) AS jumlah, (count(*) / $id) AS support
FROM apriori a1, apriori a2, session s1, session s2
WHERE
a1.id_link1 < a2.id_link1
AND (a1.id_link1, a2.id_link1)
IN (
SELECT s1.id_hyperlink, s2.id_hyperlink
FROM session s1, session s2
WHERE s1.id_session = s2.id_session AND s1.id_hyperlink <> s2.id_hyperlink
GROUP BY s1.id_hyperlink, s2.id_hyperlink
)
AND s1.id_hyperlink = a1.id_link1
AND s2.id_hyperlink = a2.id_link1
AND s1.id_session = s2.id_session
GROUP BY id1, id2
HAVING jumlah >= $jumlah
";
$hasil2 = mysql_query($sql2);
$num = 1;
?>
//-------------------------------------------------------------------------------------------------------
/*how can I add-automatically the field of table temp above because there will be still new candidates? for example adding 'id_link3' in the table 'temp' cz for the second iteration still there are a couple candidates....but for the 3rd iteration there will b 3 candidates */