PHP5 Multidimensional Array matching against database values

Hi Experts,
I'm having following data on the mysql tbl1(id1,name1) and tbl2(id2,name2) tables.
===tbl2=====
id1     name1
-------------------
1       DE SILVA
2       DUSHAN
===========

===tbl2======
id2   name2
---------------------
1      Dushan d1
2      Dushan d2
3      Dushan d3
4      Dushan d4
5      Dushan d5
6      Dushan d6
7      Dushan d7
===========

I should generate following codes(DDD00000XX) when data is on following combination.
----------------------------------------------------------------------
"DE SilVA" ,  "Dushan d1"      => "DDD0000001"
"DE SilVA" ,  "Dushan d2"      => "DDD0000002"
"DE SilVA" ,  "Dushan d3"      => "DDD0000003"
"DE SilVA" ,  "Dushan d4"      => "DDD0000004"
"DE SilVA" ,  "Dushan d5"      => "DDD0000005"
"DE SilVA" ,  "Dushan d6"      => "DDD0000006"
"DE SilVA" ,  "Dushan d7"      => "DDD0000007"
"DUSHAN " , "Dushan d1"      => "DDD0000008"
"DUSHAN " , "Dushan d2"      => "DDD0000009"
"DUSHAN " , "Dushan d3"      => "DDD0000010"
"DUSHAN " , "Dushan d4"      => "DDD0000011"
"DUSHAN " , "Dushan d5"      => "DDD0000012"
"DUSHAN " , "Dushan d7"      => "DDD0000013"
----------------------------------------------------------------------

I've prepared attached array and array loops. But it's not returning correct codes. It's always returning "DDD0000013" code ($product_code).

$product_codes 	= array("DE SilVA" => array(	"Dushan d1" 	=> "DDD0000001",
						"Dushan d2"	=> "DDD0000002",
						"Dushan d3"	=> "DDD0000003",
						"Dushan d4"	=> "DDD0000004",
						"Dushan d5"	=> "DDD0000005",
						"Dushan d6"	=> "DDD0000006",
						"Dushan d7"	=> "DDD0000007"
						),
			"DUSHAN " 	=> array("Dushan d1"	=> "DDD0000008", 
						"Dushan d2"	=> "DDD0000009", 
						"Dushan d3"	=> "DDD0000010",
						"Dushan d4"	=> "DDD0000011", 
						"Dushan d5"	=> "DDD0000012",
						"Dushan d7"	=> "DDD0000013" 
						)
					);

$SQL = "
	SELECT tbl1.name1, tbl2.name2
	FROM tbl2
	LEFT JOIN tbl1.id1 = tbl2.id2
	WHERE tbl1.name1 IN ('DUSHAN', 'DE SILVA')
";

$data = $this -> Account -> query($SQL);
foreach($data as $row){


	foreach($product_codes as $key1 => $product_code1)
	{					
		if($row['tbl1']['name1']= array_keys($product_codes,$product_code1)){
			foreach($product_code1 as $key2 => $product_code2)
			{
				if($row['tbl2']['name2']= array_keys($product_code1,$product_code2) )
				{

					 $product_code = $product_code2;
                                         return $product_code;
				}							
			}
		}
	} 
}

Open in new window

LVL 17
Dushan De SilvaTechnology ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dushan De SilvaTechnology ArchitectAuthor Commented:
I've prepared following code and it's working fine and doing the job correctly.
But for the future maintainability I got requirement to do code in above way.
if($row['tbl1']['name1']=="DE SILVA")
{	
	switch($row['tbl2']['name2'])
	{
		case "Dushan d1":
			$product_code = "DDD0000001";																
			break;
			
		case "Dushan d2":
			$product_code = "DDD0000002";																
			break;						
		
		case "Dushan d3":
			$product_code = "DDD0000003";																
			break;
		
		case "Dushan d4":
			$product_code = "DDD0000004";																
			break;									
				
		case "Dushan d5":
			$product_code = "DDD0000005";																
			break;	

		case "Dushan d6":
			$product_code = "DDD0000006";																
			break;

		case "Dushan d7":
			$product_code = "DDD0000007";																
			break;																																				

		//default:

	}

}elseif($row['tbl1']['name1']=="DUSHAN")
{
	switch($row['tbl2']['name2'])
	{
		case "Dushan d1":
			$product_code = "DDD0000008";																
			break;
			
		case "Dushan d2":
			$product_code = "DDD0000009";																
			break;				
		
		case "Dushan d3":
			$product_code = "DDD0000010";																
			break;
		
		case "Dushan d4":
			$product_code = "DDD0000011";																
			break;									
				
		case "Dushan d5":
			$product_code = "DDD0000012";																
			break;	

		case "Dushan d6":
			$product_code = "DDD0000013";																
			break;
		//default:			 
	}				
};

Open in new window

Sudaraka WijesingheWeb Application ProgrammerCommented:
You have a "return $product_code;" in the loop which breaks the loop, so that only the first record returned from the query will be processed. Could this be your problem?

Also, you can fetch the code from your predefined associative array without iterating through all the elements by doing something like this.

foreach($data as $row){
	$product_code=$product_codes[$row['tbl1']['name1']][$row['tbl2']['name2']];
	//Do something with the retrieved code ($product_code) here
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Do you mean to omit this line:

"DUSHAN " , "Dushan d6"      => "DDD00000??"

I think I would use two queries, something like this.

1. Set the DDD counter to zero.
2. SELECT the "name1" from tbl1 and build an array1
3. SELECT the "mane2" from tbl2 and build an array2
4. Iterate over the arra1
5. Combine the current "name1" with each element of array2, incrementing the DDD counter as you go through the data.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Dushan De SilvaTechnology ArchitectAuthor Commented:
Hi Sudaraka,
Thanks for the comment! Yes I need only the correctly matching code.(as shown in my second code(using switch) , which is correctly working and doing job correctly). Thing is with current code I'm getting code "DDD0000013" for all combination.



Hi Ray_Paseur,
Thanks for the comment! Thing is currently tbl1 is having more than 2 records and currently I'm selecting only two records ("Dushan" and "De Silva") from it. In future I may select more records. So then I may should create more than two arrays, if I do in the way which you explained.
I got instructions to do coding with above structure. Please help me.


BR Dushan.
Ray PaseurCommented:
When you use PHP iterators like while() and foreach() you do not have to be concerned about the number of records in the results set.  The iterators, by definition, will get to all of them!  

It would not matter, in concept, how many such arrays you needed to create.  The bigger question is about what kind of output you need to create.
Dushan De SilvaTechnology ArchitectAuthor Commented:
From SELECT command if I get data on one of above mentioned combination, I should generate the code.

Example: ["DE SilVA" ,  "Dushan d1"]      output is =>[ "DDD0000001"]
Dushan De SilvaTechnology ArchitectAuthor Commented:
my basic requirement is to generate output codes according to the values combinations on tbl1, tbl2 , when records selecting and reading one by one from php.
Sudaraka WijesingheWeb Application ProgrammerCommented:
Is the numeric part of the generated code predefined based on the two names? or is it something incremental based on the number of records returned from the DB?

If it's an incremental value you should be able to use a counter like Ray mentioned.
<?

$intProductCodeCount=0;
foreach($data as $row){
	$product_code='DDD'.str_pad(++$intProductCodeCount, 7, '0', STR_PAD_LEFT);
	//Do something with the code here
}

Open in new window


Or, if it's some the predefined based on the names, maybe you should keep them in another table and join it in your query to get the matching code.
Otherwise if you want to keep it in a PHP array, you can do it like in the first code you posted, but way you fetch the product code there should be more simpler like I mentioned earlier.
Dushan De SilvaTechnology ArchitectAuthor Commented:
Hi Sudaraka Malli,
Thanks for your Great Help!!

Dushan.
Sudaraka WijesingheWeb Application ProgrammerCommented:
Glad to help. Thanks for the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.