Solved

Nested Array PHP, MYsql

Posted on 2011-02-28
13
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
13 Comments
 
LVL 7

Accepted Solution

by:
szewkam earned 400 total points
ID: 35002867
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
ID: 35004269
Nope this is not the scenario.I want it in dropdownbox as optgroup and option.
0
 
LVL 7

Expert Comment

by:szewkam
ID: 35004624
my script generates array in the way you wanted. And doesn't run query in loop
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 3

Author Comment

by:shirso
ID: 35005875
#1052 - Column 'Id' in field list is ambiguous

This is error for your query
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 35006487
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
ID: 35006645
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
 
LVL 3

Author Comment

by:shirso
ID: 35006822

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 110

Expert Comment

by:Ray Paseur
ID: 35006979
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
ID: 35007172
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
ID: 35007196
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 110

Expert Comment

by:Ray Paseur
ID: 35007331
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
ID: 35007448
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
ID: 35020169
Thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

627 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