从用户 SQL 中选择第一个图像 [英] Select first image from user SQL

问题描述

我有一个查询，显示来自用户的所有图像.(在这种情况下，user_id = 3.)

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.

这是我的 sql 查询:

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";

结果如下:

[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
            )

    )

我不知道如何在查询返回的第一张图片附近放置一个小图标.如果我还有一个字段，1 表示它是第一条记录，或者 1 表示它不是第一条记录，我想我可以做我想做的.

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.

顺便说一下，我的images表的字段如下:

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

• id
• user_id
• 图片
• 日期

是否可以在查询中再增加一个字段，在按日期排序时用1或0表示它是否是结果集中的最新记录?

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?

推荐答案

如果您在 PHP 中可以很容易地添加这个字段，那么让您的 SQL 数据库添加这个字段是很愚蠢的.

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

只需创建一个布尔变量，在循环的第一次迭代时将其设置为 false.

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
}

<小时>

如何识别数组中的第一项和最后一项

---

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
}

这篇关于从用户 SQL 中选择第一个图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！