如何:一对多到一些到多?在 PHP 中
我正在建立一个网上商店。商店有几个类别(~10),类别有多个产品(每个~10),产品有很多子产品(每个20+)。
这是我陷入困境的部分:
子产品有几列详细信息。该产品还有 10 个标头。
这是产品的样子:
http://www.ampedwebdesign .com/bear/new2/public/products/images/straightpins.jpg
标题是深色背景的单元格,子产品是行。
这是我的标头类的一部分:
class Header extends DatabaseObject {
protected static $table_name="headers";
protected static $db_fields=array('id', 'product_id', 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col0', 'col1', 'col2');
public $id;
public $product_id;
public $col1;
public $col2;
public $col3;
public $col4;
public $col5;
public $col6;
public $col7;
public $col8;
public $col9;
public $col10;
public $col11;
public $col12;
产品视图调用产品类来显示图像。然后,产品视图调用标头类以按product_id 返回标头。然后,产品视图调用子产品类,通过product_id 返回子产品。然后,产品视图调用子产品类以在 foreach 循环中返回子产品详细信息。
任何帮助将不胜感激,当我知道有更好的方法时,我讨厌编写这样的代码。我只是不知道如何找到它。
我的数据库表:产品、子产品、类别、标题
I am building an online store. The store has several categories (~10), the categories have several products (~10 each), the products have many sub-products(20+ each).
This is the part I am getting stuck on:
The sub-products have several columns of details. The products also have 10 headers.
This is what a product looks like:
http://www.ampedwebdesign.com/bear/new2/public/products/images/straightpins.jpg
the headers are the cells with dark background, and the sub-products are the rows.
This is part of my header class:
class Header extends DatabaseObject {
protected static $table_name="headers";
protected static $db_fields=array('id', 'product_id', 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col0', 'col1', 'col2');
public $id;
public $product_id;
public $col1;
public $col2;
public $col3;
public $col4;
public $col5;
public $col6;
public $col7;
public $col8;
public $col9;
public $col10;
public $col11;
public $col12;
The product view calls the product class to show the image. The product view then calls the header class to return headers by product_id. The product view then calls the sub-products class to return the sub-products by product_id. The product view then calls the sub-products class to return the sub-product details in a foreach loop.
Any help would be appreciated, I hate writing code like this when I know there is a better way. I just don't know how to find it.
My DB tables: products, sub-products, categories, headers
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
解决此类问题时,我通常采取的第一步是尽可能规范化架构 。看看您的示例产品,这就是我想到的:
items
:列number
,price
attributes
>:列id
、title
items_attributes
:列item_id
、attribute_id
、< code>value那么,当查询数据库:
我的 MySQL 有点生疏,所以语法可能不是 100% 正确,但希望这能帮助您找到正确的方向。
编辑:
正如rootatwc在评论中提到的,InnoDB存储引擎支持外键,它允许您执行级联
UPDATE
和DELETE
s之类的操作,这样您就不会得到大量孤立数据,并强加检查约束以帮助确保您不会获得虚假/超出范围的数据。 维基百科对这些机制有相当不错的解释。The first step I usually take in solving a problem like this is to normalize the schema as much as possible. Looking at your example product, this is what jumps out at me:
items
: columnsnumber
,price
attributes
: columnsid
,title
items_attributes
: columnsitem_id
,attribute_id
,value
Then, when querying the database:
My MySQL is a bit rusty so the syntax may not be 100% correct but hopefully this'll help you in the right direction.
Edit:
As mentioned by rootatwc in the comments, the InnoDB storage engine has support for foreign keys, which allows you to do things like cascade
UPDATE
s andDELETE
s so that you don't end up with a ton of orphaned data, and impose check constraints to help ensure that you don't get bogus/out-of-range data. Wikipedia has a fairly decent explanation of these mechanics.