Solved

Nested Array PHP, MYsql

Posted on 2011-02-28
13
419 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
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 3

Author Comment

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

This is error for your query
0
 
LVL 109

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 109

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 109

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dynamic Dropdowns 15 33
Wordpress Query 5 27
Showing multiple maps using PHP and Google Maps 8 21
Php logic to add to date card 9 24
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

821 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