Help with 'recently uploaded pictures'

[Markus]

I'm doing a little project (for my own benefit) which results in an image host.

I would like to make a section on the main page for a recently uploaded section.

I have access to php and MySQL.

Anyone got any ideas?

Thanks a bunch in advance.

[pbmods]

Heya, Markus.

I'm assuming that you are storing either image files or just the paths to these files in the database (either is an acceptable solution).

Simply add a TIMESTAMP field to your images table:

ALTER TABLE
        `Ext_ImageUploads`
    ADD
        `Util_Uploaded`
            TIMESTAMP
            NOT NULL
            DEFAULT CURRENT_TIMESTAMP
            ON UPDATE CURRENT_TIMESTAMP,
    ADD
        KEY
            (`Util_Uploaded`)
 

The cool thing about the TIMESTAMP type is that, when you set its ON UPDATE CURRENT_TIMESTAMP method, MySQL will automatically set this value to the current date/time whenever you add a row.

So you might not have to change your upload script at all!

Then, to find the most recent uploads:

SELECT
        *
    FROM
        `Ext_ImageUploads`
    WHERE
        DATE_SUB(NOW(), INTERVAL 7 DAY) < `Util_Uploaded`
    ORDER BY
        `Util_Uploaded` DESC
    LIMIT 10;
 

This query will show the latest 10 uploads within the last week.

[Markus]

Heya, Markus.

I'm assuming that you are storing either image files or just the paths to these files in the database (either is an acceptable solution).

Simply add a TIMESTAMP field to your images table:

ALTER TABLE
        `Ext_ImageUploads`
    ADD
        `Util_Uploaded`
            TIMESTAMP
            NOT NULL
            DEFAULT CURRENT_TIMESTAMP
            ON UPDATE CURRENT_TIMESTAMP,
    ADD
        KEY
            (`Util_Uploaded`)
 

The cool thing about the TIMESTAMP type is that, when you set its ON UPDATE CURRENT_TIMESTAMP method, MySQL will automatically set this value to the current date/time whenever you add a row.

So you might not have to change your upload script at all!

Then, to find the most recent uploads:

SELECT
        *
    FROM
        `Ext_ImageUploads`
    WHERE
        DATE_SUB(NOW(), INTERVAL 7 DAY) < `Util_Uploaded`
    ORDER BY
        `Util_Uploaded` DESC
    LIMIT 10;
 

This query will show the latest 10 uploads within the last week.

Thanks a bunch man! :D

[pbmods]

Heya, Markus.

I understand you're having difficulties displaying the recently uploaded images. What seems to be the trouble?

[Markus]

Heya, Markus.

I understand you're having difficulties displaying the recently uploaded images. What seems to be the trouble?

MySQL isn't my strong point.

What i can't do now, is have the images displayed.

I'm not sure how i would go about getting the stored url aswell as ordering it with the timestamp-thingy-magig.

Does that make sense? Lol, i'm sorry.

[pbmods]

Heya, Markus.

Are you storing the image files in the database, or are they stored on the filesystem and you're storing the file names in the database?

[Markus]

Heya, Markus.

Are you storing the image files in the database, or are they stored on the filesystem and you're storing the file names in the database?

Yeh, i'm just storing the url to the images in the database and the actual images in a folder on the server.

:)

[pbmods]

Heya, Markus.

Good stuff.

Okay. You have two options:

• You can define 'recent' as being within the last x days.
• You can define 'recent' as being among the last x uploads.
• You can define 'recent' as being a combination of the previous two.

Three options. You have three options :)

The last one I demonstrated in my first post, and the two previous ones are merely the same thing, minus the LIMIT clause or minus the WHERE clause, respectively.

Once you run the query, it's a simple matter of extracting your file paths and outputting a bunch of HTML img tags.

[Markus]

Heya, Markus.

Good stuff.

Okay. You have two options:

• You can define 'recent' as being within the last x days.
• You can define 'recent' as being among the last x uploads.
• You can define 'recent' as being a combination of the previous two.

Three options. You have three options :)

The last one I demonstrated in my first post, and the two previous ones are merely the same thing, minus the LIMIT clause or minus the WHERE clause, respectively.

Once you run the query, it's a simple matter of extracting your file paths and outputting a bunch of HTML img tags.

Well, you see, this is my problem. I'm not sure how i would write the code.

You might give me an example and i could fill in my own info?

It'd be much appreciated.