如何:一对多到一些到多?在 PHP 中

发布于 2024-11-19 06:00:24 字数 1078 浏览 1 评论 0原文

我正在建立一个网上商店。商店有几个类别(~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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

苯莒 2024-11-26 06:00:24

解决此类问题时,我通常采取的第一步是尽可能规范化架构 。看看您的示例产品,这就是我想到的:

  • items:列numberprice
  • attributes >:列 idtitle
  • items_attributes:列 item_idattribute_id、< code>value

那么,当查询数据库:

   SELECT title, value
     FROM items_attributes
    WHERE item_id = 123
LEFT JOIN attributes
          ON items_attributes.attribute_id = attributes.id;

我的 MySQL 有点生疏,所以语法可能不是 100% 正确,但希望这能帮助您找到正确的方向。

编辑:

正如rootatwc在评论中提到的,InnoDB存储引擎支持外键,它允许您执行级联UPDATEDELETEs之类的操作,这样您就不会得到大量孤立数据,并强加检查约束以帮助确保您不会获得虚假/超出范围的数据。 维基百科对这些机制有相当不错的解释。

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:

  • Table items: columns number, price
  • Table attributes: columns id, title
  • Table items_attributes: columns item_id, attribute_id, value

Then, when querying the database:

   SELECT title, value
     FROM items_attributes
    WHERE item_id = 123
LEFT JOIN attributes
          ON items_attributes.attribute_id = attributes.id;

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 UPDATEs and DELETEs 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文