wurong Posted June 11, 2012 Share Posted June 11, 2012 I am Alan from china,new come to your site,I have your books “Effortless E-Commerce with PHP and MySQL, i Received my copy from Amazon. I am using the second example "Coffee ", but i already add 3 new tables, u can see down computer computer_products mobile mobile_products shoes shoes_products carts customers general_coffees non_coffee_categories non_coffee_products orders order_contents sales sizes specific_coffees transactions wish_lists What i need exactly? I want that each product is displayed on one page , like computer page just computer_products, shoes_product page etc view example here its my database -- -- Database: `eco2` -- -- -------------------------------------------------------- -- -- Table structure for table `shoes` -- CREATE TABLE `shoes` ( `id` tinyint(9) NOT NULL AUTO_INCREMENT, `category` varchar(40) NOT NULL, `description` tinytext NOT NULL, `images` varchar(40) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `category` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Table structure for table `shoes_products` -- CREATE TABLE `shoes_products` ( `shoes_id` tinyint(8) unsigned NOT NULL, `id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, `description` tinytext, `image` varchar(45) NOT NULL, `price` decimal(5,2) unsigned NOT NULL, `stock` mediumint(8) unsigned NOT NULL DEFAULT '0', `etat` varchar(30) NOT NULL, `marque` varchar(50) NOT NULL, `date_usine` date NOT NULL, `commentaire` tinytext NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `shoes_id` (`shoes_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `shoes_products` -- -- -------------------------------------------------------- -- -- Table structure for table `mobile` -- CREATE TABLE `mobile` ( `id` tinyint(9) NOT NULL AUTO_INCREMENT, `category` varchar(40) NOT NULL, `description` tinytext NOT NULL, `images` varchar(40) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `category` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `mobile` -- -- -------------------------------------------------------- -- -- Table structure for table `mobile_products` -- CREATE TABLE `mobile_products` ( `mobile_id` tinyint(8) unsigned NOT NULL, `id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, `description` tinytext, `image` varchar(45) NOT NULL, `price` decimal(5,2) unsigned NOT NULL, `stock` mediumint(8) unsigned NOT NULL DEFAULT '0', `os` varchar(30) NOT NULL, `etat` varchar(30) NOT NULL, `marque` varchar(50) NOT NULL, `date_usine` date NOT NULL, `commentaire` tinytext NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `bureautique_id` (`mobile_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `mobile_products` -- -- -------------------------------------------------------- -- -- Table structure for table `computer` -- CREATE TABLE `computer` ( `id` tinyint(9) NOT NULL AUTO_INCREMENT, `category` varchar(40) NOT NULL, `description` tinytext NOT NULL, `images` varchar(40) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `category` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -- Dumping data for table `computer` -- -- -------------------------------------------------------- -- -- Table structure for table `computer_products` -- CREATE TABLE `computer_products` ( `computer_id` tinyint(8) unsigned NOT NULL, `id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(60) NOT NULL, `description` tinytext, `image` varchar(45) NOT NULL, `price` decimal(5,2) unsigned NOT NULL, `stock` mediumint(8) unsigned NOT NULL DEFAULT '0', `os` varchar(30) NOT NULL, `etat` varchar(30) NOT NULL, `marque` varchar(50) NOT NULL, `date_usine` date NOT NULL, `commentaire` tinytext NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `bureautique_id` (`computer_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Dumping data for table `computer_products` -- -- -------------------------------------------------------- -- -- Table structure for table `carts` -- CREATE TABLE `carts` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `quantity` tinyint(3) unsigned NOT NULL, `user_session_id` char(32) NOT NULL, `product_type` enum('coffee','other') NOT NULL, `product_id` mediumint(8) unsigned NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `product_type` (`product_type`,`product_id`), KEY `user_session_id` (`user_session_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `carts` -- -- -------------------------------------------------------- -- -- Table structure for table `customers` -- CREATE TABLE `customers` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(80) NOT NULL, `first_name` varchar(20) NOT NULL, `last_name` varchar(40) NOT NULL, `address1` varchar(80) NOT NULL, `address2` varchar(80) DEFAULT NULL, `city` varchar(60) NOT NULL, `state` char(2) NOT NULL, `zip` mediumint(5) unsigned zerofill NOT NULL, `phone` int(10) NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `email` (`email`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `customers` -- -- -------------------------------------------------------- -- -- Table structure for table `general_coffees` -- CREATE TABLE `general_coffees` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `category` varchar(40) NOT NULL, `description` tinytext, `image` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `type` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `general_coffees` -- -- -------------------------------------------------------- -- -- Table structure for table `non_coffee_categories` -- CREATE TABLE `non_coffee_categories` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `category` varchar(40) NOT NULL, `description` tinytext NOT NULL, `image` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `category` (`category`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `non_coffee_categories` -- -- -------------------------------------------------------- -- -- Table structure for table `non_coffee_products` -- CREATE TABLE `non_coffee_products` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `non_coffee_category_id` tinyint(3) unsigned NOT NULL, `name` varchar(60) NOT NULL, `description` tinytext, `image` varchar(45) NOT NULL, `price` decimal(5,2) unsigned NOT NULL, `stock` mediumint(8) unsigned NOT NULL DEFAULT '0', `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `non_coffee_category_id` (`non_coffee_category_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `non_coffee_products` -- -- -------------------------------------------------------- -- -- Table structure for table `orders` -- CREATE TABLE `orders` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_id` int(10) unsigned NOT NULL, `total` decimal(7,2) unsigned DEFAULT NULL, `shipping` decimal(5,2) unsigned NOT NULL, `credit_card_number` mediumint(4) unsigned NOT NULL, `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `order_date` (`order_date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `orders` -- -- -------------------------------------------------------- -- -- Table structure for table `order_contents` -- CREATE TABLE `order_contents` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_id` int(10) unsigned NOT NULL, `product_type` enum('coffee','other','sale') DEFAULT NULL, `product_id` mediumint(8) unsigned NOT NULL, `quantity` tinyint(3) unsigned NOT NULL, `price_per` decimal(5,2) unsigned NOT NULL, `ship_date` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `ship_date` (`ship_date`), KEY `product_type` (`product_type`,`product_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `order_contents` -- -- -------------------------------------------------------- -- -- Table structure for table `sales` -- CREATE TABLE `sales` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `product_type` enum('coffee','other') DEFAULT NULL, `product_id` mediumint(8) unsigned NOT NULL, `price` decimal(5,2) unsigned NOT NULL, `start_date` date NOT NULL, `end_date` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `start_date` (`start_date`), KEY `product_type` (`product_type`,`product_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `sales` -- -- -------------------------------------------------------- -- -- Table structure for table `sizes` -- CREATE TABLE `sizes` ( `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `size` varchar(40) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `size` (`size`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `sizes` -- -- -------------------------------------------------------- -- -- Table structure for table `specific_coffees` -- CREATE TABLE `specific_coffees` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `general_coffee_id` tinyint(3) unsigned NOT NULL, `size_id` tinyint(3) unsigned NOT NULL, `caf_decaf` enum('cafe','deca') DEFAULT NULL, `ground_whole` enum('moulu','grains') DEFAULT NULL, `price` decimal(5,2) unsigned NOT NULL, `stock` mediumint(8) unsigned NOT NULL DEFAULT '0', `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `general_coffee_id` (`general_coffee_id`), KEY `size` (`size_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `specific_coffees` -- -- -------------------------------------------------------- -- -- Table structure for table `transactions` -- CREATE TABLE `transactions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `order_id` int(10) unsigned NOT NULL, `type` varchar(18) NOT NULL, `amount` decimal(7,2) NOT NULL, `response_code` tinyint(1) unsigned NOT NULL, `response_reason` tinytext, `transaction_id` bigint(20) unsigned NOT NULL, `response` text NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `order_id` (`order_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `transactions` -- -- -------------------------------------------------------- -- -- Table structure for table `wish_lists` -- CREATE TABLE `wish_lists` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `quantity` tinyint(3) unsigned NOT NULL, `user_session_id` char(32) NOT NULL, `product_type` enum('coffee','other','sale') DEFAULT NULL, `product_id` mediumint(8) unsigned NOT NULL, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `product_type` (`product_type`,`product_id`), KEY `user_session_id` (`user_session_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- -- Dumping data for table `wish_lists` -- Would anyone have any suggestions as to how do so? Many thanks in advance! Link to comment Share on other sites More sharing options...
Antonio Conte Posted June 13, 2012 Share Posted June 13, 2012 "Coffe" is a generalization here. That's kind of the point. The same would work for products. Instead of adding tables for shoes, pants, etc, you define product categories inside the products table. products ( id, product_id*, quantity, price_per_unit, etc....) product_types ( product_id, product_type) Now you'll be able to create type for shoes, computers, coffee, whatever. You then use product_id as a foreign key inside the products table. This way, you'll be able to write queries like... SELECT * FROM products WHERE product_id = $product_id and thus only show computers on the computer store, coffe at the coffee store, etc. This could be done by something like get: prod.php?type=5 (For the sake of this example, lets say 5 is coffee)... Hope I make sense. Generally, I would recommend you reading about table normalization. You missed a few very vital points there. 1 Link to comment Share on other sites More sharing options...
wurong Posted June 18, 2012 Author Share Posted June 18, 2012 Thank you and thank u I tried your method Link to comment Share on other sites More sharing options...
wurong Posted June 18, 2012 Author Share Posted June 18, 2012 This my code, i try to do but my page is emply <?php //show records $query = mysql_query("SELECT * FROM products WHERE product_id = $product_id"); while ($row = mysql_fetch_assoc($query)) { ?> <div class="record round"><?php echo "{$row['name']}#{$row['price']}";?></div> <?php } ?> please Larry i need help Link to comment Share on other sites More sharing options...
Larry Posted June 18, 2012 Share Posted June 18, 2012 What debugging steps have you taken and what were the results? Link to comment Share on other sites More sharing options...
Antonio Conte Posted June 18, 2012 Share Posted June 18, 2012 What is $product_id? Is the variable set? Have you tried the query in phpmyadmin with values instead of a variable? Link to comment Share on other sites More sharing options...
Paul Swanson Posted June 18, 2012 Share Posted June 18, 2012 wurong, if that is your full script you are missing the piece where you make a database connection. Nothing would be printed because no row of data would be returned. Link to comment Share on other sites More sharing options...
Recommended Posts