Select first image from user 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 record, 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?

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

It is possible to add SQL to do this, but I think it would be simpler to add a boolean isFirst in your PHP code and modify it while you loop through the result set. If you had posted the code where you parse the SQL result I'd have been able to post a more specific answer. — Mark Byers
– Mark Byers, Commented Jan 30, 2012 at 0:36
I can not post any code because I return query to array you can see it and print_r. I do not know how to do next step I mean search first photo. Making foreach ($data as $key => $value) { } how can I do isFirst? — Viktors
– Viktors, Commented Jan 30, 2012 at 1:10

Kane Wallmann · Accepted Answer · 2012-01-31 01:42:54Z

3

It would be silly to make your SQL database add this field when you can do it very easily in PHP.

Just create a boolean variable that is set to false on the first iteration of your loop.

$first = true;

for( $data as $key => $value )
{
    if( $first )
    {
        echo "I'm the first row";
        $first = false;
    }

    // Do other stuff
}

EDIT: How to identify first and last item in an array

$keys = array_keys( $data );
$values = array_values( $data );

$count = count( $data );

for( $a = 0; $a < $count; $a++ )
{
    $key = $keys[ $a ];
    $value = $values[ $a ];

    if( $a == 0 )
    {
        // First
    }
    else if( $a+1 == $count )
    {
        // Last
    }

    // All
}

edited Jan 31, 2012 at 1:42

answered Jan 30, 2012 at 2:45

Kane Wallmann

2,32215 silver badges10 bronze badges

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

3 Comments

Viktors Over a year ago

as I understand it give first row from my result example but how to get last row ?

Mark Byers Over a year ago

@ViktorsGolubevs: Why do you need to know which is the last row? Your question only says you need the first row. Can you please be more specific about what you are trying to do?

Kane Wallmann Over a year ago

Updated answer to include identifying last item

Matthew Dally · Accepted Answer · 2012-01-30 02:46:37Z

0

Do you mean you want the first image that appears in the array or the first image with the smallest id?

The first image in the array returned in print_r should be accessible using

$images_list[0]['image'].

Does this not work?

If you wanted to find the first image in the array using a foreach statement, you could add a flag before the loop, like:

$firstImage = 0;    
foreach ($images_list as $key)     
{    
  $firstImage++;    
  if (firstImage == 1 )     
  {    
    $imageSrc = $key['image'];    
  }    
}

Please note the code above has not been tested, just a guide.

answered Jan 30, 2012 at 2:46

Matthew Dally

4121 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select first image from user SQL

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related