Explore our sites

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

Here are my tables:

units_main:
FACID----sub_key-----ASGMT_Name
AR01____1000_______LEGAL COMMAND
AR01____1001_______OPPS 67
AR02____1002_______REG D


units_sub:
FACID-----sub_key-----Long_Name
AR01_____1000_______cmd a, b and sometimes c
AR01_____1000_______secondary cmd structrue
AR01_____1001_______opps seg 798 b
AR02_____1002_______s cmd structure cc8

I'm passing a FACID to the query via $_GET variable in URL to login page, then the $_GET gets set to a $_SESSION variable. Based on this FACID, i'd like to query units_main, then join records from units_sub via sub_key.

Here's my query:

$real_facid = $_SESSION['facid'];
$query = "SELECT m.FACID
 , m.sub_key
 , m.ASGMT_Name
 , s.Long_Name
FROM units_main AS m
LEFT OUTER
JOIN units_sub AS s
ON s.FACID = m.FACID
AND s.sub_key = m.sub_key
WHERE m.FACID = '$real_facid'
ORDER
BY m.FACID
, m.sub_key
, s.Long_Name";

Here I run the query and grab the array:

$result = mysql_query($query) or die(mysql_error());
while ($rows = mysql_fetch_array($result)){
echo $rows['ASGMT_Name'];
echo $rows['Long_Name'];}

The query works, but for FACID=AR01, I get the following results:

LEGAL COMMAND
cmd a, b and sometimes c
LEGAL COMMAND
secondary cmd structrue
OPPS 67
opps seg 798 b

I want the returned results to look like this (notice the 2nd "LEGAL COMMAND" removed):

LEGAL COMMAND
----cmd a, b and sometimes c
----secondary cmd structrue
OPPS 67
----opps seg 798 b

I know i'd have to loop over the array, but I'm really struggling with it...any kinda help would be appreciated!

Thanks for the help!

Edit (Dec 6th) Here was the Final, working script: 

$result = mysql_query($query) or die(mysql_error());
        $res = array();
        while ($rows = mysql_fetch_array($result))
        { 
            //echo $rows['ASGMT_Name'];
            if (!array_key_exists($rows['ASGMT_Name'], $res))
            {
                $res[$rows['ASGMT_Name']] = array();
            }

            //for some reason this didn't work?
            //$values = $res[$rows['ASGMT_Name']];

            $var = $rows['Long_Name'];
            array_push($res[$rows['ASGMT_Name']],$var);

        }
                    print_r($res);

I want to add 2 more levels to the beginning of the $res array, i.e.:

name -> branch -> ASGMT_Name -> Long_Name

I tried this, but it's not working, the 'LEGAL COMMAND' needs to be the index of the array just before it....:

$result = mysql_query($query) or die(mysql_error());
        $res = array();
        while ($rows = mysql_fetch_array($result))

        {   //print_r($rows);
            //echo $rows['ASGMT_Name'];

            $res[$rows['name']] = array($rows['branch'] => array());
                //$var = $rows['branch'];
                //array_push($res[$rows['name']],$var);

            if (!array_key_exists($rows['ASGMT_Name'], $res))
            {
                $res[$rows['ASGMT_Name']] = array();
            }

            $var = $rows['Long_Name'];
            array_push($res[$rows['ASGMT_Name']],$var);

        }
        print_r($res);

Here's the output from the above:

Array ( [name] => Array ( [branch] => Array ( ) )[LEGAL COMMAND] => 
Array ( [0] => cmd a, b and sometimes c [1] => cmd a, b and sometimes c)

any help?

thanks again!

share|improve this question
add comment

1 Answer

up vote 0 down vote

You need something like

$res = array();
while ($rows = mysql_fetch_array($result))
{
  if (!array_key_exists($res, $rows['ASGMT_Name']))
  {
      $res[$rows['ASGMT_Name']] = array();
  }
  $values = $res[$rows['ASGMT_Name']];
  array_push($values,$rows['Long_Name'];)     
}

Now you have hierarchical data in $res.

share|improve this answer
 
should it be array_push($res, $values, $rows['Long_Name']); ? –  dan Nov 20 '11 at 14:46
 
Nope, if I understood you well. It should be as it is in the answer. Again, unless I misunderstood the question... –  a1ex07 Nov 20 '11 at 15:54
 
hum, no matter what I do, I'm able to access the key in $res, but the value is another array. foreach($res as $mname => $name) {echo $mname; if(is_array($name) { foreach($name as $ikey => $ivalue) { echo $ikey; echo $ivalue;}}} $mname prints great, but I can't get anything to print for the $name array? –  dan Nov 21 '11 at 1:19
 
for some reason, the array() stored in the 'value' of $res[key] wasn't being populated. I changed to... –  dan Nov 21 '11 at 16:27
 
$var = $rows['Long_Name']; array_push($res[$rows['ASGMT_Name']],$var); –  dan Nov 21 '11 at 16:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.