mysql查询从两个表中获取信息

vmpqdwk3 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(298)

我一直在尝试为我的图库制作一个新的小部件，它可以显示我最近更新的相册。最后上传的图片总是有较高的pid，并且图片只能在一个相册中。
但经过长时间的尝试，我做了一些事情，但我有两个问题：第一：没有专辑标题。第二：它没有显示我选择的拇指。
这是我的密码

<?php
    require_once('include/config.inc.php');
    header("Content-type: application/x-javascript");

    $connect = mysql_connect('localhost','user','pass') or die('Error conexion server');
    $connect_db = mysql_select_db('database', $connect) or die ('Error conexion base de datos');

                $resultado = mysql_query(" SELECT DISTINCT(aid) FROM cpgq7_pictures ORDER BY pid DESC LIMIT 0 ,6", $connect) or die('Ningun album encontrado');

                echo 'document.write(\'';

                    if(mysql_num_rows($resultado) == 0){
                        echo 'Ningun album obtenido';
                    } else {

echo '<div class="photos">  ';
                        while($row = mysql_fetch_array($resultado)){
                            echo ' ';
                                $album_id = $row['aid'];
                                $subresult = mysql_query("SELECT * FROM cpgq7_pictures where aid=$album_id order by pid DESC LIMIT 0, 6");
                                $album_title = mysql_query("SELECT * FROM cpgq7_albums where aid=$album_id DESC LIMIT 0, 6");

                                if(mysql_num_rows($subresult) == 0){
                                    $album_img = "http://link/thumbs/thumb_nopic.png";
                                } else {
                                    while($subrow = mysql_fetch_array($subresult)){
                                        $album_img = "http://link/albums/".$subrow['filepath'].'normal_'.$subrow['filename']  .$subrow['datebrowse'];
                                    }
                                }
                                echo '<div class="g-album">';
                                    echo '<a href="http://link/thumbnails.php?album='.$album_id.' " target="_blank"><img src="'.$album_img.'" alt="" /></a>';
                                    echo '<div class="g-title"><a href="http://link/thumbnails.php?album='.$album_id.' " target="_blank">'.$album_title.'</a></div>';
                                echo "</div>";
                        }
                        echo '</div>';
                    }
                    echo '\');';
            ?>

在cpgp7\u图片中你会发现：“pid”，“aid”，“filepath”，“filename”等，在cpgp7\u相册中你会发现：“aid”，“title”，“thumb”（封面的pid）
例子：

cpgp7_pictures

pid       aid    filepath          filename
21074     159    userpics/10002/   CREATIONS00004.jpg
21073     405    userpics/10002/   LMH00003.jpg
21072     405    userpics/10002/   LMH00002.jpg
21071     405    userpics/10002/   LMH00001.jpg

cpgp7_albums

aid       title                                    thumb
405       T-Mobile, Las Vegas - CreationsOfLa      21074
159       Love Me Harder - Jones Crow              21071

加载的小部件应该是这样的：

<div class="photos">
    <div class="g-album">
    <a href="http://arianagrandechile.net/galeria/thumbnails.php?album=405" target="_blank"><img src="http://arianagrandechile.net/galeria/albums/userpics/10002/normal_CREATIONS00004.jpg" alt="" /></a>
    <div class="g-title"><a href="http://arianagrandechile.net/galeria/thumbnails.php?album=405" target="_blank">T-Mobile, Las Vegas - CreationsOfLa</a></div>
</div>

有人能帮帮我吗？

mysql php

来源：https://stackoverflow.com/questions/51249568/my-sql-query-getting-info-from-two-tables

1条答案

按热度按时间

ia2d9nvy1#

不要在php中进行连接，而是使用单个sql查询来获取数据。

SELECT * 
FROM `cpgq7_pictures` a
JOIN `cpgq7_albums` b
    ON a.`aid` = b.`aid`
GROUP BY a.`aid`, a.`pid`
ORDER BY a.`aid`, a.`pid` DESC 
LIMIT 0, 6

您不需要限制来自cpgq7\u相册的行，因为每个图片应该只匹配一个相册。
edit创建了一个sqlfiddle来显示查询
sql小提琴
mysql 5.6架构设置：

CREATE TABLE IF NOT EXISTS `cpgp7_pictures` (
    `pid`       INT(11) UNSIGNED        NOT NULL    AUTO_INCREMENT  COMMENT 'Primary Key',
    `aid`       INT(11) UNSIGNED        NULL        DEFAULT NULL,
    `filepath`  VARCHAR(200)            NOT NULL    DEFAULT '',
    `filename`  VARCHAR(200)            NOT NULL    DEFAULT '',
    PRIMARY KEY (`pid`)
) 
    ENGINE=MyISAM 
    AUTO_INCREMENT=1 
    DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci
    COMMENT '';

CREATE TABLE IF NOT EXISTS `cpgp7_albums` (
    `aid`       INT(11) UNSIGNED        NOT NULL    AUTO_INCREMENT  COMMENT 'Primary Key',
    `title`     VARCHAR(200)            NOT NULL    DEFAULT '',
    `thumb`     INT(11) UNSIGNED        NULL        DEFAULT NULL,
    PRIMARY KEY (`aid`)
) 
    ENGINE=MyISAM 
    AUTO_INCREMENT=1 
    DEFAULT CHARSET=utf8 
    COLLATE=utf8_unicode_ci
    COMMENT '';

INSERT INTO `cpgp7_pictures`
(`pid`,`aid`,`filepath`,`filename`)
VALUES
(21074,159,'userpics/10002/','CREATIONS00004.jpg'),
(21073,405,'userpics/10002/','LMH00003.jpg'),
(21072,405,'userpics/10002/','LMH00002.jpg'),
(21071,405,'userpics/10002/','LMH00001.jpg');

INSERT INTO `cpgp7_albums`
(`aid`,`title`,`thumb`)
VALUES
(405,'T-Mobile, Las Vegas - CreationsOfLa',21074),
(159,'Love Me Harder - Jones Crow',21071)

查询1：

SELECT * 
FROM `cpgp7_pictures` a
JOIN `cpgp7_albums` b
    ON a.`aid` = b.`aid`
GROUP BY a.`aid`, a.`pid`
ORDER BY a.`aid`, a.`pid` DESC 
LIMIT 0, 6

结果：

|   pid | aid |        filepath |           filename | aid |                               title | thumb |
|-------|-----|-----------------|--------------------|-----|-------------------------------------|-------|
| 21074 | 159 | userpics/10002/ | CREATIONS00004.jpg | 159 |         Love Me Harder - Jones Crow | 21071 |
| 21073 | 405 | userpics/10002/ |       LMH00003.jpg | 405 | T-Mobile, Las Vegas - CreationsOfLa | 21074 |
| 21072 | 405 | userpics/10002/ |       LMH00002.jpg | 405 | T-Mobile, Las Vegas - CreationsOfLa | 21074 |
| 21071 | 405 | userpics/10002/ |       LMH00001.jpg | 405 | T-Mobile, Las Vegas - CreationsOfLa | 21074 |

第二次编辑
运行查询和输出div的php代码。

<?php
    $mysqli = new mysqli($host,$user,$pass,$dbname);
    if($mysqli->connect_errno) {
        echo "<p>Error connecting to db: ".$mysqli->connect_errno.", ".$mysqli->connect_error."</p>\n";
    } else {
        $qstr = "";
        $qstr .= "SELECT\n";
        $qstr .= "  a.`aid`,\n";
        $qstr .= "  a.`pid`,\n";
        $qstr .= "  a.`filepath`,\n";
        $qstr .= "  a.`filename`,\n";
        $qstr .= "  b.`title`,\n";
        $qstr .= "  b.`thumb`\n";
        $qstr .= "FROM `cpgp7_pictures` a\n";
        $qstr .= "JOIN `cpgp7_albums` b\n";
        $qstr .= "    ON a.`aid` = b.`aid`\n";
        $qstr .= "GROUP BY a.`aid`\n";
        $qstr .= "ORDER BY a.`aid`\n";
        $qstr .= "LIMIT 0, 6;";
        $results = $mysqli->query($qstr);
        if($results) {
            while($row = $results=>fetch_assoc()) {
                echo "<div class='photos'>\n";
                echo "    <div class='g-album'>\n";
                echo "    <a href='http://arianagrandechile.net/galeria/thumbnails.php?album=".$row['aid']."' target='_blank'><img src='http://arianagrandechile.net/galeria/albums/".$row['filepath']."normal_".$row['filename']."' alt='' /></a>\n";
                echo "    <div class='g-title'><a href='http://arianagrandechile.net/galeria/thumbnails.php?album=$row['aid']' target='_blank'>".$row['title']."</a></div>\n";
                echo "</div>\n";
            }
        }
    }
?>

赞(0）回复(0）举报 2021-06-21

我来回答

mysql查询从两个表中获取信息

1条答案

相关问题

热门标签

最新问答