Page 1 of 1

Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Sun Feb 13, 2011 12:35 am
by DedMousie
Coming from a long Coldfusion background, I'm having a little trouble grasping some hopefully basic stuff. Having problems with placing a 2nd query inside a loop. Or, having problems grouping output, depending on how you look at it.

So, here's what I've got:

Code: Select all

QUERY IN A LOOP

<?php
// FIRST QUERY
 $result = mysql_query("SELECT id_cat, link_cat FROM link_categories ORDER by link_cat_ord") or die(mysql_error()); 
?> 

<!--- BUNCH OF STUFF SNIPPED OUT HERE --->

<?php
// LOOP HERE - SHOWS CATEGORY WITH H1 TAG
while ($row = mysql_fetch_assoc($result)) {
    echo "<h1>".$row['link_cat']."</h1>";

// 2ND QUERY HERE, USES RESULTS FROM FIRST QUERY TO PULL UP RELATED RECORDS AND DISPLAY UNDER THE MAIN CATEGORIES
     $result2 = mysql_query("select link_name, cat_id from link_urls where cat_id = $row[id_cat]") or die(mysql_error());
        while ($row = mysql_fetch_assoc($result2)) {
          echo $row['link_name'];
           echo "<br />";
     }	
  }
?>
1. 2nd query isn't displaying any results.
2. If I change "$row[id_cat]" to "10" (a known ID), I'll get results - so I must have a syntax error because the 2nd query WILL work if given a good ID number.

What do I have wrong?
------------------------------------------------
Gotta be a better way though. In Coldfusion, I could just run a single query on the two tables, and then GROUP the output with CFOUTPUT tags

Code: Select all

<cfoutput query="myquery" group="mycategories">
  <h1>#mycategories#</h1>
    <cfoutput>
       #allMySubCategories#<br />
   </cfoutput>
</cfoutput>
Giving me the desired output:
MAIN STUFF
sub stuff 1
sub stuff 2
sub stuff 3
MORE STUFF
sub stuff 1a
sub stuff 2a
sub stuff 3a

Look how clean and simple that is! For the life of me, I can NOT find a way to do it in PHP, thusly the query inside the loop - which will also work in Coldfusion, but isn't the optimal way of doing things.

Advise please?

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Sun Feb 13, 2011 1:46 am
by s992
I don't know of a way to do it within a single query..maybe someone with a little more knowledge can help with that.

As far as your current code, try wrapping the index of your array with single quotes($row['id_cat']) and see if that helps.

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Sun Feb 13, 2011 3:29 am
by mellowman
Instead of querying two things in one query, try storing the first query within a variable/array and then when you loop in the second query echo the variable.

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Sun Feb 13, 2011 3:32 am
by Eran
You need to learn about JOIN syntax - http://dev.mysql.com/doc/refman/5.5/en/join.html

Code: Select all

SELECT id_cat, link_cat,link_name 
FROM link_categories 
INNER JOIN link_urls ON link_urls.cat_id=link_categories.id_cat
ORDER by link_cat_ord

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Sun Feb 13, 2011 3:34 am
by mellowman
Eran wrote:You need to learn about JOIN syntax - http://dev.mysql.com/doc/refman/5.5/en/join.html

Code: Select all

SELECT id_cat, link_cat,link_name
FROM link_categories 
INNER JOIN link_urls ON link_urls.cat_id=link_categories.id_cat
ORDER by link_cat_ord

Olala me like :D

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Sun Feb 13, 2011 3:51 am
by Darhazer
The problem in your code most likely is that you override the $row variable
use another variable in the inner loop

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Thu Feb 24, 2011 2:42 pm
by DedMousie
Eran wrote:You need to learn about JOIN syntax
I *do* understand JOINS. That's not the problem. My query is returning the expected data. The problem is formatting the output.

Let's say I've got Categories, and items. When displaying it I need:

CATEGORY 1
category 1 stuff 1
category 1 stuff 2
category 1 stuff 3

CATEGORY 2
category 2 stuff 1
category 2 stuff 2
category 2 stuff 3

This is stupid easy in Coldfusion as previously shown. Using a single query with a JOIN, you can just nest two "cfoutput" tags and tell the first tag to group by your "category".

I've still not found an elegant solution in PHP - other than to nest a second query inside the "while" statement of the first.

Re: Query/Loop questions - PHP Noob - Coldfusion Expert

Posted: Thu Feb 24, 2011 8:53 pm
by McInfo
With the query Eran posted, the display pseudo-code goes like this.

Code: Select all

- Set variable lastCategory to null.
- Get a row from the query result.
    - If the category is not the same as lastCategory,
        - print the category name, and
        - set lastCategory to row's category.
    - Print the item.
    - (Implied) Continue to next row.