Sql Multi Linked List

Question

I am trying to display the order in which certain steps can be performed. Some steps can be taken at the same time, while others must follow in a specific order. I have the data already in the SQL table, and I just want it to be able to be transferred to a PHP array or something so that I can print it.

Data is stored in table 1 sql with two fields. The first is stat (which is the number of this block), the second is prereq, which identifies the predecessor (which will be some other stat). If the prereq field is null, this is the starting point. The end point is when there are no other lines.

First example:

status_number    prereq
-------------    -------
3                NULL
4                3
5                4
6                4
7                5
7                6
8                7

Looks like this conceptually:

, PHP, , 2 ( 5 6). , : (3,4, (5,6), 7,8). ? !

Answer 1

, ,

/* fetch the status_numbers and prereqs */
$query = "SELECT status_number, prereq FROM status";
$rs = mysqli_query($dbc, $query);
while ($row = mysqli_fetch_row($rs)) {
    $ids[] = $row[0];
    $fwd[$row[0]][] = $row[1];
}

/* find the endpoint(s) */
$ends = array();
foreach ($ids as $search) {
    $found = false;
    foreach ($fwd as $deps) {
        if (in_array($search, $deps)) {
            $found = true;
            break;
        }
    }
    if ($found)
        continue;
    $ends[] = $search;
}

/* sort the deps so we can string compare */
foreach ($fwd as &$deps)
    asort($deps);

    /* recursive resolve function */
function resolve($fwd, $id, &$output) {
    if (!is_null($id))
        array_unshift($output, $id);

    $count = count($fwd[$id]);
    if ($count == 0)
        return;

    if ($count > 1) {
        $subs = array();
        $groups = array();
        foreach ($fwd[$id] as $dep)
            $subs[$dep] = implode(',', $fwd[$dep]);

        foreach ($subs as $dep => $str1) {
            unset($subs[$index]);
            foreach ($subs as $index => $str2)
                if ($str1 == $str2) {
                    unset($subs[$index]);
                    $groups[$str1][] = $index;
                }
        }

        foreach ($groups as $ids => $group) {
            array_unshift($output, $group);
            $ids = explode(',', $ids);
            foreach ($ids as $id)
                resolve($fwd, $id, $output);
        }
    }
    else {
        resolve($fwd, $fwd[$id][0], $output);
    }
}

$output = array();
foreach ($ends as $end)
    resolve($fwd, $end, $output);
print_r($output);

:

Array
(
  [0] => 3
  [1] => 4
  [2] => Array
      (
          [0] => 5
          [1] => 6
      )

  [3] => 7
  [4] => 8
)

Answer 2

, , , :

# get the data - let the DB handle the ordering
$sql = "SELECT status_number,prereq FROM t ORDER BY prereq, status_number"
$res = mysql_query($sql);

# initialize pre-loop stuff
$status_array = array();
$prev_prereq = '';

# loop through the results
while ($row = mysql_fetch_assoc($res))
{
  # check if the prereq is the same as the previous result, and if so...
  if ($prev_prereq == $row['prereq'])
  {
    # look at the last element in the array
    end($status_array);
    $lastIndex = key($status_array);

    # if it not an array
    if (! is_array($status_array[lastIndex]))
    {
      #  make it one that contains the value that was in that spot
      $v = $status_array[lastIndex]
      $status_array[lastIndex] = array();
      status_array[$lastIndex][] = $v;
    }

    # then append the status to that array
    status_array[$lastIndex][] = $row['status_number'];

  } else
  # just append the latest status the the end of the status array
  {
    $status_array[] = $row['status_number'];
    $prev_prereq = $row['prereq'];
  }
}