Get last image from query SQL

Question

I have a query which shows all images from a user. (In this case the user_id = 3.)

I want to return the results of the query and display near the user record, a static picture which I have uploaded. This picture is a small icon.

Here is my sql query:

$sql = "SELECT username as user, p.image as user_image, i.image, i.id as image_id, i.description as text, UNIX_TIMESTAMP(i.date) as image_date, COALESCE ( imgcount.cnt, 0 ) as comments
        FROM users u
        LEFT JOIN images i ON i.user_id = u.id
        LEFT JOIN images p ON p.id = (SELECT b.id FROM images AS b where u.id = b.user_id ORDER BY b.id DESC LIMIT 1)
        LEFT JOIN (SELECT image_id, COUNT(*) as cnt FROM commentaries GROUP BY image_id  ) imgcount ON i.id = imgcount.image_id
        WHERE i.user_id = 3
        ORDER BY i.date DESC";

And here are the results:

[images_list] => Array
    (
        [0] => Array
            (
                [user] => 3333
                [user_image] => http://127.0.0.1/auth_system_1/upload_images/24/24_nsm5rixy14lexm9cy15wzyg9u_224.jpg
                [image] => http://127.0.0.1/auth_system_1/upload_images/224/224_nsm5rixy14lexm9cy15wzyg9u_224.jpg
                [image_id] => 5
                [text] => 
                [image_date] => 7 hours, 1 minute
                [comments] => 2
            )

        [1] => Array
            (
                [user] => 3333
                [user_image] => http://127.0.0.1/auth_system_1/upload_images/24/24_nsm5rixy14lexm9cy15wzyg9u_224.jpg
                [image] => http://127.0.0.1/auth_system_1/upload_images/224/224_gfbyjh6zf66g914e28bsfdkuf_f4d.jpg
                [image_id] => 3
                [text] => 
                [image_date] => 20 hours, 50 minutes
                [comments] => 0
            )

        [2] => Array
            (
                [user] => 3333
                [user_image] => http://127.0.0.1/auth_system_1/upload_images/24/24_nsm5rixy14lexm9cy15wzyg9u_224.jpg
                [image] => http://127.0.0.1/auth_system_1/upload_images/224/224_80jrg3z0xrh9isskc3yuhtqh1_163.jpeg
                [image_id] => 1
                [text] => test1
                [image_date] => 1 day, 22 hours
                [comments] => 0
            )

    )

I do not have any idea how to place a small icon near the first image that is returned from the query. If I had one more field with a 1 indicating it is the first record, or a 1 indicating it is not the first upload image by user, I think I could do what I want.

By the way, the fields of my images table are as follows:

id
user_id
image
date

Is it possible to add one more field to the query, which will indicate with a 1 or 0 whether or not it is the latest record in the result set when sorting by date?

My goal is to pu icon near this image

    [2] => Array
        (
            [user] => 3333
            [user_image] => http://127.0.0.1/auth_system_1/upload_images/24/24_nsm5rixy14lexm9cy15wzyg9u_224.jpg
            [image] => http://127.0.0.1/auth_system_1/upload_images/224/224_80jrg3z0xrh9isskc3yuhtqh1_163.jpeg
            [image_id] => 1
            [text] => test1
            [image_date] => 1 day, 22 hours
            [comments] => 0
        )

Maybe you can show me an easier way to do this?

If I understand you correctly, you want help to print an icon next to the output where you print your images, but you don't include how you generate this output. — kba
– kba, Commented Jan 30, 2012 at 22:53
Is this much different than the last time you posted this question? stackoverflow.com/questions/9058102/… — Mike Christensen
– Mike Christensen, Commented Jan 30, 2012 at 22:54
In last my question I asked about first image, everything is ok. People shoe me function php and I get what I want. thank's them ! — Viktors
– Viktors, Commented Jan 31, 2012 at 2:20

kba · Accepted Answer · 2012-01-30 22:59:45Z

1

Since you don't include much information about how you process your array, I'll have to make some assumptions

Assuming the array you include in your question is called $image_list, then you can iterate over this list using a for loop. Once you reach the end of the loop, you just print your icon.

$length = sizeof($image_list);
for ($i = 0; $i < $length; $i++)
{
    if ($i == $length) print "<img src=\"icon-goes-here.png\" />\n";
    printf("<img src=\"%s\" />\n", $image['image']);
}

answered Jan 30, 2012 at 22:59

kba

19.5k6 gold badges65 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Viktors Over a year ago

is it possible to do it with foreach not for ? I suppouse in this case only for goes?

kba Over a year ago

You could use a foreach loop as well, but since you're depended on knowing which item number you're currently at, it wouldn't make sense to do.

Abdul Hfuda · Accepted Answer · 2012-01-30 23:22:27Z

0

Seems you want this all in SQL... here is one attempt goes:

Select *,DateDiff(ms,max,CreatTime)
From (Select user,max(image_date) as max 
     From YourQuery
     Group By user) as a
Join (Select *
      from YourQuery) as b on b.user=a.user

Although I think its more appropriate to do this pro grammatically after YourQuery's results are fetched rather than this double query mess.

There probably is also a better way to do it in one query without calling on yours twice. Cant think of anything at the moment though.

answered Jan 30, 2012 at 23:22

Abdul Hfuda

1,51312 silver badges25 bronze badges

Collectives™ on Stack Overflow

Get last image from query SQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related