Alternative to nested queries?

[dj-kenpo]

I'm still learning so excuse me if this is a silly question.

 

I'm wondering if there is an alternative, IE faster way of doing or avoiding all together nested sql queries.

 

example:

 

my blog script uses a while loop and fetches 10 entries. on each of those 10 entries it then does ANOTHER sql query + while loop for any images attached in the relational database (ie one blog entry might have 1 or 9 images attached)

 

that's now 11 queries minimum, not including comment number on each entry, now we're up to 21 queries per page load.

 

there's got to be a more efficient way

[MadTechie]

family table

[/td]

Position

Age

Dad

41

Mom

45

 

 

food Table

Meal

Age

Steak

Dad

Mom

 

SELECT food.Meal, family.Position

FROM family, food 

WHERE food.Position = family.Position

 

or use Joins

[dj-kenpo]

Thanks for the reply, but unless I'm mistaken that won't work.

 

"SELECT food.Meal, family.Position

FROM family, food

WHERE food.Position = family.Position"

 

won't help me if there's 40 images attached, or am I wrong? (I accept that I very well may be) it looks like it will only get the last image. or put in other terms, the last element in the nested query.

[MadTechie]

how are your tables setup ?

 

this is what i would do..

 

member table

ID UserName

1 Test1

2 Test2

 

Images Table

ImagePath UID

/images/test1.jpg 1

/images/test2.jpg 2

/images/test3.jpg 1

/images/test4.jpg 1

/images/test5.jpg 4

 

SELECT Images.ImagePath

FROM Images, member

WHERE member.ID= Images.UID

AND member.UserName = 'Test1'

 

result

/images/test1.jpg

/images/test3.jpg

/images/test4.jpg

 

 

 

*untested

 

[dj-kenpo]

what I'm trying to ask (and I appologize if I'm not getting this accross...) is how to get this:

 

 

table blog entries

id | title | entry

2 | entry 01 | body text

3 | entry 02 | body text

 

table blog images

rel_id | image

2 | aa.jpg

2 | bb.jpg

2 | cc.jpg

3 | dd.jpg

 

 

what I'd like is one query that grabs all that data into the result:

 

entry o1

body text

- aa.jpg

- bb.jpg

- cc.jpg

 

entry 02

body text

- dd.jpg

 

-------------

rather than calling

query blog entries

while result

{

get id

print blog title

print blog entry

----query for images with rel id = blog id

{print images}

}

 

it's just alot of nested queries once you add comments + more entries

[MadTechie]

So

 

SELECT entries.entry, images.image
FROM entries, images
WHERE entries.id = images.rel_id

 

can you post a little code if this doesn't help !

[dj-kenpo]

I'm just not seeing how that query will give me 10 blog entires and then 20 images. won't it just give me ONE image per blog entry?

 

or does it return images.image as an array containing the 5 images?

[MadTechie]

No

using the example date

table blog entries

id | title | entry

2 | entry 01 | body text

3 | entry 02 | body text

 

table blog images

rel_id | image

2 | aa.jpg

2 | bb.jpg

2 | cc.jpg

3 | dd.jpg

 

it would give you this

 

entry image

body text1 aa.jpg

body text1 bb.jpg

body text1 cc.jpg

body text2 dd.jpg

 

so maybe use

SELECT entries.id, entries.entry, images.image
FROM entries, images
WHERE entries.id = images.rel_id

 

returns

id entry image

2 body text1 aa.jpg

2 body text1 bb.jpg

2 body text1 cc.jpg

3 body text2 dd.jpg

 

remember this is just 1 query, you could aways create a loop to then build this into a array..

 

 

[Barand]

Try

 

<?php
$sql = "SELECT b.id, b.title, b.entry, i.image
        FROM blog_entries b
        INNER JOIN blog_images i ON b.id = i.rel_id
        ORDER BY b.id";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

$prev = '';
while (list($id, $t, $e, $i) = mysql_fetch_row($res)) {
    if ($prev != $id) {
        echo "<br><strong>$t<br>$e</strong><br>";
        $prev = $id;
    }
    echo " - $i <br>" ;
}
?>