thinkPHP multi-table query method example-PHP Tutorial-php.cn

thinkPHP multi-table query method example

小云云

Release： 2023-03-20 07:58:02

Original

1265 people have browsed it

This article mainly introduces the implementation method of thinkPHP multi-table query and paging function. It analyzes the related implementation techniques of thinkPHP multi-table query and paging display of query results based on specific examples. Friends who need it can refer to it. I hope it can help. Everyone.

The project business logic is: the teacher uploads the test paper, sets the answer sheet, and publishes the answer sheet to the relevant class or group. Only after the answer sheet associated with the test paper is released, the test paper can be searched in the system test paper. At the same time Other teachers can also collect it. In the front-end collection module, there is a business that provides an input box to provide search functions to users. However, in the pre-designed search table, only one test paper ID is associated with the test paper table. What if the user searches for the test paper title? Instead of querying two tables, the method I thought of at first was to add an extra field to the collection table, that is, add the fields of the test paper questions to the collection table, and the business is completed. Today when I was dealing with the logic of question bank sharing, I discovered this problem again. I saw that the sharing table designed by my colleague only had one question bank ID, so I called my colleague over to "correct it", but beforehand I still wanted to hear my colleague's design ideas. , my colleague said that it is a table query, let me check it out, it seems that I am used to using a table query, I did not expect this situation, and was despised, so I faced my mistake squarely, of course, the premise is to talk about my own The idea is not quite right now that I think about it, so I will give the relevant TP code below.

// 异步请求试卷夹下某一个试卷夹的列表
public function ajaxLists() {
  extract($_GET);
  $page = intval($_GET[&#39;p&#39;]);
  $prefix = C(&#39;DB_PREFIX&#39;);
  $collect = $prefix . &#39;collect&#39;;
  $resource = $prefix . &#39;resource&#39;;
  if ($keyword) {
    $arr = preg_split(&#39;/ /&#39;, $keyword);
    // 搜索标签
    foreach ($arr as $value) {
      $id = A(&#39;Home/Papers&#39;)->trunWordToId(array($value));
      if ($id) {
        $where[&#39;resource.rta_id&#39;][] = array(&#39;LIKE&#39;, &#39;%,&#39; . $id . &#39;,%&#39;);
      }
      $where[&#39;resource.re_title&#39;][] = array(&#39;LIKE&#39;, &#39;%&#39; . $value . &#39;%&#39;);
    }
    if ($where[&#39;resource.rta_id&#39;]) {
      $where[&#39;resource.rta_id&#39;][] = &#39;AND&#39;;
    }
    if ($where[&#39;resource.re_title&#39;]) {
      $where[&#39;resource.re_title&#39;][] = &#39;OR&#39;;
    }
    if ($where[&#39;resource.re_title&#39;] && $where[&#39;resource.rta_id&#39;]) {
      $where[&#39;_logic&#39;] = &#39;OR&#39;;
    }
  }
  if ($where) {
    $map[&#39;_complex&#39;] = $where;
  }
  $map[&#39;collect.a_id&#39;] = $this->authInfo[&#39;a_id&#39;];
  $map[&#39;_string&#39;] = &#39;collect.col_object_id = resource.re_id&#39;;
  // 总数
  $count = M()->table("$collect collect, $resource resource")->where($map)->count();
  // 总页数
  $regNum = ceil($count/6);
  // 验证当前请求页码是否大于总页数
  $page = $page > $regNum ? $regNum : $page;
  // 引入ajax分页库
  import("@.ORG.Util.AjaxPage");
  $Page = new AjaxPage($count, 6);
  $list[&#39;page&#39;] = trim($Page->show());
  $list[&#39;list&#39;] = M()->table("$collect collect, $resource resource")->where($map)->order(&#39;col_id DESC&#39;)->limit($Page->firstRow . &#39;,&#39; . $Page->listRows)->field(&#39;collect.col_id,collect.col_object_id,resource.re_id,resource.re_title,resource.re_created,resource.re_collect_count,resource.re_score_count,resource.re_score_num,resource.rta_id&#39;)->select();
  // 获取试卷的标签
  $wheree[&#39;rta_id&#39;] = array(&#39;IN&#39;, trim(str_replace(&#39;,,&#39;, &#39;,&#39;, implode(&#39;&#39;, getValueByField($list[&#39;list&#39;], &#39;rta_id&#39;))), &#39;,&#39;));
  $tag = setArrayByField(M(&#39;ResourceTag&#39;)->where($wheree)->field(&#39;rta_id,rta_title&#39;)->select(), &#39;rta_id&#39;);
  // 把标签和试卷对应
  foreach ($list[&#39;list&#39;] as $key => &$value) {
    $str = &#39;&#39;;
    foreach ($tag as $k => $v) {
      if (strpos($value[&#39;rta_id&#39;], &#39;,&#39; . $k . &#39;,&#39;) !== FALSE) {
        $str .= &#39; | &#39; . $v[&#39;rta_title&#39;];
      }
      $value[&#39;rta_title&#39;] = trim($str, &#39; |&#39;);
    }
    if ($keyword) {
      foreach ($arr as $vv) {
        if (strpos($value[&#39;re_title&#39;], $vv) !== FALSE) {
          $value[&#39;re_title&#39;] = str_replace($vv, &#39;<font color=\&#39;red\&#39;>&#39; . $vv . &#39;</font>&#39;, $value[&#39;re_title&#39;]);
        }
        if (strpos($value[&#39;rta_title&#39;], $vv) !== FALSE) {
          $value[&#39;rta_title&#39;] = str_replace($vv, &#39;<font color=\&#39;red\&#39;>&#39; . $vv . &#39;</font>&#39;, $value[&#39;rta_title&#39;]);
        }
      }
    }
    $value[&#39;re_created&#39;] = date(&#39;Y-m-d&#39;, $value[&#39;re_created&#39;]);
  }
  echo json_encode($list);
}

Copy after login

Related recommendations:

Examples of constraints, multi-table queries and subqueries in MySQL Detailed explanation

ThinkPHP multi-table query-if field A is the same, add field B_PHP tutorial

thinkphp study notes multi-table Query, thinkphp study notes_PHP tutorial

The above is the detailed content of thinkPHP multi-table query method example. For more information, please follow other related articles on the PHP Chinese website!