Query/Loop questions - PHP Noob - Coldfusion Expert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DedMousie
Forum Newbie
Posts: 12
Joined: Sat Feb 12, 2011 7:21 pm

Query/Loop questions - PHP Noob - Coldfusion Expert

Post 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?
s992
Forum Contributor
Posts: 124
Joined: Wed Oct 27, 2010 3:06 pm

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

Post 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.
mellowman
Forum Commoner
Posts: 62
Joined: Sat Nov 22, 2008 5:37 pm

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

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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
mellowman
Forum Commoner
Posts: 62
Joined: Sat Nov 22, 2008 5:37 pm

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

Post 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
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

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

Post by Darhazer »

The problem in your code most likely is that you override the $row variable
use another variable in the inner loop
DedMousie
Forum Newbie
Posts: 12
Joined: Sat Feb 12, 2011 7:21 pm

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

Post 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.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

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

Post 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.
Post Reply