Solved

Nested Array PHP, MYsql

Posted on 2011-02-28
13
414 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
 
LVL 3

Author Comment

by:shirso
ID: 35005875
#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
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 108

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 108

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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.

932 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

10 Experts available now in Live!

Get 1:1 Help Now