Help with a query

Making it more efficent

[nfs2]

I have a site where members are able to upload pictures. I have a code that displays the last 4 uploaded pictures, but im afraid the code isnt very efficent. Here it is

<?php
 $result=mysql_query("SELECT * FROM gallery_pics ORDER BY pid DESC LIMIT 0, 1");
 while ($i = mysql_fetch_array($result)) {
 $pics = '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>';
 }
 $result2=mysql_query("SELECT * FROM gallery_pics ORDER BY pid DESC LIMIT 0, 2");
 while ($i = mysql_fetch_array($result2)) {
 $pics2 = '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>';
 }
 $result3=mysql_query("SELECT * FROM gallery_pics ORDER BY pid DESC LIMIT 0, 3");
 while ($i = mysql_fetch_array($result3)) {
 $pics3 = '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>';
 }
 $result4=mysql_query("SELECT * FROM gallery_pics ORDER BY pid DESC LIMIT 0, 4");
 while ($i = mysql_fetch_array($result4)) {
 $pics4 = '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>';
 }
 
?>
 
 <div>
 <?php echo $pics?>&nbsp;
 <?php echo $pics2?>&nbsp;
 <?php echo $pics3?>&nbsp;
 <?php echo $pics4?>&nbsp;
 </div>

As you can see it takes 4 seperate queries. Is there a way to do the same thing in a single query?

[dreamcatcher]

<div>
<?php

$result=mysql_query("SELECT * FROM gallery_pics ORDER BY pid DESC LIMIT 4");

while ($i = mysql_fetch_array($result)) {
echo '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>';
}

?>
</div>

dc

[nfs2]

Thanks, that does work but.. Is there a way to get a variable for each picture?

see, the way i have it, i can position any of the pictures any way i want. Your code just spews out a clump of 4 pics (although it does it very efficently).. lol

Another reason id want a variable for each picture is that i'll need this same code on another page to display the last 30 pics and i'll need 3 rows of that. I can't make rows with your code, but i can with mine..

So, is there any way to do the same thing as my code, but in a single query?

[dreamcatcher]

Why not assign the piccies to an array?

$pics = array();

while ($i = mysql_fetch_array($result)) {
$pics[] = '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>';
}

dc

[nfs2]

Hi. Thanks for your patience in helping me with this problem

I havent used an array because im not sure how. For example, the code below outputs nothing

$result=mysql_query("SELECT * FROM gallery_pics ORDER BY pid DESC LIMIT 6");
$pics = array();
while ($i = mysql_fetch_array($result)) { 
$pics[] = '<a href="http://www.example.com/albums/'.$i['username'].'/'.$i['file'].'"><img src="http://www.example.com/albums/'.$i['username'].'/resize/'.$i['file'].'?resize(90)"/></a>'; 
}

and if i try to echo $pics[] i get an error saying i cant use [] for reading.

[tomda]

Use print_r to echo an array.

To echo a value from the array, do:
echo $print[0];
echo $print[1];

To loop in your array, use foreach()

[nfs2]

Ah ok i get it now! Thanks for the help

[tomda]

Making it more efficient!

I forgot also to tell you that because you only need "username" and "file", your query should looks like this:
$result=mysql_query("SELECT username, file FROM gallery_pics ORDER BY pid DESC LIMIT 6");