(PHP) Fetch from DB then print as list

[neojiphre]

Maybe I'm being dumb here but I can't find what I'm doing wrong. I have a table of categories in my database.

ID-------Name--------Parent
20-------Apples------ 1
21-------Bananas-----2
22-------Oranges-----3
24-------Peaches-----1
25-------Tomato----- 1

I want to fetch all records where parent equals to a certain value (eg Parent = 1) and then print this as unordered list.

Right now I have:

function getChild()
{
	//Take value from url
	$catId = (int)$_GET['c'];

	//Query to DB
	$sql = "SELECT cat_id, cat_name FROM tbl_category WHERE cat_parent_id=$catId";
	$result = dbQuery($sql);

	$childcat = array();
		while ($row = dbFetchAssoc($result)) {
        $childcat[] = $row;
    }
	
	return $childcat;
}

So this takes the id and name of a category. The id is to be used later for URL purposes.

<?php
	$child = getChild();
		foreach ($child as $childcat) {
			$cat_id = $childcat['cat_id'];
			$cat_name = $childcat['cat_name'];
			$url = $_SERVER['PHP_SELF'] . "?c=$cat_id";
		}

	//Print array as list.
	echo "\n<ul>\n" ;
		foreach($child as $childcat){
			echo "<li><a href=\"" . $_SERVER['PHP_SELF'] . "?c=$cat_id" . "\">$cat_name</a></li>\n";
	}
	echo "</ul>" ;
?>

All this does is print the same record (it chooses the last one) over a number of times. It does get the number of records to be fetched right. So suppose it was to fetch all records with a parent of 1 (3 records) it will show the record Tomato in a list three times.

How can I fix this? Thanks in advance.

 

[DesignerBill]

Your code looks unnecessarily complex to me, using the function to fetch the $childcat rows and all, but your answer lies in the second code block. You're assigning $cat_id and $cat_name in the first loop, so they will keep their values from the last row only.

So, remove the first loop and assign $cat_id, $cat_name, and $url (if you need this variable) in the second loop before you write out the <li> elements.

Another tip: You can avoid using escape characters in your echo statement by enclosing each string in single, rather than double quote marks.

 

[MarkR]

Also make sure you escape your input, with mysql-real-escape-string, or even better use prepared statements.