[SOLVED] Newbie routine optimization

[fusionpixel]

Hi all,

 

I have the following loops that work as expected:

 

$firstQuery = " SELECT bgroup.group FROM bgroup";
$firstResult = mysql_query ( $firstQuery ) or die ( mysql_error() );

while( $firstRow = mysql_fetch_array( $firstResult )) {

echo "<h2>". $firstRow['group']." </h2><br />";

$query = " SELECT bmarks.name, bmarks.group, bmarks.address FROM bmarks LEFT JOIN ( bgroup) ON (bgroup.group = '".$firstRow['group']."' ) WHERE bmarks.group = bgroup.group ORDER BY bgroup.group";	 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

echo "<a href=\"" .$row['address'] ."\" target=\"_blank\">". $row["name"]. "</a>";
	echo "<br />";
}
}

 

But I am a much better way to do this rather than having to query the database everytime the first loop kicks in. I have a small database at the moment but I cant imagine how much overload it would be with couple of hundred of items in the DB.

 

My DB consists of 2 tables

 

bmarks table contains all the bookmarks (address/description/info)

bgroup table contains a list of groups that beling to the bmark table

 

Thanks

[Mirkules]

Since you're obviously looping through a result set from a previous query, can you find a way to combine the two queries?

[fusionpixel]

Right, and that is why I am looking for help since I dont know exactly how to combine them. At least I dont know of any specific logical way to do it.

 

This is what happens..

 

1. get list of groups

2. with the first group loop inside the first group and any item that matches the group name from the first table show it

3. loop

 

I am thinking that I could save the info in an array and then access it through there.... but only hitting the database once per table.

[Mirkules]

My apologies, I missed the first part of the code where you show the first query.

 

Anyway, I'm not sure why you need the first query at all? I think you can get away with doing a left join on bgroup.group = bmarks.group, without the WHERE clause.

 

From http://www.w3schools.com/sql/sql_join.asp:

 

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

[fusionpixel]

Ah good point... the reason why I need the first query is this

 

 

[GROUP TITLE]

-child element

-child element

-child element

[GROUP TITLE]

-child element

-child element

-child element

-child element

-child element

[GROUP TITLE]

-child element

-child element

-child element

 

without the first query I would get this:

 

-child element

-child element

-child element

-child element

-child element

-child element

-child element

-child element

-child element

-child element

-child element

 

Thanks for your help and your suggestions...

[Mirkules]

I see what you mean now. In that case, why don't you add the bgroup.group field to your select clause, and then test in your PHP code whether the group is the same as you had before.

 

Based on that test, you can then choose to write the h2 or not... I'm not sure if I'm being clear, so let me demonstrate:

 

 

$query = " SELECT bmarks.name, bmarks.group, bmarks.address, bgroup.group as bg FROM bmarks, bgroup WHERE bmarks.group = bgroup.group ORDER BY bgroup.group";
$result = mysql_query($query);

$previous_group = "";
while ($row = mysql_fetch_assoc('$result'))
{
if ($previous_group != $row['bg'])
	echo "<h2>". $firstRow['bg']." </h2><br />";
echo "<a href=\"" .$row['address'] ."\" target=\"_blank\">". $row["name"]. "</a>";
        echo "<br />";
        $previous_group = $row['bg'];
}

 

Would that work for you?

[fusionpixel]

Ah! Yeah that makes sense and seems logical enough for me....

 

Thanks so much I really appreciate it.

[fusionpixel]

One last comment, on this line

 

 

while ($row = mysql_fetch_assoc('$result'))

 

it should be

 

 

while ($row = mysql_fetch_assoc($result))

 

Thanks again...

[Mirkules]

Well, that's what I get for not trying out the code first

[fusionpixel]

haha! Are you kidding me? one small insignificant error without first testing. that is great!