SQL - Mostrar la ultima fecha de un registro desde otra tabla

Ok no tengo mucho tiempo trabajando con SQL asi que quizas mi codigo no este muy optimizado. Tengo 2 tablas una para productos y otra tabla de fechas puesto que cada producto tiene lotes de fechas diferentes
tengo mi tabla productos asi:

idproducto ---- Nombre --- Vendedor ---- cantidad disponible ---- ultimo lote
auto increase descripcion del producto cuantos producto me quedan No se como declararlo

y tengo la tabla de lotes asi:

idfecha ----- fecha--- idproducto
auto increase fecha del lote producto guardado

cada producto tiene n cantidad de lotes por lo que en vez de tener:

producto1 ------ lote06/apr/2017
producto2 ------ lote12/oct/2017
producto1 ------ lote27/dic/2017
producto1 ------ lote15/feb/2018
producto3 ------ lote07/apr/2018
producto4 ------ lote07/apr/2018
producto2 ------ lote22/apr/2018

quisiera que mostrara solo la ultima fecha o la fecha de lote mas reciente:

producto1 ------ lote15/feb/2018
producto2 ------ lote22/apr/2018
producto3 ------ lote07/apr/2018
producto4 ------ lote07/apr/2018

tengo 12mil registros en la tabla fechas, y 678 productos en la tabla productos. no se como llenar en la tabla productos la columna ultimo lote con la ultima fecha registrada, por ahora para mostrar la tabla tengo esto: y obviamente la fecha solo muestra un 0 he intentado copiar la secuencia pero usando la tabla fecha en vez de product pero no logro que me la muestre. que estoy haciendo mal? Gracias de antemano

<?php
try {
require ("db.php");
$stmt = $conn->prepare("SELECT * from product ORDER BY nombre");
$stmt->execute();
while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";

echo "<td align='center'> ".$row['nombre']." </td>";

mysql_connect("localhost", "root", "base") or
die("Could not connect: " . mysql_error());
mysql_select_db("lista");
$vendorid = $row['marca'];
$sql1 = "SELECT marca FROM vendedores WHERE idvendor=" . $vendorid;
$result1 = mysql_query($sql1);
while($row1 = mysql_fetch_array($result1)) {
echo "<td align='center'> ".$row1['marca']." </td>";
}
echo "<td align='center'> ".$row['disponible']." </td>";


echo "<td align='center'> ".$row['fecha]." </td>";
echo "</tr>";
}
}
catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}
?>

Hola Andy:

Aquí hay varias cosas que tienes que considerar:

1. ¿Cómo determinas cuál es el último lote para cada producto?... es decir, dices que tienes esto en tu campo:


¿qué significado tiene esto para tí? porque para mi es simplemente una cadena, pero no sé qué formato tiene.... si estamos hablando del 15 de febrero de 2008 como fecha, o del lote 15 con fecha febrero de 2018. esa sería la primer dificultar, pues creo suponer que esto lo guardas en un campo tipo TEXTO y esto es un terrible error de diseño.

2. Cuando un campo PUEDE SER CALCULADO (como en este caso), entonces NO HAY NECESIDAD DE GUARDARLO EN LA BASE DE DATOS. En otras palabras, en lugar de tratar de ver cómo actualizas el campo de tu tabla de productos, lo "ideal" es que elimines este campo, y si necesitas mostrarlo entonces, mediante una consulta CALCULAS el valor que le corresponde. Esto para mí sería lo mejor,

3. Si de plano necesitas que el campo se actualice en tu tabla de productos, entonces tienes que hacer un TRIGGER (Disparador) cada que INSERTES o ACTUALICES un registro de tu tabla de LOTES. En este TRIGGER lo que harías sería CALCULAR el último lote que le corresponde a cada producto y ACTUALIZAR dicho registro en la tabla de productos.

4. Si necesitas actualizar la información que ya tienes en tu tabla de productos, entonces primero tendrías que armar una consulta con JOIN's para obtener qué lote le pertenece a cada producto y después convertir esa consulta en un UPDATE.

Saludos
Leo.

Gracias Leo,

Efectivamente tengo un "terrible error de diseño" pero no tenia otra opcion puesto que los dueños quieren exclusivamente el lote con la fecha, asi que tenemos lote+fecha guardada en un texto y eso es algo que tienen haciendo desde hace años y no se de verdad como repararlo porque son demasiados registros.

trabajando con la tabla sola puedo organizar todas las fechas y ver la lista de productos que fueron hechos en esas fechas, pero para la tabla principal que fue la que mostre, no consigo como mostrar la fecha mas reciente buscandola en la tabla que la contiene
el producto1 tiene 37 fechas, osea 37 lotes diferentes, pero solo me interesa saber que la ultima fecha en la que se agrego un lote fue el 15/feb/2018, para ver los otros 36 registros solo los listo en el reporte pero es engorroso listar un reporte completo para poder ver la fecha mas reciente, el producto 9 por ejemplo tiene 173 fechas, y tengo que llamar las 173 fechas para poder ver la mas reciente,
estoy indagando con lo del join y no se si hacerlo en un nuevo php o el php principal, sigo buscando...

he intentado cambiar el campo de la tabla fecha a string o varchar, o solo date y ha sido todo un desastre, de verdad estoy con el frustrometro a mil

cualquier luz al final del tunel es humildemente apreciada

Hola de nuevo Andy:

¿Con qué BD's estás trabajando? este dato es muy importante porque cada Motor trabaja con una sintaxis y funciones distintas para el manejo de fechas, Pudiera pensar que trabajas con MySQL, pero eso sólo Dios y tú lo saben.

tal como lo dices, el principal problema es convertir tu cadena a fecha, porque para obtener el ultimo lote para cada producto debes de usar la función MAX pero no es lo mismo trabajar con cadenas a fechas o números.

suponiendo que los últimos caracteres de tu campo texto correspondan a la fecha, entonces tendriamos que algunos ejemplos de tus fechas serían:


La mayoría de los DBMS's (si no es que todos) trabajan las fechas con el formato ANSII estándar, es decir YYYY-MM-DD... por lo que te recomendaría que transformaras las cadenas de arriba en una cadena con el formato que te comento... Creo que la parte más complicada sería el MES, ya que no sé si los estás poniendo como abreviaciones en inglés o alguna otra nomenclatura. Podrías en todo caso crear una función para que te haga la transformación y las validaciones pertinentes.

Ve por partes... por lo pronto trata de hacer la función que convierta fechas y después seguimos con la otra parte de la consulta, que en realidad es algo muy simple.

Saludos
Leo.

Saludos Leo,

Ya consegui resolver el problema, si estoy trabajando con MySQL y termine resolviendo hacer una busqueda y llamando en vez del campo de la tabla a mi dato de "pantalla" por darle un nombre. ya que esta es una empresa pequeña que tiene años haciendo las cosas de una manera son un poco renuentes a cualquier tipo de cambio, asi que lo que hice fue maquillar el campo de texto que tienen para los lotes y como el id es incremental meti ese id en una busqueda despues de identificar al producto asi:

$sql3 = "SELECT * FROM fecha WHERE id_fecha IN(SELECT MAX(id_fecha) FROM lotes)";

el id mas alto es a juro y porque si la fecha mas reciente asi que me vale. No es la solucion mas ortodoxa pero funciona y pues asi se quedo, ellos pueden seguir guardando su text del modo que quieren pero a terminos de pantalla yo solo voy a mostrar la entrada mas reciente independientemente del producto que se escanee. asi que en fin, funciona!!!!

gracias de todos modos por tratar de ayudarme de verdad lo aprecio ha sido tortuoso jeje.

buen dia!