I use something like this in my e-commerce for degrees of my category of
products. I think it´s the same thing you want.
I get everything to an array and then use it to work.
By this follow function, i get all options for my select field, it´s all in
portuguese but i think it can help you.
function select_categorias(){
global $con;
$sql = "SELECT
*
FROM
categoria
ORDER BY
cat_id, nome";
$con->Query($sql);
$counter = $con->count;
$categorias=array();
for($z=0;$z<$con->count;$z++){
$con->Seek($z);
list($id_cat, $cat_id, $nome_cat, $icone_cat) = $con->result;
$categorias[$id_cat]=array("id_cat" => $id_cat, "cat_id" => $cat_id,
"nome_cat" => $nome_cat, "icone_cat" => $icone_cat, "familia" => $id_cat, );
$continua=$cat_id!=null && $cat_id>0;
if ($continua) {
$qual=$cat_id;
$categorias[$id_cat]["indice"]="";
while ($continua) {
$categorias[$id_cat]["indice"]=$categorias[$qual]["nome_cat"].$categorias[$id_cat]["indice"];
$continua=$categorias[$qual]["cat_id"]!=null &&
$categorias[$qual]["cat_id"]>0;
$tem_pai=$cat_id!=null && $cat_id>0;
if ($tem_pai) $pai=$cat_id;
while ($tem_pai) {
if (!strpos($categorias[$pai]["familia"],",
".$categorias[$id_cat]["id_cat"]))
$categorias[$pai]["familia"].=", ".$categorias[$id_cat]["id_cat"];
$tem_pai=$categorias[$pai]["cat_id"]!=null &&
$categorias[$pai]["cat_id"]>0;
$pai=$categorias[$pai]["cat_id"];
}
if ($continua) {
$qual=$categorias[$qual]["cat_id"];
}
}
}
}
reset($categorias);
$linhas=array();
foreach ($categorias as $categoria) {
$linhas[$categoria["indice"].$categoria["nome_cat"]]=array("id_cat" =>
$categoria["id_cat"], "nome_cat" => $categoria["nome_cat"], "icone_cat" =>
$categoria["icone_cat"], "indice" => $categoria["indice"], "familia" =>
$categoria["familia"]);
}
ksort($linhas);
reset($linhas);
$espaco=" ";
$espacos=0;
$anterior="";
$categorias="<option value='0'>Todo o site</option>\n";
foreach ($linhas as $linha) {
if ($linha["indice"]!="") {
if (substr($linha["indice"],0,strlen($anterior))==$anterior &&
(strlen($linha["indice"])!=strlen($anterior) || $espacos==0)) $espacos+=4;
if (substr($linha["indice"],0,strlen($anterior))!=$anterior) $espacos-=4;
} else {
$espacos=0;
}
$anterior=$linha["indice"];
$categorias.="<option
value=\"".$linha["familia"]."\">".($espacos>0?str_repeat($espaco,$espacos):"").$linha["nome_cat"]."</option>\n";
}
return $categorias;
}
"ESV Media GmbH" <[EMAIL PROTECTED]> escreveu na mensagem
news:[EMAIL PROTECTED]
> Hey everybody,
>
> i´m becoming desperate about the following problem.
> We´ve an online-community / network.
> There´s a network table ( sql create below ), in which we store the
> relationships between the member.
> I need a SQL-statement or a PHP-Function to calculate the 2nd, 3rd and 4th
> degrees between the members.
> So, the friends of my friends, the friends of them and so on.
> I would really appreciate any help and would be so happy if you can help
> me with this problem :
>
> Here is the function i´ve right now :
>
>
> function getFriends($uid,$userself=0,$test=0){
> $uid = mysql_real_escape_string(strip_tags($uid));
> // Liefert alle "ersten grad Freunde" eines Members zurück
>
>
> $res = mysql_query(" (
> SELECT bid AS frd_id FROM network n
> WHERE ( n.mid IN (".$uid.") AND n.bid NOT IN (".$uid.",$userself) )
> )
> UNION
> (
> SELECT mid AS frd_id FROM network n
> WHERE ( n.mid NOT IN (".$uid.",$userself) AND n.bid IN (".$uid.") )
> )",_CON);
> $this->mySQLOptimizer("get friends");
>
> if ($test==1)
> {
> die(" (
> SELECT bid AS frd_id FROM network n
> WHERE ( n.mid IN (".$uid.") AND n.bid NOT IN (".$uid.",$userself) )
> )
> UNION
> (
> SELECT mid AS frd_id FROM network n
> WHERE ( n.mid NOT IN (".$uid.",$userself) AND n.bid IN (".$uid.") )
> )");
> }
>
> $friends = array();
> if ( mysql_num_rows($res) >= 1 )
> {
> while ($data = mysql_fetch_array($res))
> $friends[] = $data["frd_id"];
> }
> else
> {
> $friends=0;
> }
> return $friends;
> }
>
> function getFriensdegrees($uid){
> $uid = mysql_real_escape_string(strip_tags($uid));
> // level 1 ermitteln
>
> $retarry[1] = $this->getFriends($uid);
> if (is_array($retarry[1]))
> {
> // level 2 ermitteln
> $retarry[2] = $this->getFriends(explode(',',$retarry[1]),$uid,1);
>
> if (is_array($retarry[2]))
> {
> $retarry[3] = $this->getFriends(explode(',',$retarry[2]),$uid);
> }
> else
> {
> $retarry[3] = "0";
> }
>
> if (is_array($retarry[3]))
> {
> $retarry[4] = $this->getFriends(explode(',',$retarry[3]),$uid);
> }
> else
> {
> $retarry[4] = "0";
> }
> $returnarray = array("1" => $retarry[1], "2" => $retarry[2], "3" =>
> $retarry[3], "4" => $retarry[4]);
> }
> else
> {
> $returnarray = "";
> }
> return $returnarray;
> }
>
> function countfriensdegrees($uid){
> $uid = mysql_real_escape_string(strip_tags($uid));
> // level 1 ermitteln
> $retarry[1] = $this->getFriends($uid);
> if (count($retarry[1]) >= 1 && is_array($retarry[1]))
> {
> // level 2 ermitteln
> $retarry[2] = $this->getFriends(implode(",",$retarry[1]),$uid);
>
> if (is_array($retarry[2]))
> {
> $retarry[3] = $this->getFriends(implode(",",$retarry[2]),$uid);
> }
> else
> {
> $retarry[3] = "0";
> }
>
> if (is_array($retarry[3]))
> {
> $retarry[4] = $this->getFriends(implode(",",$retarry[3]),$uid);
> }
> else
> {
> $retarry[4] = "0";
> }
>
>
> /*
> // level 3 ermitteln
> foreach($members_lev2 as $value){
> $res = gForum::return_friends($value);
> foreach($res as $val) $members_lev3d[] = $val;
> unset($res);
> }
> $members_lev3 = array_unique($members_lev3d);
> $members_lev3 = array_diff($members_lev3, $members_lev2);
> $members_lev3 = array_diff($members_lev3, $members_lev1);
> $members_lev3 = array_diff($members_lev3, array("0" => $member));
>
> // level 4 ermitteln
> foreach($members_lev3 as $value){
> $res = gForum::return_friends($value);
> foreach($res as $val) $members_lev4d[] = $val;
> unset($res);
> }
> $members_lev4 = array_unique($members_lev4d);
> $members_lev4 = array_diff($members_lev4, $members_lev3);
> $members_lev4 = array_diff($members_lev4, $members_lev2);
> $members_lev4 = array_diff($members_lev4, $members_lev1);
> $members_lev4 = array_diff($members_lev4, array("0" => $member));
> */
> $retarray = array("1" => sizeof($retarry[1]), "2" => sizeof($retarry[2]));
> }
> else
> {
> $retarray = array("1" => 0, "2" => 0);
> }
> return $retarray;
> }
>
>
>
> Network Table :
>
> bid is the id of the person i´ve connected.
>
> DROP TABLE IF EXISTS `db80049_36`.`network`;
> CREATE TABLE `db80049_36`.`network` (
> `id` int(11) NOT NULL auto_increment,
> `mid` int(11) NOT NULL default '0',
> `bid` int(11) NOT NULL default '0',
> `reason` longtext NOT NULL,
> `action` int(1) NOT NULL default '0',
> `sent` datetime NOT NULL default '0000-00-00 00:00:00',
> `action_on` datetime NOT NULL default '0000-00-00 00:00:00',
> `reason_decline` longtext NOT NULL,
> PRIMARY KEY (`id`),
> KEY `mid` (`mid`),
> KEY `bid` (`bid`),
> KEY `accepted` (`action`),
> KEY `mid_bid_together` (`mid`,`bid`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
