[RESOLVED] How to iterate thru query results by category->subject->articles

dmacman1962

Hello all,

I have a query that gets all the record for a particular Subject (Economics Id=7 in this example), and I can get the Subject -> articles for them, but I am stuck trying to break them down into arrays that I can iterate through.

Here is the query:

SELECT DISTINCT
eos.EyeopenersId,
eos.SubjectId,
eot.Id AS eotId,
eot.`Name`,
eo.Id AS eoId,
eo.EyeOpenersTypeId AS eoTypeId,
eo.Title
FROM eyeopeners_subjects as eos
LEFT OUTER JOIN eyeopeners as eo
ON eos.EyeopenersId=eo.Id
LEFT OUTER JOIN eyeopenerstype as eot
ON eot.Id=eo.EyeOpenersTypeId
WHERE eos.SubjectId=7
ORDER BY Date ASC

Here is an example from that query.

What I am trying to display would be (from my example JPG link above):
Videos (the name of each category before the records for that category)
--- 1st Title
--- 2nd Title
--- 3rd Title
--- 4th Title
Infographics
--- 1st Title
Infographics2
--- 1st Title
Charts & Graphics
-- 1st Title

Here is my attempt to populate the arrays:

while ($row = mysql_fetch_array($result)) {
			$eotId = $row['eotId'];
			$eoId = $row['eoId'];
			$Title = $row['Title'];
			$name = $row['Name'];
			$subject[$eotId]= $eoId;
			$types[$eotId] = $subject;
}

But this returns an array that I couldn't figure out how to sort like my example above.

I tried to use this post from a similar question I had before, but I struggle with arrays and looping through them.

Thanks,
Don

VadimK

well what i always! do is actually load all my query results into a 2D array using this code

	function DB_Result_To_Array($Result)
	{
		$res_array = array();
		for ($count=0;  $row = mysql_fetch_array($Result) ; $count++)
		{
			$res_array[$count] = $row;
		}
		return $res_array;
	}

and later on i will be able to call every single value using an
foreach ($res_array as $res)
of of course any other array

hope I helped
ask if you need something else

Weedpacket

VadimK wrote:
well what i always! do is actually load all my query results into a 2D array using this code

You might want to look at [man]PDOStatement-fetchall[/man] then, where that's done for you without needing all that extra code.

VadimK

Weedpacket;10998441 wrote:
You might want to look at [man]PDOStatement-fetchall[/man] then, where that's done for you without needing all that extra code.

well this is only for OOP and it does not seem like he wants to work with objects

dmacman1962

I like the PDO function, but its above my head. I tried to apply it and I tried VadimK's solution, and I did not get any results back.

VadimK, I am 'sort of' following you, but like you said, I am not OOP savvy. With your function, how would I pass the query to your function?

VadimK

I think that this will make everything much clearer
this is how you should call that function
because what it basically does is puts each result line into an array or arrays

function Get_Fields_Of_Study()
{
	DB_Connect();

$Query = "SELECT 
				* 
			FROM 
				tbl
			"; 

$Result = mysql_query($Query);
$Result = DB_Result_To_Array($Result);
return $Result;
}

try to print_r($Result) after you call the DB_Result_To_Array() function and you will see the entire thing

Derokorian

@: Note however that you are relying on the deprecated mysql_* library and you aren't checking of the query was actually successful. This isn't the way you should interact with the database. If you aren't ready for object you should REALLY use the mysqli library (the i stands for improved) which by the way can be used in OOP or procedural, and makes for a great stepping stone for going from procedural to OO.