1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need help on Mysql Select to show all under one id using php.

Discussion in 'Databases' started by davcheong, Jul 16, 2018.

0
  1. #1
    Hi all, need your help, any one can give an example code for what i am trying to do here is, using php and mysql,

    Database structure : (Figure A)
    Line 1 A ---
    / \ \
    Line 2 B C D
    / | \ | |
    Line 3 E F G H I
    / |
    Line 4 J K

    Output structure : show out in php ( Figure B)
    Line 1 A ------------
    / \ \
    Line 2 B C D
    | | |
    Line 3 E - F - G - J - K H I

    Example : J, K is under E, while E is under B, B is under A
    I've been trying to figure out the shortest way to output as shown in Output Structure figure B instead of the database structure shown in figure A. Any list under E or J will be all listed in Line 3 max.

    Any help/guide? in Php + Mysql
     
    davcheong, Jul 16, 2018 IP
  2. SpacePhoenix

    SpacePhoenix Well-Known Member

    Messages:
    196
    Likes Received:
    28
    Best Answers:
    2
    Trophy Points:
    155
    #2
    What database tables (inc fields) are involved and what's the required output?
     
    SpacePhoenix, Jul 16, 2018 IP
  3. Benanamen

    Benanamen Greenhorn

    Messages:
    22
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    20
    #3
    Your post makes no sense at all. What is the REAL problem you are trying to solve?
     
    Benanamen, Jul 16, 2018 IP
    davcheong, SpacePhoenix and sarahk like this.
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #4
    sarahk, Jul 16, 2018 IP
    davcheong likes this.
  5. davcheong

    davcheong Well-Known Member

    Messages:
    1,148
    Likes Received:
    51
    Best Answers:
    0
    Trophy Points:
    175
    #5
    Hi Sarahk, sorry didn't use much on the sqlfiddle.

    What i am trying to achieve is, is very similar to MLM but instead of showing the whole line out, i want to make it like example of image imgur dot com/a/XhnoIZ2


    Instead 4th line downline, i wish to have it in, any all agent under agent02, will be listed in 1 line on the 3rd line.
     
    davcheong, Jul 20, 2018 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #6
    Your picture explains the "lines"
    So really, you're just trying to flatten out the MLM to being only 3 layers. If you're not the top level or the next level then you get bumped up?

    [​IMG]
     
    sarahk, Jul 20, 2018 IP
    davcheong likes this.
  7. davcheong

    davcheong Well-Known Member

    Messages:
    1,148
    Likes Received:
    51
    Best Answers:
    0
    Trophy Points:
    175
    #7
    Yes. Bingo. I manage to do the continuous all the way down with many lines, but i just want 3 lines, as shown above.
     
    davcheong, Jul 20, 2018 IP
  8. sarahk

    sarahk iTamer Staff

    Messages:
    28,500
    Likes Received:
    4,460
    Best Answers:
    123
    Trophy Points:
    665
    #8
    Now is a good time to start. http://sqlfiddle.com/#!9/d038d1

    but thinking about it why wouldn't you just loop through the results and do it when you're building the html?

    <?php
$sql = "select * from peeps where parent_id is null";
// run that through your database library, pdo or whatever

foreach($results as $row){
   if (is_null($row['parent_id'])){
     
   // do all the html stuff
   getLine2Block($row['id']);
}
}

function getLine2Block($row){
   $sql = "select * from peeps where parent_id = $row['id']"; // except that the database library will probably take the sql input differently
   foreach($resultsLine2 as $row2){
   // do all the html stuff
   getLine3Block($row2['id']);
   }
}

function getLine3Block($row){
   $sql = "select * from peeps where parent_id = $row['id']"; // except that the database library will probably take the sql input differently
   foreach($resultsLine2 as $row3){
   // do all the html stuff
   getLine3Block($row3['id']);  // just keep calling this function until there is no data left.
   }
}
    PHP:
    In reality you'd probably do one query and iterate through the array many times but this may be easier to understand while you are learning.
     
    sarahk, Jul 21, 2018 IP
    davcheong likes this.
  9. davcheong

    davcheong Well-Known Member

    Messages:
    1,148
    Likes Received:
    51
    Best Answers:
    0
    Trophy Points:
    175
    #9
    Will have a try on the above code, and update here with the result.

    Thanks sarahk
     
    davcheong, Jul 21, 2018 IP