Page 1 of 1

Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 4:35 pm
by TheBrandon
Hello all,

This is my first time toying with functions returning arrays so I'm sure the answer is simple, but please help.

I have a function doing my query:

Code: Select all

	function fetch_category_discounts($cat_ID) {
			mysql_connect(SQL_HOST_NAME, SQL_USER_NAME, SQL_PASSWORD) or die(mysql_error());
			mysql_select_db(SQL_DATABASE) or die(mysql_error());
			$result = mysql_query("SELECT * FROM discounts, category_relations WHERE category_relations.member_of = '$cat_ID' AND category_relations.id = discounts.id")or die(mysql_error());
			return mysql_fetch_assoc($result);
	}
Then I have my code using the function:

Code: Select all

		$discounts = fetch_category_discounts($cat_ID);
		print_r($discounts);
It's returning:

Code: Select all

Array ( [id] => 1 [short_discount] => 15% Off International Flights [long_discount] => 15% Off all International Flights. Coach seating only. Must wear cool sunglasses when in flight. [merchant] => Delta [expiration] => 1277494242 [member_of] => 1 )
However if you run the same query in MySQL you get:

Code: Select all

id	short_discount	long_discount	merchant	expiration	id	member_of
1	15% Off International Flights	15% Off all International Flights. Coach seating o...	Delta	1277494242	1	1
2	Testing Short Discount	I am a longer discount because I am the long_disco...	HIM	 	2	1
Where am I going wrong?

Re: Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 4:43 pm
by requinix

Code: Select all

return mysql_fetch_assoc($result);
Your code only gets one row. If you want all of them then I suggest a 2D array and a while loop.

Re: Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 4:44 pm
by TheBrandon
I thought that would dump all of the results into an associative array?

Can you please give me an example of how the code should be modified?

Re: Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 4:49 pm
by flying_circus
Try something like this

Code: Select all

	function fetch_category_discounts($cat_ID) {
			mysql_connect(SQL_HOST_NAME, SQL_USER_NAME, SQL_PASSWORD) or die(mysql_error());
			mysql_select_db(SQL_DATABASE) or die(mysql_error());
			$result = mysql_query("SELECT * FROM discounts, category_relations WHERE category_relations.member_of = '$cat_ID' AND category_relations.id = discounts.id")or die(mysql_error());

			$aOutput = array();
			while($row = mysql_fetch_assoc($result))
				$aOutput[] = $row;

			return $aOutput;
	}

Re: Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 4:52 pm
by TheBrandon
That did it. So mysql_fetch_assoc only returns a single entry?

Re: Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 5:01 pm
by flying_circus
Yes.

mysql_query returns a result set.

mysql_fetch_array, mysql_fetch_assoc, mysql_fetch_row, etc all return 1 row of a result set.

You will need to use a loop to iterate through all the rows of a result set. :)

Re: Array only returning one result? (Should return 2)

Posted: Tue May 25, 2010 5:16 pm
by TheBrandon
Thank you for the help and explanation. I really appreciate it.