如何从另一个表排序的mysql表中获取值? [英] how to get value from mysql table ordered by another table?

问题描述

对于给定的文章，我有一些参考记录.参考可能是文章，书籍，论文等的类型. 每个都可能具有不同的属性，例如

i have some reference to record for a given article. a reference might be of type an article, book, thesis etc.. each might have different attributes, e.g.

//article_refs table
ID Article_ID Article_Title Author_Name ...
1  1          title1        author1
2  1          title2        author2

//thesis_refs table
ID Article_ID Thesis_Title Thesis_Publisher ...
1  1          thesis1      publisher1
2  1          thesis2      publisher2

//ref_types table
ID Article_ID ReferenceType
1  1          book
2  1          article
3  1          article
4  1          book

当我插入其中一个表时，我首先将ref_type表及其类型(书，文章)插入表中.然后插入它所属的表. (例如，如果是文章，请插入文章表中.)

when i insert into one of the tables, i first into ref_type table with its type (book, article). then insert whichever table it belongs. (e.g. if it is an article, insert into article table)..

现在，我必须能够按顺序列出参考文献.

now, i have to be able list references in order.

$sql=mysql_query("SELECT * FROM ref_types 
WHERE $article_ID=Article_ID ORDER BY ID ASC");
while($row = mysql_fetch_array($sql)){
    $counter=1;

    if($row[2]=="thesis"){
        $sqlthesis=mysql_query("SELECT * FROM thesis_refs
        WHERE $article_ID=Article_ID 
        ORDER BY ID ASC");
        while($thesis_row = mysql_fetch_array($sqlthesis)){
            echo "record $counter: ";
            echo $row[2];
            echo ", ";
            echo $thesis_row[2];
            echo "<BR>";
            $counter++
        }   
    }elseif.....

这样，它列出了所有论文记录，然后列出了文章表格等.

this way it lists all thesis records then lists article table etc..

record 1: book1
record 2: book2
record 3: article1
record 4: article2

我知道这仅仅是因为while循环，但是如何获得它(与ref_types表的顺序相同.)?

i know it is simply because of while loop, but how to get this (same order with ref_types table..)??

record 1: book1
record 2: article1
record 3: article2
record 4: book2

任何帮助，我们感激不尽. 预先感谢..

any help is appreciated. thanks in advance..

推荐答案

除了在ref_types表中具有ReferenceType列之外，您还需要一个Reference_ID列，该列将相应表中的实际ID引用为

In addition to having ReferenceType column in ref_types table, you also need a Reference_ID column that refers to the actual ID in the corresponding table as a foreign key.

//ref_types table
ID Article_ID ReferenceType Reference_ID
1  1          book          1
2  1          article       1
3  1          article       2
4  1          book          2

然后，您可以避免WHILE循环，而让MySQL通过JOIN为您完成工作:

Then, you can avoid a WHILE loop and let MySQL do the work for you with JOINs:

SELECT CONCAT('record ', rt.ID, ': ',
  COALESCE(ar.Article_Title, tr.Thesis_Title, br.Book_Title))
FROM ref_types rt
LEFT JOIN article_refs ar
  ON rt.ReferenceType = 'article' AND ar.ID = rt.Reference_ID
LEFT JOIN book_refs br
  ON rt.ReferenceType = 'book' AND br.ID = rt.Reference_ID
LEFT JOIN thesis_refs tr 
  ON rt.ReferenceType = 'thesis' AND tr.ID = rt.Reference_ID

得出结果:

record 1: book1
record 2: article1
record 3: article2
record 4: book2

这篇关于如何从另一个表排序的mysql表中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！