Array Building

[Mahngiel]

Greetings freaks,

 

I am trying to figure out how to build an array for the following purpose, and have just been spinning my wheels as it's a very new topic to me.

 

I have a database with a table and the following important rows:  ID, Parent, and Name.  In this table, Parents are given the value 0 for 'Parent' and a unique 'ID' & 'Name' - children are given all unique 'ID' and 'Name' with the value for Parent being 'ID' of the Parent they belong to.

 

Example: ( as 'ID', 'Parent', 'Name') 1, 0, John & 2, 1, Jane / 3, 0, Mark & 4, 3, Mary & 5, 3, Mindy.

 

As you can see, the second names in each example (the children) are connected to their parents by the 'Parent' row, whereas the first names (the parents) are not associated with anybody else.

 

What I would like to do is build an array that associates every child to the parent ([Parent] => child1 ).  Simple MySQL grants me an array for the parents:

$parent_pull = mysql_query('SELECT * FROM `table` WHERE Parent = 0')
while($row = mysql_fetch_array($parent_pull)){
$name[] = $row['Name'];
$id[] = $row['ID'];
}

 

Now, I want to be able to use the $id[]s to build arrays WHERE Parent = $id[].

 

I have tried in loops with $id[$i] & $i++ to cycle the array values, but PHP tells me there mysql_fetch_array() expects parameter 1 to be resource, boolean given ... on line 32.

$i = 0

while($i < 10){
$child_pull = mysql_query('SELECT * FROM `table` WHERE Parent = $id[$i] ');
$child = mysql_fetch_array($child_pull);
$child[] = $child['Name'];  <-- Line 32
$i++;
}

 

How would I go about pulling all the children and associating them with their parents?

[Mahngiel]

Another variation:

$parent_pull = mysql_query('SELECT * FROM `table` WHERE Parent = 0 ');
$i = 0;
while($row = mysql_fetch_array($parent_pull)){
	$name[] = $row['Name'];
	$parent[] = $row['ID'];
}
unset($name[11], $name[12]);
print_r($parent);
echo "<br /><br /> $parent[0] <br /><br />";

$child_pull = mysql_query('SELECT * FROM `table` WHERE Parent = $parent[0] ');
while($row = mysql_fetch_array($child_pull)){   <-- Line 28
	$child[] = $row['Name'];
}
print_r($child);  <-- Line 31

 

Outputs:

Array ( [0] => 79 [1] => 80 [2] => 81 [3] => 82 [4] => 83 [5] => 84 [6] => 85 [7] => 86 [8] => 87 [9] => 88 [10] => 91 [11] => 99 [12] => 96 )

79

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ... on line 28 Notice: Undefined variable: child in ... on line 31 

 

I will add that changing the $parent[] to an existing Parent ID produces the desired outcome, but this requires manual input and is not dynamic.  I have looked into foreach() but have not been successful in building a working loop.

[Drummin]

How would I go about pulling all the children and associating them with their parents?

Just a thought...  Have a table called users.  Each type of user gets a level, i.e. Parents get 1, child gets 2 etc.  In the user table have field called `parentid` and add the parent's id for each child.  You then can list users by type and get the parent of each child.  I know it doesn't answer your array question but might be an easier option to work with.

[Mahngiel]

Each type of user gets a level, i.e. Parents get 1, child gets 2 etc.  In the user table have field called `parentid` and add the parent's id for each child.  You then can list users by type and get the parent of each child. 

This is already the situation.  Perhaps my explanation wasn't well enough.

 

Table:

ID | Parent | Name

1  |  0  |  A

2  |  0  |  B

3  |  0  |  C

38 |  1  |  a

42  |  2  |  b

54  |  3  |  c

 

'a' is linked with it's parent via ID 1.  In my query, if I perform SELECT * ... WHERE ID = 1, 'a' will presented.  However, when I build an array from the parents with:

$parent_pull = mysql_query('SELECT * FROM `phpbb_forums` WHERE ID = 0 ');
$i = 0;
while($row = mysql_fetch_array($parent_pull)){
	$name[] = $row['Name'];
	$parent[] = $row['ID'];

}

 

I can use the parent's ID in itself, but I am not able to use the array to create a loop for each of the parents.  A while() loop tells me I cannot use [] in the query, and my limited ability to create a foreach() does not produce any results.

 

I would think the best way to do this would be to create an array for every parent ID, which is stored within the parent's Name.

ie:

[Mark](Array [0] => Mindy [1] => Mandy)

with the same methodology as:

$item_ary = array(
    'food'        => array(
        'banana',
        'apple',
        'pie',
        'ice-cream',
    ),

    'animal'    => array(
        'Horse',
        'Dog',
        'Cat',
    ),
);

Thank you for your input, Drummin, but there must be a way to proficiently do this.

[Drummin]

You should be able to get values from the table without array.

$parent_pull = mysql_query("SELECT Name,ID FROM `phpbb_forums` WHERE ID = 0");

while($row = mysql_fetch_array($parent_pull)){

$name = $row['Name'];

$parentid = $row['ID'];

echo "Name: $name ID: $parentid<br />";

}

[jcbones]

I understand what you are wanting to do, and have worked up a solution.  Please note, that the current database design only allows one parent per child.  To correct this, you would need a relational table.

 

Here is what I have currently.

<?php
include 'config.php'; //database connection.

$sql = "SELECT * FROM `table`"; //select all rows.
$result = mysql_query($sql) or trigger_error(mysql_error()); //complete or error.
while($r = mysql_fetch_assoc($result)) { //fetch
if($r['Parent'] == 0) { //if the parent is 0.
	$arr[$r['ID']][0] = $r['Name']; //set them to the first index in the array indexed by their id.
}
else {
	$arr[$r['Parent']][1][] = $r['Name']; //children get set to the second index in the parents array.  We
													//made this an array, so that multiple children can reside in it.
}
}

//BELOW is just an example of how to output it.
echo '<ol>'; //start an ordered list.
foreach($arr as $id => $array) { //itenerate the array.
echo '<li>' . $array[0]; //remember the first index is the parent.
	if(isset($array[1]) && is_array($array[1])) { //if a second index is set, and is an array, there is a child.
		echo '<ol>'; //start a nested ordered list.
		foreach($array[1] as $children) { //itenerate through the children array.
			echo '<li>' . $children . '</li>'; //put them in a line item.
		}
		echo '</ol>'; //close the children ordered list.
	}
echo '</li>'; //close the parent line item.
}
echo '</ol>'; //close the whole ordered list.

?>