mySqli ....内部加入? [英] mySqli ....inner join?

问题描述

我有两个具有以下结构的桌子..照片和画廊:
-----------------------------------------------
表格:照片
-----------------------------------------------
--photo -----所有者-画廊-订单
123.jpg -------- 23 -------- 28 ------ 1
234.jpg -------- 23 -------- 28 ------ 2
345.jpg -------- 23 -------- 29 ------ 1
456.jpg -------- 23 -------- 29 ------ 2
567.jpg -------- 23 -------- 30 ------ 1

I have two tables..photos and galleries with the following structure:
-----------------------------------------------
Table: photos
-----------------------------------------------
--photo ----- owner - gallery - order
123.jpg --------23--------28 ------ 1
234.jpg --------23--------28 ------ 2
345.jpg --------23--------29 ------ 1
456.jpg --------23--------29 ------ 2
567.jpg --------23--------30 ------ 1

--------------------------------------------------- -
表:画廊
-----------------------------------------------
id --galleryname ----所有者----访问-
28-图库1 ---------- 23 -------私人-
29-Gallery 2 ---------- 23 ------- public-
30-Gallery 3 ---------- 23 ------- public-

------------------------------------------------
Table: galleries
-----------------------------------------------
id --galleryname ---- owner ---- access -
28 -- Gallery 1 ---------- 23 ------- private -
29 -- Gallery 2 ---------- 23 ------- public -
30 -- Gallery 3 ---------- 23 ------- public -

两个表之间的关系是photos.gallery = galleries.id

The relation between the two tables is that photos.gallery = galleries.id

我想获取具有特定访问类型(私有)的每个画廊的列表(使用while循环)，并从顺序为1的每个画廊的photos表中获取photos.photo.

I want to get a list (using a while loop) of every gallery with a specific access type (private) and get the photos.photo from the photos table for each gallery where the order = 1.

我应该使用内部联接，左联接还是什么?我尝试了下面的方法，但是没有用.有人可以帮我了解我在做什么错吗?

Should I be using an inner join, left join or what? I tried the below and it did not work. Can someone please help me understand what I am doing wrong?

if ($stmt = $mysqli->prepare("SELECT galleries.id, galleries.galleryname, photos.photo FROM galleries INNER JOIN photos ON galleries.id=photos.gallery WHERE galleries.access = 'private' AND galleries.owner = '23' AND photos.count = '1'")) 
{     
  $stmt->execute();     
  $stmt->bind_result($private_gal_id,$private_gal_name,$private_gal_photo);
  while ($stmt->fetch()) 
    {      
      echo "The results: $private_gal_id - $private_gal_name - $private_gal_photo";
    }
  $stmt->close();;
}

推荐答案

而不是:

AND photos.count = '1'"))

在查询末尾应该是:

AND photos.order = '1'"))

这篇关于mySqli ....内部加入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！