php - MySQL获取所有有 child 的 parent

Question

我有两个具有一对多关系的表。我需要查询，它将返回我所有 child 的 parent 。我可以在 PHP 中使用 foreach parent 给我所有的 child 来做到这一点。像这样:

查询所有 parent :

SELECT * FROM parents WHERE deleted=0;

比在 PHP 中的 foreach

SELECT * FROM children WHERE deleted=0 AND parent_id = {$parentID};

有没有一种方法可以通过一个数据库查询来完成此操作。如果有人可以向我推荐一些教程或一些简单的例子:)

Answer 1

你可以这样做:

<?php
  /**
    a sample model
  **/
  class Model {
  
    public function getParentsWithChildren() {

      $out = array();
  
      //you have your PDO instance...
      $query = $pdostmt->query('SELECT * FROM parents WHERE (deleted = 0)');
  
      while ($data = $query->fetch(PDO::FETCH_OBJ)) {
        
        $out[] = array(
          $data->id, $data->column_name, $this->getChildren($data->id)
        );
        
      }
    
      $query->closeCursor();
    
      return $out;

    }

    public function getChildren($parent) {
        
      $out = array();

      $query = $pdostmt->prepare('SELECT * FROM children WHERE (deleted = 0) AND (parent_id = ?)');
      $query->execute(array($parent));

      while ($data = $query->fetch(PDO::FETCH_OBJ)) {
        
        $out[] = array(
          $data->id, $data->column_name, $data->other_column
        );
        
      }
    
      $query->closeCursor();
    
      return $out;
        
    }
    
  }
?>

在那里你可以像这样使用它:

<?php
  //your php file

  include('path/to/the/Model.php');

  $model = new Model;

  $parentComplete = $model->getParentsWithChildren();

?>

你可以得到一个具有这种结构的数组:

(
  0 => (
         0 => 1,
         1 => example_parent,
         2 => (
                0 => (
                       0 => 1,
                       1 => example_child_1
                     ),
                1 => (
                       0 => 1,
                       1 => example_child_2
                     )
              )
       ),
   1 => (
    //...
   )
)