Solved

Nested Array PHP, MYsql

Posted on 2011-02-28
13
410 Views
Last Modified: 2012-05-11
Hi,
I am looking for something like that
$arr['Sport'] = array(6 => 'Golf', 9 => 'Cricket',7 => 'Swim');
$arr['Rest']  = array(3 => 'Sauna',1 => 'Massage');

My code is below.
There is only one area that I can't solve.In $result2 there is extra index of array.How can I solve this.Is this code right?Please help

Thanks ,  
function dropdownforfunctionalarea()
{
    $sql1="select Id,CatagoryName from mstjobcatagory";
    $res1=mysql_query($sql1); 
     $result=array();
     $CatId=null;
    
     while($row1=mysql_fetch_assoc($res1))
     { 
         $sql2="select Id,FunctionName from mstjobfunction where CatagoryId=".$row1["Id"]."";
         $res2=mysql_query($sql2);
        $result2=array(); 
        
         while($row2=mysql_fetch_array($res2))
         {    
             $result2[]=array($row2["Id"]=>$row2["FunctionName"]); 
             
         }
         $result[$row1["CatagoryName"]]=$result2;
       
     }
     return $result; 
      
}

Open in new window

0
Comment
Question by:shirso
  • 8
  • 3
  • 2
13 Comments
 
LVL 7

Accepted Solution

by:
szewkam earned 400 total points
Comment Utility
one of the worst thing you can do is put query into a loop.
Try something like below
function dropdownforfunctionalarea()
{
    $sql1="select Id,CatagoryName from mstjobcatagory c JOIN mstjobfunction f ON(f.CategoryId = c.Id)";
    $res1=mysql_query($sql1); 
     $result=array();
     $CatId=null;
    
     while($row1=mysql_fetch_assoc($res1))
     { 
         $result[$row1['CategoryName'][$row1['Id']] = $row1['FunctionName'];
       
     }
     return $result; 
      
}

Open in new window

0
 
LVL 3

Author Comment

by:shirso
Comment Utility
Nope this is not the scenario.I want it in dropdownbox as optgroup and option.
0
 
LVL 7

Expert Comment

by:szewkam
Comment Utility
my script generates array in the way you wanted. And doesn't run query in loop
0
 
LVL 3

Author Comment

by:shirso
Comment Utility
#1052 - Column 'Id' in field list is ambiguous

This is error for your query
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
Comment Utility
MySQL is not a black box - you must test for success when you use mysql_query() using something like this:

if (!$res = mysql_query($sql))
{
    echo PHP_EOL . "SQL FAIL: $sql";
    echo PHP_EOL . mysql_errno();
    echo PHP_EOL . mysql_error();
    die("FIX THE QUERY!");
}

Please post the CREATE TABLE statements for the tables you want to query.  In general, i agree with what szewkam wrote about queries inside loops, but it may not be a problem for you if you use appropriate indexing and LIMIT clauses.
0
 
LVL 3

Author Comment

by:shirso
Comment Utility
Please check the table and screen shot.I just want the same result without array index(i.e 0,1,2,3).
DROP TABLE IF EXISTS `mstjobcatagory`;
CREATE TABLE IF NOT EXISTS `mstjobcatagory` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `CatagoryName` varchar(255) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `mstjobcatagory`
--

INSERT INTO `mstjobcatagory` (`Id`, `CatagoryName`) VALUES
(1, 'Test Job1'),
(2, 'Test Job2'),
(3, 'Test Job3');

Open in new window

DROP TABLE IF EXISTS `mstjobfunction`;
CREATE TABLE IF NOT EXISTS `mstjobfunction` (
  `Id` int(10) NOT NULL AUTO_INCREMENT,
  `CatagoryId` int(10) NOT NULL,
  `FunctionName` varchar(255) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `mstjobfunction`
--

INSERT INTO `mstjobfunction` (`Id`, `CatagoryId`, `FunctionName`) VALUES
(1, 1, 'Test Job Function1 For Job Category1'),
(2, 1, 'Test Job Function2 For Job Category1'),
(3, 1, 'Test Job Function3 For Job Category1'),
(4, 1, 'Test Job Function4 For Job Category1'),
(5, 2, 'Test Job Function1 For Job Category2'),
(6, 2, 'Test Job Function2 For Job Category2'),
(7, 2, 'Test Job Function3 For Job Category2'),
(8, 2, 'Test Job Function4 For Job Category2'),
(9, 3, 'Test Job Function1 For Job Category3'),
(10, 3, 'Test Job Function2 For Job Category3'),
(11, 3, 'Test Job Function3 For Job Category3'),
(12, 3, 'Test Job Function4 For Job Category3');

Open in new window

function dropdownforfunctionalarea()
{
  $sql1="select Id,CatagoryName from mstjobcatagory";
    $res1=mysql_query($sql1); 
     $result;
     $CatId=null;
    
     while($row1=mysql_fetch_assoc($res1))
     { 
         $sql2="select Id,FunctionName from mstjobfunction where CatagoryId=".$row1["Id"]."";
         $res2=mysql_query($sql2);
        $result2;
        
         while($row2=mysql_fetch_array($res2))
         {    
             
             $result2[]=array($row2["Id"]=>$row2["FunctionName"]); 
             
         }
         $result[$row1["CatagoryName"]]=$result2;
       
     }
     return $result; 
      
}

Open in new window

Document.doc
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Author Comment

by:shirso
Comment Utility

function dropdownforfunctionalarea()
{
  $sql1="select Id,CatagoryName from mstjobcatagory";
    $res1=mysql_query($sql1); 
     $result=array();
     $CatId=null;
    
     while($row1=mysql_fetch_assoc($res1))
     { 
         $sql2="select Id,FunctionName from mstjobfunction where CatagoryId=".$row1["Id"]."";
         $res2=mysql_query($sql2);
        $result2=array();
        
         while($row2=mysql_fetch_array($res2))
         {    
             
             $result2[]=array($row2["Id"]=>$row2["FunctionName"]); 
             
         }
         $result[$row1["CatagoryName"]]=$result2;
       
     }
     return $result; 
      
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I am a little confused about the names of the columns, and I think you will find that if you use consistent names across the two tables, it will be very helpful.

Let's see if I can describe the logic of this in plain language...

SELECT Id, CatagoryName FROM mstjobcatagory

With each resulting row, create an entry in an array-1 with the key equal CatagoryName and the value equal Id

Iterate over the array taking the Id and using it to query mstjobfunction, WHERE CatagoryId = Id

With each resulting row, create an entry in an array-2 with the key equal Id and the value equal FunctionName
When the last row has been processed, put this array-2 into the value of the array-1

When all such rows have been processed, iterate over array-1 and test each value with is_array(); discard any elements that are not arrays.

Does that sound like what you are trying to do?
0
 
LVL 3

Author Comment

by:shirso
Comment Utility
You can see the screen shot.I get desired result but $result2 array's index is there.So,I get additional optgroup.

Can you understand the scenario?
0
 
LVL 3

Author Comment

by:shirso
Comment Utility
Please see the document.doc attach file.I am getting parent child structure.But don't know how to rid off this index.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I looked at the screen shot, but it's not much help.  It might be more useful to see an example of what you want - then we might be able to show you how to use PHP to generate the HTML.
0
 
LVL 3

Author Comment

by:shirso
Comment Utility
I am using smarty.
In PHP page
$smarty->assign('lookups', dropdownforfunctionalarea());

In smart template
 {html_options name=foo options=$lookups}

0
 
LVL 3

Author Closing Comment

by:shirso
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now