show latest record of specific category

[newprog]

Hi, I want to show the only latest record entered for a specific category.


Here is my database structure.


-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 05, 2011 at 07:12 AM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `books`
--

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
`prod_id` int(11) NOT NULL AUTO_INCREMENT,
`prod_name` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
`prod_desc` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
`cat_id` int(11) NOT NULL DEFAULT '0',
`prod_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`prod_image` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '',
`reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`prod_avail` int(1) NOT NULL DEFAULT '0',
`prod_hot` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`prod_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=59 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`prod_id`, `prod_name`, `prod_desc`, `cat_id`, `prod_price`, `prod_image`, `reg_date`, `prod_avail`, `prod_hot`) VALUES
(55, 'Sony Vaio VGN', '32222222222221231054545897', 13, '56000.00', '1304551980_mini-Laptop-Sony-Vaio-Vgn.jpg', '0000-00-00 00:00:00', 2, 2),
(56, 'Dell Desktop', 'asdfasdfasasdfasd asdfasd asdf\r\n asdfasd\r\n asdfasdf\r\nafasdfsdf\r\nasfdasdfa\r\nasdfas', 2, '33000.00', '1304555654_desktop computer.jpg', '0000-00-00 00:00:00', 2, 2),
(57, 'ASUS LCD', 'do go ake g kawe ke galk e gkek keg lkake glkketh e kakghlke k gkeajke aklajga klkjkljf kjkljfe', 5, '12000.00', '1304555871_asus-mw221-lcd.jpg', '0000-00-00 00:00:00', 2, 2),
(58, 'SamSung', 'asdfadsfjasldfjlasdfkasjdflkasdjfl asdf\r\na\r\nsdf \r\nasdf \r\nasd f\r\nsadfa s\r\ndfs', 13, '75000.00', '1304556310_Samsung_Laptops.jpg', '0000-00-00 00:00:00', 2, 2);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;



code I am using is say:

$cat = "2";
$q = mysql_query( "SELECT * FROM products WHERE cat_id=$cat");

but it displays all the products for that specific category while I want to show the only latest one.

how would I do this?

Thanks.

[fastsol1]

$q = mysql_query( "SELECT * FROM products WHERE cat_id=$cat ORDER BY reg_date DESC LIMIT 1"); 

This is assuming that the reg_date column is what you are using to determine when the product was registered to the system. If so then this will sort the list by the reg_date in Descending order (meaning backwards) and LIMIT 1 will only get one record.