试图在几个表上形成SQL连接 [英] trying to form SQL join on several tables

问题描述

因此，我试图通过1个查询来弄清楚如何做到这一点.这是我对几个查询的要求

So Im trying to figure out how to do this with 1 query if possible. Here is what I have with several queries

$file_url = 'http://'.$cms.'/filemanager/gallery';
$link_url = '/our-process/gallery/';
include 'inc/config.php';
function gallerySafeName($var){
    $var = strtolower(preg_replace('([^a-zA-Z0-9_])','-',$var));
    return $var;
}
$galleryimages = array();
$sql = "SELECT * FROM `cms_gallery_categories` ORDER BY RAND() LIMIT 3";
$result = mysqli_query($con,$sql);
while ($row = mysqli_fetch_array($result)){
    $sql1 = "SELECT * FROM `cms_gallery_images` WHERE `image_id` = ".$row['category_image_id'];
    $result1 = mysqli_query($con,$sql1);
    while ($row1 = mysqli_fetch_array($result1)){
        $sql2 = "SELECT * FROM `cms_gallery` WHERE `gallery_id` = ".$row1['gallery_id'];
        $result2 = mysqli_query($con,$sql2);
        while ($row2 = mysqli_fetch_array($result2)){
            print '<li>
            <a href="'.$link_url.$row['category_name'].'/'.$row2['gallery_name'].'/'.gallerySafeName($row1['image_caption']).'/">
            <img  width="210" height="133" border="0" src="'.$file_url.'/'.$row['category_name'].'/'.$row2['gallery_name'].'/'.$row1['image_name'].'" />
            </a>
            </li>';
        }
    }
}

我正在尝试从画廊类别中获取3个随机条目，并分配代表该类别的图像.然后获取图库名称，然后获取图像名称，以形成img src和链接.我的表结构如下

Im trying to get 3 random entries from the gallery category and the image assigned to represent that category. Then get the gallery name and then the image name to form a img src and link. My table structure is as follows

-cms_gallery_categories
category_id
category_title
category_name
category_image_id

-cms_gallery
gallery_id
gallery_title
gallery_name
category_id

-cms_gallery_images
image_id
image_name
image_caption
gallery_id

图像属于画廊，画廊属于类别

Images belong to galleries, and galleries belong to categories

推荐答案

SELECT GC.*, G.*, I.*
FROM cms_gallery_categories GC
INNER JOIN cms_galleries G ON (G.category_id = GC.category_id)
INNER JOIN cms_gallery_images I ON (I.gallery_id = G.gallery_id)
ORDER BY RAND() LIMIT 3

您将希望将字段限制为仅需要的内容.从您的代码中可以看到，是这样的:

You will want to limit your fields to what you need only. From what I can see in your code, it's this:

SELECT GC.category_name, G.gallery_name, I.image_caption FROM...

更多信息

• http://www.w3schools.com/sql/sql_join.asp
• http://www. codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
• http://www.tutorialspoint.com/sql/sql-using- joins.htm
• http://www.w3schools.com/sql/sql_join.asp
• http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
• http://www.tutorialspoint.com/sql/sql-using-joins.htm

这篇关于试图在几个表上形成SQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！