3 Replies - 984 Views - Last Post: 13 May 2011 - 03:23 PM Rate Topic: -----

#1 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Best practice for converting normalised MySQL data to PHP array

Posted 12 May 2011 - 10:22 PM

This is a general sort of question about converting normalised MySQL data into a PHP array.

Suppose you have a normalised data structure in your database, an example of which is given below:
Spoiler


Now suppose you want to get the normalised data into a PHP array, similar to the one given below:

Spoiler


In this instance, there are 5 "terminal" nodes -
FORMAT: Action - Food - FoodGroup
  • Slice - Apple - Fruit
  • Peel - Apple - Fruit
  • Peel - Orange - Fruit
  • Juice - Orange - Fruit
  • Slice - Ham - Meat


I'm leaning towards a solution something like this:
// PSEUDO-CODE
array = array()
fetch all foodgroups
foreach (foodgroup as this_foodgroup)
{
  array[] = this_foodgroup
  fetch all foods for this_foodgroup
  foreach (food as this_food)
  {
    array[this_foodgroup][] = this_food
    fetch all actions for this_food
    foreach (action as this_action)
    {
      array[this_foodgroup][this_food][] = this_action
    }
  }
}


What I'm concerned about is that there is potentially a lot of "fetches" being sent to the database. In this instance you have 6 individual fetches:
  • Foodgroups
  • Foods within Fruit Foodgroup
  • Foods within Meat Foodgroup
  • Actions within Apple food
  • Actions within Orange food
  • Actions within Ham food

With a much more extensive data structure, this could result in hundreds or thousands of individual "fetches". What would be best practice to do this, given the concerns of excessive load on the DB server and excessive load on the PHP server?

Is This A Good Question/Topic? 0
  • +

Replies To: Best practice for converting normalised MySQL data to PHP array

#2 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2911
  • View blog
  • Posts: 10,085
  • Joined: 08-August 08

Re: Best practice for converting normalised MySQL data to PHP array

Posted 13 May 2011 - 06:21 AM

This query is untested, but it's what I would attempt:
SELECT FoodGroups.FoodGroup, Food.Food, Actions.Action WHERE Food.FoodGroupID = FoodGroups.ID AND Actions.ID = FoodActions.ActionID

Was This Post Helpful? 0
  • +
  • -

#3 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6052
  • View blog
  • Posts: 23,487
  • Joined: 23-August 08

Re: Best practice for converting normalised MySQL data to PHP array

Posted 13 May 2011 - 07:47 AM

I created the appropriate foreign keys and indexes on the tables and got the desired SQL result from this query:

SELECT 
Food, FoodGroup, Action FROM 
Food JOIN FoodGroups ON FoodGroups.ID = Food.FoodGroupID 
JOIN FoodActions ON Food.ID = FoodActions.FoodID 
JOIN Actions ON Actions.ID = FoodActions.ActionID


yielding

Food 	FoodGroup 	Action
Apple 	Fruit 	Slice
Apple 	Fruit 	Peel
Orange 	Fruit 	Peel
Orange 	Fruit 	Juice
Ham 	Meat 	Slice

Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Best practice for converting normalised MySQL data to PHP array

Posted 13 May 2011 - 03:23 PM

Oh yeah I know how to get that result from a query, but then transforming it into the array format I showed... that's the part I'm musing over.

Array
-[FoodGroup][0]=Fruit
--[Food][0]=Apple
---[Action][0]=Slice
---[Action][1]=Peel
--[Food][1]=Orange
---[Action][0]=Peel
---[Action][1]=Juice
-[FoodGroup][1]=Meat
--[Food][0]=Ham
---[Action][0]=Slice



So 'echo $ary[FoodGroup][0][Food][1][Action][1]' would produce 'Juice'

This post has been edited by e_i_pi: 13 May 2011 - 03:35 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1