Dynamic query - let me explain

Hi everyone,

i’m using a MVC style system for my code and i need some help.

Now what i need to do is display a table with parent and child category but the child categories need to be indented.
The problem i am having is creating the dynamic array for the child categories part.

The function i have takes two parameters a ID and a ParentID


$link = dbconnect();
	$fullArry = array();

	$query = mysqli_query($link, "Query String");
	$queryNumRows = mysqli_num_rows($query);

	if($queryNumRows > 0){
		while($queryResults = mysqli_fetch_assoc($query)){
			//Build array
		}
	}

	return $fullArry;

The above code will get the parent categories - so my idea is to call the function again in the same function and carry the parents id into the function


	$link = dbconnect();
	$fullArry = array();

	$query = mysqli_query($link, "Query String");
	$queryNumRows = mysqli_num_rows($query);

	if($queryNumRows > 0){
		while($queryResults = mysqli_fetch_assoc($query)){
			//Build array
			//Call the function
			$childArry = categories(/*include the in and parent id here*/);
		}
	}

	return $fullArry;

And append the $childArry to the existing one.

is there a better way of doing this or am i on the right track?
Thanks for reading any replays will be great.

I’m not a 100% sure I understand your question but you want to display something like this?

Parent Category #1
  Child  #1.1
  Child #1.2
Parent Category #2
  Child #2.1
Parent Category #3
  Child #3.1
  Child #3.2
  Child #3.3
etc...

It’s hard to tell because we don’t see your table structures, but normally you would get all the data with one SELECT JOIN and just loop through it.

You would end up with a result set like this :

Headers would be something like this:
ParentID ChildID ParentName ChildName

And the data :

1, 1, Parent #1, Child #1.1
1, 2, Parent #1, Child #1.2
2, 3, Parent #2, Child #2.1

Then, when you loop, you check if the “current parent” was already displayed. If not, it’s a new parent so you need to display the “parent”, then all the children until a new parent shows up.

Hi xMog,

When i posted i was in a state of confusion and anger so sorry if the question was not clear :smiley:

Let me explain the structure of the table and way i’m having trouble with this.

Let say i have a category and a child category inside it:

parent.category#1
    child.category#1.1
    child.category#1.2
parent.category#2
    child.category#2.1
    child.category#2.2
...etc

I have no problem showing the above but where i am having trouble with is when someone adds a “child.category” inside another “chil.category”

parent.category#1
    child.category#1.1
        sub.child.category#1.1.1
        sub.child.category#1.1.2
        sub.child.category#1.1.3
    child.category#1.2
parent.category#2
    child.category#2.1
    child.category#2.2
        sub.child.category#2.2.1
...etc

The table structure has to grow according to the array structure, the above is for two levels deep but i need the table to grow as far down as they want for example if i wanted to have child categories 5 deep then the table has to grow to that size without having to change any code.

But i think i have a plan on how to do what i want.
i’m thinking of making my array structure as follows:

parent.id#1 =>
	name => parent.name
	order => parent.order
	children =>
		child.id =>
			name => child.name
			order => child.order
			level => child.level
		child.id =>
			name => child.name
			order => child.order
			level => child.level
parent.id#2 =>
	name => parent.name
	order => parent.order
	children =>
		child.id =>
			name => child.name
			order => child.order
			level => child.level
		child.id =>
			name => child.name
			order => child.order
			level => child.level
...etc

so all the child categories are now in a flat array structure and i use the “level” will tell me how meany indentations i have to apply to that child category in the table.
Let me know what you think and if it’s a good idea.
Thanks for the replay have a great day.

Well, that’s a case where OOP would be easier to work with IMO. You could have an object like this (it’s pseudo code) :

class Category {

   $children (list of "Category")
   $title

   Category($title, $level, $id);

   getChildren();
   addChild();
}

Also, getting a recursive structure like this is a little bit tricky with PHP & mySQL, but I think that you should be able to do it with one SQL query if you use a stored procedure.
Do a search on Google for “mysql tree structure”. I found this article which seems nice: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Also, doing a search on PHP & mysql tree structure should give you some ideas, like :
http://stackoverflow.com/questions/5916482/php-mysql-best-tree-structure
http://leon.vankammen.eu/tech/storing-retrieving-hierarchical-trees-between-php-and-mysql.html

Good luck! :slight_smile:

Thank you xMog i will have a look at the links you gave and let you know what the results are.

Thank you xMog.

The links you gave me are amazing, it is things like this that make me love coding - when you have no idea of what to do and then someone has the answer to your questions and it’s so simple, i think the links you gave will work i’m just looking up a couple of things so that i understand it currently.

I’m glad I could help! It’s actually a “not so easy problem”, but at least is has a name and somebody already spent time on it! Good luck! :wink: