Query nested in a while()

[NLT]

while($comp = mysql_fetch_assoc($company))
{ echo "company name: $comp['name'] <br>";
$c = mysql_fetch_assoc(mysql_query("SELECT * FROM comp_images WHERE company='". $comp['name'] ."'"));
echo $c;
}

 

When I use that, it only echos one image URL, none others.

[Jessica]

You should be doing a join, not queries in a loop.

Example:

 

SELECT comp_images.*, company.*

FROM comp_images

LEFT JOIN company

ON company.company_id = comp_images.company_id

[NLT]

But how would I echo them out on a different line etc?

[NLT]

Nevermind - I understand it now.

 

But it is still only displaying one.

[xyph]

Post your code

[NLT]

$company = mysql_query("SELECT comp_images.*, company.* FROM comp_images LEFT JOIN company ON num > 2");
while($comp = mysql_fetch_assoc($company))
{ echo "company name: $comp['name'] <br>";
$c = mysql_fetch_assoc(mysql_query("SELECT * FROM comp_images WHERE company='". $comp['name'] ."'"));
echo $c;
}

[Jessica]

Don't do the query inside the loop!

[NLT]

Don't do the query inside the loop!

 

Okay, I've done that.

 

Now it does display that..

but I'm not sure how I can split them up.

 

It's now saying: image,companyname

image,companyname

(or vice versa)

 

But I want it to be like:

companyname

imageimageimageimage

[Jessica]

You can either do two queries (not in a loop, but two separate single queries) to get the list of companies and the list of all images sorted by country, OR you can do this (which is how I'd do it)

 

In the join the ON needs to represent the relationship between company and comp_images. There should be a unique ID for each company.

 

<?php
$sql = mysql_query("SELECT comp_images.*, company.* FROM comp_images LEFT JOIN company ON company.id = comp_images.company_id ORDER BY company.name");
$last_company_id = 0;
while($comp = mysql_fetch_assoc($sql))
{ 
    if($comp['id'] != $last_company_id){
        echo "company name: $comp['name'] <br>";
        $last_company_id = $comp['id'];
    }
   echo $comp['image_info_here'].'<br>';
}
?>

[Psycho]

EDIT: jesirose beat me to it, but I'll post this nonetheless since I don't like seeing queries written inside the mysql_query() function as it makes debugging more difficult

 

You didn't show your current code, so this is only a guess based upon the table fields

$query = "SELECT comp_images.image, company.name
          FROM company
          LEFT JOIN comp_images
            ON company.id = comp_images.company";
$result = mysql_query($query) or die(mysql_error());

$company = false;
while($row = mysql_fetch_assoc($result))
{
    if($company != $row['name'])
    {
        $company = $row['name'];
        echo "<br><b>{$company}</b><br>\n";
    }
    echo $row['image'};
}

[NLT]

How would I go about two queries?

 

If I use SELECT on them, how would I make it loop without in a while? Or would I make it into a while loop?

 

I'm not sure how I would make it into a while loop though, because of the mysql_fetch_assoc for the while condition.

 

EDIT: Explanation why: I'm not really a big fan of the LEFT JOIN and things, I'd prefer use SELECT statements if you know what I mean.

 

It may be because I'm not the biggest under-stander of them..

[xyph]

A LEFT JOIN is still a SELECT.

 

If you plan on using a relational database, I'd suggest starting to get comfortable with JOINs. They're very powerful, and efficient.

[NLT]

A LEFT JOIN is still a SELECT.

 

If you plan on using a relational database, I'd suggest starting to get comfortable with JOINs. They're very powerful, and efficient.

 

Then the code you gave me still does the same. It displays x and then y straight after, and repeats it until it's done.

[xyph]

I didn't give you any code. Everything posted above will behave the way you want it to, but it might not work by just copying it in to your code.

 

What is x? What is y?

 

Post some code, please. You've probably implemented it incorrectly.

[Jessica]

I don't like seeing queries written inside the mysql_query() function as it makes debugging more difficult

 

I very much agree, great point.

[NLT]

Got it working, thanks all.