Solved

Nested Array PHP, MYsql

Posted on 2011-02-28
13
422 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.

728 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