Usuarios, imágenes y fechas

[J_Sparrow]

Buenas, una de mis locuras, igual es mejor que me digáis desde el principio "no se puede", antes que os vuelva locos

Estoy usando la extensión IMGUR de @AbaddonOrmuz (que por cierto, es impresionante) para almacenar las imágenes que hay en mi Foro y las que se publiquen a partir de ahora. Claro, todas las imágenes las guarda IMGUR en UNA carpeta, con lo que no se de quien es la imagen en concreto. Llevo como tres días intentando buscar una determinada imagen en IMGUR y o soy mas tonto que el mecanismo de un chupete, o no se como narices se usa el buscador de IMGUR, porque no he podido localizar nada, ni una puñetera coma.
A lo que voy, se me ha ocurrido usar la Base de Datos y algo mas o menos me ha preparado mi hijo, pero no es lo que quiero y el no sabe llagar mas allá. Quiero que a través de una consulta, buscar en la tabla de los mensajes (post) todo lo que esté encerrado entre los comandos [img] y [/img], junto con el autor de esa respuesta y la fecha. Hasta ahora he llegado a esto:

Código: Seleccionar todo

SELECT u.username, p.poster_id, p.post_time, p.post_text FROM `phpbb_posts` as p inner join phpbb_users as u on p.poster_id = u.user_id WHERE post_text like '%[img]%[/img]%'

... y funciona bien (excepto la conversión de la fecha, que mi hijo no ha sabido convertirla), pero me extrae TODO el contenido del mensaje, no solo el enlace.
¿Veis factible que se pueda hacer?, ¿vamos por buen camino?, ¿o me olvido del tema?.

Es que soy un poco "maniático" con algunas cosas.

Si tenéis otra idea mas simple, o hay alguna extensión que haga algo parecido (cosa que no he visto), soy todo oidos

Gracias.

[picaron]

Hola @J_Sparrow.

El valor de cada post_time que obtengas de la consulta a la base de datos, podría ser algo así:

$row['post_time']

Para convertir a fecha en formato "legible", incluye esta linea de PHP:

$fecha = date('d/m/Y H:i', $row['post_time']);

O bien puedes utilizar el core de PHPBB: (Aunque con esta solución creo que te vas a complicar el "body")

$fecha = $user->format_date($row['post_time']);

Saludos.

[AlfredoRamos]

Puedes intentar la siguiente consulta:

Código: Seleccionar todo

SELECT
	p.post_id,
	u.username,
	FROM_UNIXTIME(p.post_time, '%Y-%m-%d %T') AS fecha,
	REGEXP_SUBSTR(p.post_text, 'https?:\\/\\/i\\.imgur\\.com[a-zA-Z0-9\\/_\\.]+') AS imagen
FROM phpbb_posts AS p
INNER JOIN phpbb_users AS u
	ON p.poster_id = u.user_id
WHERE
	p.post_text REGEXP '\\[img\\].*(https?:\\/\\/i\\.imgur\\.com[a-zA-Z0-9\\/_\\.]+).*\\[\\/img\\]'
HAVING imagen IS NOT NULL
ORDER BY p.post_time DESC;

Si obtienes muchos resultados, puedes agregar un límite:

Spoiler

Código: Seleccionar todo

SELECT
	p.post_id,
	u.username,
	FROM_UNIXTIME(p.post_time, '%Y-%m-%d %T') AS fecha,
	REGEXP_SUBSTR(p.post_text, 'https?:\\/\\/i\\.imgur\\.com[a-zA-Z0-9\\/_\\.]+') AS imagen
FROM phpbb_posts AS p
INNER JOIN phpbb_users AS u
	ON p.poster_id = u.user_id
WHERE
	p.post_text REGEXP '\\[img\\].*(https?:\\/\\/i\\.imgur\\.com[a-zA-Z0-9\\/_\\.]+).*\\[\\/img\\]'
HAVING imagen IS NOT NULL
ORDER BY p.post_time DESC
LIMIT 0,150;

phpbb_posts.post_time es un dato numérico (tiempo UNIX), son segundos, para crear un formato a partir de eso puedes usar la función FROM_UNIXTIME()

Para obtener únicamente la URL de la imágen, puedes usar REGEXP_SUBSTR() que regresará el texto que coincida con al expresión regular. En tu caso las imágenes de Imgur tienen el siguiente formato:

Código: Seleccionar todo

https://i.imgur.com/{HASH}.{EXTENSION}

La condición en WHERE hará que sólo busque en posts donde uses [img] Y que además tenga una URL de Imgur, ya que si unicamente se buscara donde se usa [img] podrías obtener imágenes de otros servidores, que no te interesan.

Obtendrá los posts más recientes primero.

[J_Sparrow]

Impresionante ambos, habéis sido mas rápidos que yo haciendo la pregunta. Ahora a probar los buenos y sabios consejos.

La condición en WHERE hará que sólo busque en posts donde uses [img] Y que además tenga una URL de Imgur, ya que si unicamente se buscara donde se usa [img] podrías obtener imágenes de otros servidores, que no te interesan.

¿Y si quiero incluir lo que no sea de IMGUR, o mejor aún, que me muestre todo menos lo de IMGUR?

@AbaddonOrmuz , ¿tu tienes que dedicarte a esto en tu vida cotidiana, no?, porque si lo haces como hobby, santo dios lo que se están perdiendo en "otro lado".

Gracias chicos, a ver si termino lo que tengo entre manos y antes de irme a dormir, lo pruebo.

[AlfredoRamos]

Y si quiero incluir lo que no sea de IMGUR, o mejor aún, que me muestre todo menos lo de IMGUR?

Mmmm...

Para obtener otras imágenes sin importar de donde sean:

Código: Seleccionar todo

SELECT
	p.post_id,
	u.username,
	FROM_UNIXTIME(p.post_time, '%Y-%m-%d %T') AS fecha,
	REGEXP_SUBSTR(p.post_text, 'https?:\\/\\/([a-zA-Z0-9\\/_\\.\\-?&=])+') AS imagen
FROM phpbb_posts AS p
INNER JOIN phpbb_users AS u
	ON p.poster_id = u.user_id
WHERE
	p.post_text REGEXP '\\[img\\].+\\[\\/img\\]'
HAVING imagen IS NOT NULL
ORDER BY
	p.post_time DESC;

Para obtener imágenes excepto de Imgur:

Código: Seleccionar todo

SELECT
	p.post_id,
	u.username,
	FROM_UNIXTIME(p.post_time, '%Y-%m-%d %T') AS fecha,
	REGEXP_SUBSTR(p.post_text, 'https?:\\/\\/([a-zA-Z0-9\\/_\\.\\-?&=])+') AS imagen
FROM phpbb_posts AS p
INNER JOIN phpbb_users AS u
	ON p.poster_id = u.user_id
WHERE
	p.post_text REGEXP '\\[img\\].+\\[\\/img\\]'
AND
	p.post_text NOT REGEXP '\\[img\\].*(https?:\\/\\/i\.imgur\\.com[a-zA-Z0-9\\/_\\.]+).*\\[\\/img\\]'
HAVING imagen IS NOT NULL
ORDER BY
	p.post_time DESC;

Las sentencias, y en particular las expresiones regulares, podrían mejorarse pero depende mucho de lo que necesites exactamente.

¿tu tienes que dedicarte a esto en tu vida cotidiana, no?

Si aunque no en SQL, ni siquiera en PHP

Esto lo veo como retos, para seguir aprendiendo

[J_Sparrow]

Gracias @AbaddonOrmuz , no quiero abusar mas de ti hasta que haga unas pruebas, que creo serán mas que suficientes.
A ver, lo que quiero es un simple capricho, no es ninguna necesidad, me gustaría saber quien publicó una imagen determinada, por si un día tengo que reponerlas. Ya me ha pasado en 4 o 5 ocasiones, que debido a cierres de servidores de imágenes, he tenido que reponer muchas imágenes. Mi Foro se basa muco en trabajos que hace la gente y en problemas que exponen con imágenes para explicarse. Si las imágenes desaparecen, carecen de sentido los post. Con el primer "susto" que me llevé, Imageshack dejó de ser gratuito y limitó el espacio gratuito, con lo que me eliminó muchas imágenes que habían subido los usuarios a sus cuentas. A partir de ahí hice copias de todas las imágenes publicadas y siempre las tengo a mano para reponerlas, como ahora me está pasando con Tinypic. Es el problema de depender de terceros, que si cierran te dejan con el culo al aire.
Con esta solución pretendo tener un listado de las imágenes y autor, luego solo tengo que ir buscando donde están y reponerlas.

Ya te contaré mis resultados. Muchas gracias otra vez, como siempre.

[karbiko]

como ahora me está pasando con Tinypic

A mi me cogió Tinypic con todas las imágenes subidas a su server y no supe que cerraban hasta que fue demasiado tarde.
Luego intenté IMGUR, pero no supe activarlo bien y vi que estaba en desarrollo la ext IMAGE UPLOAD de dmzx y martin y que sube las imágenes a tu propio servidor.
Evidentemente, si no llevas control y todo quisqui sube indiscriminadamente se comen el tamaño de tb bbdd.
Pero como las imágenes no es algo que se publique en todos los temas de mi foro, por ahora va superbien... y espero que de esta manera ya no pierda ninguna imagen más.

Me hubiera hecho falta conocer este código que han puesto para "rescatar" todas esas fotos que tenía en su momento en el jodío tinypic

[J_Sparrow]

Buenas noches.
Tenía pendiente probar este tema y lo acabo de hacer.
@AbaddonOrmuz
ESPECTACULAR, no tiene otro nombre.
He probado la opción de solo IMGUR y es IMPRESIONANTE y luego la de todas las imágenes, buff, MARAVILLOSA
No ha tardado apenas un segundo y me ha listado unos 2000 imágenes la primera opción y unos 1600 la segunda opción. Me gusta mucho, porque además lo has clavado, sale el número del tema donde está la imagen, el nombre del usuario, la fecha con la hora y la dirección de la imagen, información MUY interesante para mi. Fíjate si será interesante, que me ha "cantado" algunos errores que no los podría descubrir si no revisado mensaje a mensaje. Y solo revisando un poco por encima.

Es mas de lo que necesitaba y muy simple. Gracias, me guardo esta información como un tesoro.

[J_Sparrow]

y me ha listado unos 2000 imágenes la primera opción y unos 1600 la segunda opción.

Ya se me estaba haciendo raro que en la segunda opción (todas las imágenes), hubiese menos que en la primera, no son 1600, son 16000, que me he comido un "cero".
Pero bueno, lo importante es que la consulta funciona a las mil maravillas y lo mas importante, con ella aprenderé a usarla para mas situaciones. ¿Tal vez pueda modificarla para extraer un listado de las imágenes adjuntas?, tengo que estudiar si puedo buscar esto [attachment=0]PORTAL-24.jpg[/attachment]por ejemplo, pero eso ya veremos si soy capaz de hacerlo. Si, ya se que voy al PCA y que ahí están todas, pero no las puedo gestionar de la misma manera.
Por cierto, según escribía esto, he mirado a ver como esta la info y acabo de ver que hay cosas que me pueden interesar, los adjuntos que no son imágenes, ahí están todos y los puedo localizar para ir subiéndolos a mi cuenta de Mega. Interesante, ya me he vuelto a poner mucho mas trabajo
Y como estas cosas, algunas mas, ya me las plantearé. Al final descubriréis que soy un poco "friki" y que me gustan unos datos un tanto peculiares. Si vieseis el listado que estoy construyendo a mano uno a uno con los adjuntos, flipabais, me llamaríais LOCO