Issue with SQL and Arrays

PHP
1

Think I could use some fresh eyes on this one. I’m sure this is how I’ve done it before but I cannot access the results of my SQL query properly.

I’m using this piece of code in a function to retrieve certain category information. In this particular instance it is calling this:

SELECT * FROM categories WHERE type IN (‘category’) ORDER BY category_name ASC

	$query = "SELECT $columns FROM categories WHERE $where $orderby $order $limits";
	$result = mysql_query($query) or die(mysql_error());
		while($row = mysql_fetch_array($result))
			$catList[] = $row;
			
		return $catList;

I tried just returning row but it only returned one record. Returning result just has it contain “mysql resource(#)”.

But I can’t seem to access the information properly, saying any I try to access are an undefined index.

foreach($categories as $category) {
			if($categories['category_id'] = $selected) {
				$output .= "\	<option value='".$categories['category_id']."'$selected>".$categories['category_name']."</option>\
";
				break;
			}
		}

A var_dump of $categories results in the following (restricted to one entry for readability)

array(1) { 
[0]=> array(14) { 
[0]=> string(1) "3" ["category_id"]=> string(1) "3" 
[1]=> string(2) "1a" ["category_name"]=> string(2) "1a" 
[2]=> string(5) "test1" ["cleanname"]=> string(5) "test1" 
[3]=> string(8) "category" ["type"]=> string(8) "category" 
[4]=> string(3) "123" ["description"]=> string(3) "123" 
[5]=> string(1) "1" ["parent"]=> string(1) "1" 
[6]=> string(1) "5" ["count"]=> string(1) "5" } }

I don’t know if being an array within an array is at issue, I assumed that made sense as it obviously separates individual records. Echoing anything but category_id returns “Array” though I can’t see any nested arrays. Echoing category_id just returns ‘1’ regardless of the actual id of the returned record.

EDIT: Removing makes it at least accessible but only returns 1 record and it still returns the wrong id :frowning: or at least not returns it as much as when it is being echo’d only ‘1’ appears.
EDIT2: Discovered at least part of the problem. Somewhere along the line, the default category (currently 1) is being assigned to category_id. Found it, changed = to ===, fixed that issue at least. Now just need to make it return the sql results properly.

2

Try mysql_fetch_assoc instead of mysql_fetch_array

and then echo your query and check that it’s being put out correctly.

3

Hmm…nope.

If I do it with , as so:

	$query = "SELECT $columns FROM categories WHERE $where $orderby $order $limits";
	echo $query;
	$result = mysql_query($query) or die(mysql_error());
		while($row = mysql_fetch_assoc($result))
			$catList[] = $row;
			
		return $catList;

I get all the results but it can’t access them, complaints about invalid index (again I assume because its an array in an array).

If I do it without the on $catlist, it works but only returns the one row.

EDIT: So confusing, I’m sure this is how I always access these kind of details.

4

You seem to be doing a foreach loop, aliasing $categories as $category, and then trying to access the indexes using $categories. I don’t think that makes sense since the indexes of $categories are the row results themselves. Try using $category[‘category_id’]

5

and you’re doing $categories = somefunctionnameicantseeposted(); ? I need to see a bit more of the code to answer this correctly.

6

Ha… yes, njr has hit upon it - good eyes. Inside the foreach you do need to use the individual rather than the collection.

7

You know, I did that before and it failed. Maybe it is working now because of the other suggestion of StarLion but either way it is working. Thanks SL and njr.