Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

creating a chain list

Codes here !

Moderators: macek, egami, gesf

creating a chain list

Postby sameemur » Sun Apr 22, 2012 5:19 am

my table consist of

agent_code | name | introducer_code | rank | sales
-------------------------------------------------------
a001 | raj | a001 | 15 | 500
a002 | raja | a001 | 14 | 300
a003 | rahul | a001 | 14 | 200
a005 | sanjay | a002 | 13 | 500
a006 | sanjana| a002 | 13 | 100
a007 | rajesh | a003 | 13 | 400
a008 | ramesh | a005 | 12 | 200
a009 | suresh | a008 | 11 | 100
a0019 | ram | a009 | 10 | 150

there are nearly 600 records and i need to find the sum of sales agent wise along with the list of chain such as
raj / a001/15
--raja/a002/14
-----sanjay/a005/13
-----sanjana/a006/13
--rahul/a003/14
-----rajesh/a007/13

plz help me out
sameemur
New php-forum User
New php-forum User
 
Posts: 4
Joined: Sun Apr 22, 2012 4:48 am

Re: creating a chain list

Postby JordanMRichards » Wed Apr 25, 2012 7:35 am

Hello sameemur,

Once you have connected to your database correctly you can perform various queries.

I worked hard and coded this up, untested but it should help.

#Getting the sum of all the sales (You'll need to define the tabel name where it says "NAME HERE")
Code: Select all
$sum = 1; //place holder
$query = "SELECT * FROM name here";
if ($query_run=mysql_query($query)) {
   while ($query_rows=mysql_fetch_assoc($query_run)) {
   $sum = $sum + query_rows['sales'];
   echo ".$query_rows['agent']."/".$query_rows['name']."/".$query_rows['rank'];  // you can customise this as you wish, just use $query_rows[''] then the name of the row you want to get such as agent or rank.
   }
}
echo $sum; // $sum is the final result

//Credit to @JordanMRichards - twitter


Hope this helped :)

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
14 Year Old Programmer & Graphic Artist, Confident and Courageous
Image
User avatar
JordanMRichards
New php-forum User
New php-forum User
 
Posts: 84
Joined: Mon Apr 23, 2012 7:43 am

Re: creating a chain list

Postby egami » Wed Apr 25, 2012 9:33 am

Another way would be:
Code: Select all


/* Change this... */
$table = "change me";

$query = "SELECT * FROM $table";
$result = mysql_query($query);
$numrow = mysql_num_rows($result);
if ($numrow > 0) { 
  while 
($row = mysql_fetch_assoc($result)) { 
    $array
[$row['agent_code']] = $row;
  }
}

foreach(
$array as $agent_code => $new_array) { 
  $query 
= "SELECT * FROM $table WHERE introducer_code = '$agent_code'";
  $result = mysql_query($query);
  if (mysql_num_rows($result) > 0) { 
    while 
($row = mysql_fetch_assoc($result)) { 
      $final_array
[$agent_code][$row['agent_code']] = $row;
    }
  } else { 
    $final_array
[$agent_code]['empty'] = '';
  }
}

if (isset($final_array)) { 
  echo 
'<pre>'; print_r($final_array); echo '</pre>';
}
 else { 
  echo 
"There was a problem.. ";
}
 
User avatar
egami
php-forum GURU
php-forum GURU
 
Posts: 2197
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: creating a chain list

Postby sameemur » Sat Apr 28, 2012 9:51 am

thanks for your effort but this didn't help me
sameemur
New php-forum User
New php-forum User
 
Posts: 4
Joined: Sun Apr 22, 2012 4:48 am

Re: creating a chain list

Postby sameemur » Sat Apr 28, 2012 9:52 am

sameemur wrote:thanks for your effort but this didn't help me

thanks for your effort but this didn't help me
sameemur
New php-forum User
New php-forum User
 
Posts: 4
Joined: Sun Apr 22, 2012 4:48 am

Re: creating a chain list

Postby minimihi » Sat Apr 28, 2012 6:20 pm

Here is a list
Code: Select all
<?php
$database 
"test";
$table "chain_list";
$parent "a001";
$con mysql_connect("localhost""root""");
mysql_select_db($database$con);

$query "SELECT
   t1.agent_code AS lev1,
   t2.agent_code AS lev2,
   t3.agent_code AS lev3,
   t4.agent_code AS lev4,
   t5.agent_code AS lev5,
   t6.agent_code AS lev6,
   t7.agent_code AS lev7
FROM
   $table AS t1
LEFT JOIN $table AS t2
   ON t2.introducer_code = t1.agent_code AND
   t2.agent_code != t1.agent_code
LEFT JOIN $table AS t3 ON
   t3.introducer_code = t2.agent_code
LEFT JOIN $table AS t4 ON
   t4.introducer_code = t3.agent_code
LEFT JOIN $table AS t5 ON
   t5.introducer_code = t4.agent_code
LEFT JOIN $table AS t6 ON
   t6.introducer_code = t5.agent_code
LEFT JOIN $table AS t7 ON
   t7.introducer_code = t6.agent_code
WHERE
   t1.agent_code = '$parent'"
;
   
$result mysql_query($query);
while (
$row mysql_fetch_assoc($result)) {
    
$rows[] = $row;
}
$md_arr m_dimension_struct($rows);
print_arr($md_arr);

mysql_close($con);

//=========================================//

function print_arr($array$count=0) {
    global 
$table;
    
$i=0;
    
$tab ='';
    while(
$i != $count) {
        
$i++;
        
$tab .= "--";
    }
    foreach(
$array as $key => $value){
        
$query "SELECT *
        FROM
            $table
        WHERE
            agent_code = '$key'"
;
        
$result mysql_query($query);
        
$row mysql_fetch_assoc($result);
        if(
is_array($value)){
            echo 
$tab.$key."/".$row['introducer_code']."/".$row['rank']."<br />";
            
$count++;
            
print_arr($value$count);
            
$count--;
        } else {
            echo 
$tab.$key."/".$row['introducer_code']."/".$row['rank']."<br />";
        }
    }
    
$count--;
}
function 
m_dimension_struct($array) {
    
$hier_struct = array();
    foreach(
$array as $i => $element) {
        
$single null;
        foreach(
$element as $key => $value) {
            
$single add_dimension($single$array[$i][$key]);
        }
        
$hier_struct[] = $single;
    } 
    
$result multimerge($hier_struct[0], $hier_struct[1]);
    
$result multimerge($result$hier_struct[2]);
// hide errors =D
ob_clean();
    return 
$result;
}
function 
add_dimension($array$dimension) {
    if(empty(
$dimension))
        return 
$array
    if(!
is_array($array))
        return array(
$dimension => $array);
    foreach(
$array as $key => $value) {
        
$array[$key] = add_dimension($value$dimension);
    }
    return 
$array;
}
function 
multimerge($array1$array2) {
    if (
is_array($array2) && count($array2)) {
        foreach (
$array2 as $key => $value) {
            if (
is_array($value) && count($value)) {
                
$array1[$key] = multimerge($array1[$key], $value);
            } else {
                
$array1[$key] = $value;
            }
        }
    } else {
        
$array1 $array2;
    }
    return 
$array1;
}
/* function array_depth($array) {
    $max_indentation = 1;

    $array_str = print_r($array, true);
    $lines = explode("\n", $array_str);

    foreach ($lines as $line) {
        $indentation = (strlen($line) - strlen(ltrim($line))) / 4;

        if ($indentation > $max_indentation) {
                $max_indentation = $indentation;
        }
    }

    return ceil(($max_indentation - 1) / 2) + 1;
} */
?>
User avatar
minimihi
New php-forum User
New php-forum User
 
Posts: 238
Joined: Sat Apr 14, 2012 11:57 am
Location: Vilnius, Lithuania

Re: creating a chain list

Postby sameemur » Sun Apr 29, 2012 8:38 am

minimihi wrote:Here is a list
[code=php]<?php
$database = "test";
$table = "chain_list";
$parent = "a001";
$con = mysql_connect("localhost", "root", "");
mysql_select_db($database, $con);

$query = "SELECT
   t1.agent_code AS lev1,
   t2.agent_code AS lev2,
   t3.agent_code AS lev3,
   t4.agent_code AS lev4,
   t5.agent_code AS lev5,
   t6.agent_code AS lev6,
   t7.agent_code AS lev7
FROM
   $table AS t1
LEFT JOIN $table AS t2
   ON t2.introducer_code = t1.agent_code AND
   t2.agent_code != t1.agent_code
LEFT JOIN $table AS t3 ON
   t3.introducer_code = t2.agent_code
LEFT JOIN $table AS t4 ON
   t4.introducer_code = t3.agent_code
LEFT JOIN $table AS t5 ON
   t5.introducer_code = t4.agent_code
LEFT JOIN $table AS t6 ON
   t6.introducer_code = t5.agent_code
LEFT JOIN $table AS t7 ON
   t7.introducer_code = t6.agent_code
WHERE
   t1.agent_code = '$parent'";
   
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
}
$md_arr = m_dimension_struct($rows);
print_arr($md_arr);

mysql_close($con);

//=========================================//

function print_arr($array, $count=0) {
    global $table;
    $i=0;
    $tab ='';
    while($i != $count) {
        $i++;
        $tab .= "--";
    }
    foreach($array as $key => $value){
        $query = "SELECT *
        FROM
            $table
        WHERE
            agent_code = '$key'";
        $result = mysql_query($query);
        $row = mysql_fetch_assoc($result);
        if(is_array($value)){
            echo $tab.$key."/".$row['introducer_code']."/".$row['rank']."<br />";
            $count++;
            print_arr($value, $count);
            $count--;
        } else {
            echo $tab.$key."/".$row['introducer_code']."/".$row['rank']."<br />";
        }
    }
    $count--;
}
function m_dimension_struct($array) {
    $hier_struct = array();
    foreach($array as $i => $element) {
        $single = null;
        foreach($element as $key => $value) {
            $single = add_dimension($single, $array[$i][$key]);
        }
        $hier_struct[] = $single;
    } 
    $result = multimerge($hier_struct[0], $hier_struct[1]);
    $result = multimerge($result, $hier_struct[2]);
// hide errors =D
ob_clean();
    return $result;
}
function add_dimension($array, $dimension) {
    if(empty($dimension))
        return $array; 
    if(!is_array($array))
        return array($dimension => $array);
    foreach($array as $key => $value) {
        $array[$key] = add_dimension($value, $dimension);
    }
    return $array;
}
function multimerge($array1, $array2) {
    if (is_array($array2) && count($array2)) {
        foreach ($array2 as $key => $value) {
            if (is_array($value) && count($value)) {
                $array1[$key] = multimerge($array1[$key], $value);
            } else {
                $array1[$key] = $value;
            }
        }
    } else {
        $array1 = $array2;
    }
    return $array1;
}
/* function array_depth($array) {
    $max_indentation = 1;

    $array_str = print_r($array, true);
    $lines = explode("\n", $array_str);

    foreach ($lines as $line) {
        $indentation = (strlen($line) - strlen(ltrim($line))) / 4;

        if ($indentation > $max_indentation) {
                $max_indentation = $indentation;
        }
    }

    return ceil(($max_indentation - 1) / 2) + 1;
} */
?>
thanks a lot you saved my day
sameemur
New php-forum User
New php-forum User
 
Posts: 4
Joined: Sun Apr 22, 2012 4:48 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.