MYSQL不会返回超过1个类别

Question

I've tried to find an answer to this problem, but I can't.

I have a 3 table format mqsql database. I use 1 table to add all of the product information, CarpetInfo, 1 Table to list my categories, CarpetCategories, and 1 Table to add categories to the products, CarpetCategorySort.

My CarpetCategorySort table has 3 columns, Manufacturer, Style, CategoryID. Example would be Manufacturer = Aladdin, Style = Alma Mater, CategoryID = 14/ 15/ 18/ 19/ 20/ 21/ 67/

My CarpetCategories Table has 2 Columns CategoryID and Category. 2 Examples would be CategoryID = 14, Category = Commercial & CategoryID = 15, Category = Commercial Loop

I can only get the code to work when I type in Commercial into the $category variable below. The code will not work if I type Commercial Loop into the $category variable. It's like it will only pull in the first number 14 and all of the others are ignored. The pricing order and everything else works right, just not the CategoryID part.

Here is my code.

<?php $mill = "Aladdin"; $category = "Commercial Loop";
$order = mysqli_query($con, "
SELECT * FROM CarpetInfo JOIN CarpetCategorySort USING (Manufacturer, Style) 
JOIN CarpetCategories USING (CategoryID)
WHERE Manufacturer='$mill' AND Category LIKE '%$category%'
order by Price = 0, Price asc,
Style asc");
include($_SERVER['DOCUMENT_ROOT'].'/includes/pricing/carpet-order-test.htm');?>

Any help is greatly appreciated!

Answer 1

That's a backward way of handling things, a field with several datapoints smooshed together like that really never has any justification for existence. I'd list the carpets in one table, list the categories in another, and finally list cross-references of both in another table, where you get a many-to-one relationship at both ends.

You need an actual category table only if you think it's going to take so much room to give duplicate info for each category and/or you can't control user input for categories (like you aren't just using a pulldown to give a name choice).

Something like:

CREATE TABLE IF NOT EXISTS `carpets` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `abbrev` varchar(10),
  `description` varchar(250),
  [...]
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `carpet_categories` (
`id` int(11) NOT NULL,
  `carpet_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
[...]
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `carpet_category_info` (
  `id` int(11) NOT NULL,
  `price-per-sqf` int(11) NOT NULL,
  `name` varchar(50),
   [...]
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

then all your joins become simple, and fast.. And accurate.