Organize 2 mysql querys

[transmutated]

Alright, so I have 2 query's I have run on a page and now I want to organise them to display efficiently on the same page. As of right now, the first query is listed in this manner:

while($catrow = mysql_fetch_array($catquery, MYSQL_ASSOC)) {
 echo "<h2>". $catrow['categorie'] . "</h2>";
}

As you can see...this is just putting headings on the page.

The other query is from a relational database that has a foreign key in it that will match to the primary key database the array from above comes from.

I couldn't find anything on the internet that pointed me in the right direction as for what I want to do. For these headings that are generated from the above code, I want to list the coordinating information below them that have have the same key and foreign key in them. Is there an IF statement I can use to sort the second query out and insert that information into the above loop or do I need to need to keep it out of the loop and do it another way.

I tried this:

while($catrow = mysql_fetch_array($catquery, MYSQL_ASSOC)) {
 echo "<h2>". $catrow['categorie'] . "</h2>";
 if ($row['cat_key'] == $catrow['catid']) {
 echo "<p>" . $row['event'] . "</p>";
 }
}

and it didn't work. I hope my above code kinda shows you want I want to do. I just think I am going about it the wrong way.

Any help is greatly appreciated! Thanks in advance.

[kamperzoid]

Why don't you join your 2 query's in 1 query? and filter your data that way.

[transmutated]

Alright, I just did that. Talk about tricky:)

*Note to self*
JOIN statements come before WHERE statements...

Now, the results of the array look like this:

Category 1
event 1
description

Category 1
event 2
description

Category 2
event 3
description

Category 2
event 4
description

How I would like to do this now is just have it say:

Category 1
Event 1
description

Event 2
description

Category 2
Event 3
description

Event 4
description

Any direction for me?

[kamperzoid]

$cat_id = $catrow['catid'];

if ($cat_id != $cat_id_old) {
//Here you show your category like this
echo "<h2>". $row['categorie'] . "</h2>";
}
//Make from the new id the old id
$cat_id_old = $cat_id;

//And the event you allways show
echo "<p>" . $row['event'] . "</p>";

////////////////////////////////////////
with this peace of code you check of there is an new category and when it is you show it.

This peace of code workt for me so i hope it helps you.

greetz

[transmutated]

Sorry Kamperzoid, no go.

Is there a way to collect only unique results from the above while statement?

I have already tried storing the values from the specific field into an array and using the array_unique function to root out the duplicates, but alas, it didn't work as I expected it to.

Any help would be appreciated. Thanks again!

[transmutated]

First, here is the code:

while ($catrow = mysql_fetch_array($result)) {
$row = mysql_fetch_array($query);
// Show header for category list
echo "<h1>" . $catrow['categorie'] . "</h1>";
// Check to see if query result should be listed within
// current category
 if ($row['cat_key'] == $catrow['cat_id']) {
echo $row['event'];
} else {
// Or else there are no events 
echo "There are no events scheduled for this category";
}
}

I think I can explain what I need to do a little better now. This code works as it should until I need to more then one event. As you can see, there is currently an echo event there. That works if there is only one event, but I can't seem to get a loop to work in there. I have tried a while loop, but it repeats itself to no end. I have tried a foreach statement, but that didn't work for me either. So, I ask you:) What can I do run a nested loop? Thanks in advance!

[rocknbil]

How I would like to do this now is just have it say:

There are two approaches to this: one big select with a join or a select inside the category select (nested query.) As kamperzoid's sample demonstrates, you may have output issues with a single select, having to "keep track" of what the current head is, inserting interim html, etc. If you use semantic html - as the output you're after is obviously LISTS,

<h3>cat</h3>
<ul>
<li>Item one</li>
<li>Item two</li>
<li>Item three</li>
</ul>

It gets worse. The nested query doubles your database queries, but is easier to wrestle with in these cases. Note I've created a sample that may not have a lot to do with your specific situation, but it's simplified to get the logic. May contain errors, typed on the fly.

$query = "select cat_id,title from categories order by title";

$result=@mysql_query($query); 
while ($row=mysql_fetch_array($result)) { 
 echo "<h3>" . $row[1] . "</h3>"; 
 echo "<ul>"; 
 // We're done with $query, recycling is efficient. :-) 
 $query = "select prod_id,title from products where cat_id=" . $row[0] . " order by title"; 
 $result2=@mysql_query($query); 
 while ($row2=mysql_fetch_array($result2)) { 
echo "<li><a href=$this_script?pid=" . $row2[0] . ">" . $row2[1] . "</a><li>\n"; 
 } 
 mysql_free_result($result2); // Just because you should 
 echo "</ul>"; 
} 
mysql_free_result($result); 

Will it be "slower?" Maybe. But unless you're outputting a page that is ten miles into scrolling perdition, it will probably be insignificant.

[transmutated]

Thanks rocknbill, I'll give that a try later on today! Thanks for the help!

[transmutated]

That worked like a champ rocknbill! I didn't know you could use the results from a query in another query. Thanks a mother load! I can finally put this to rest!

[rocknbil]

NP, but keep in mind you're doubling the queries, only nest one if you need it . . . the idea is to balance the potential load on the server against simplifying the task in the code. On a huge or busy database, one might have to go the first route.