donglang6656 2015-06-25 01:01
浏览 33
已采纳

php / sql连接多个字段的表

I'm trying to echo info from a database onto the webpage. The page is supposed to have a bunch of article publications which includes the title, authors, journal name (if applicable), date published. For example:

  1. title of the article (with author 1, author 2, author 3), name of the journal, date published.

I've made two tables. One contains paper_id, author_id, paper_name, journal_name, date. Second one contains author_id, author_firstname, author_lastname.

The part I'm struggling with is outputting multiple authors. in the second table I have the following for first paper

author_id: 1
author_firstname: john
author_lastname: smith

author_id: 1
author_firstname: bob
author_lastname: thomas

I want to print this: Name of the paper (with john smith, bob thomas), journal name, date published.

I've tried joining author_id but it outputs the same paper twice (since i have two authors with id 1. I need suggestions as to how i should change the table or join it differently and what not. Any help will be appreciated, thanks. I'm also a beginner programmer

  • 写回答

2条回答 默认 最新

  • duandu1915 2015-06-25 01:18
    关注

    I beleieve the correct strategy is to use GROUP_CONCAT on each paper to obtain a comma separated list of authors. As a note, each group in the query below having a given paper_id will also have the same values for paper_name, journal_name and date. I include the columns other than paper_id so that they will be available in the SELECT clause.

    SELECT t1.paper_name,
        GROUP_CONCAT(CONCAT(t2.author_lastname, ', ', t2.author_firstname)
        SEPARATOR ' '),
        t1.journal_name, t1.date
    FROM table1 t1 INNER JOIN table2 t2
    ON t1.author_id = t2.author_id
    GROUP BY t1.paper_id, t1.paper_name, t1.journal_name, t1.date
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 Llama如何调用shell或者Python
  • ¥20 谁能帮我挨个解读这个php语言编的代码什么意思?
  • ¥15 win10权限管理,限制普通用户使用删除功能
  • ¥15 minnio内存占用过大,内存没被回收(Windows环境)
  • ¥65 抖音咸鱼付款链接转码支付宝
  • ¥15 ubuntu22.04上安装ursim-3.15.8.106339遇到的问题
  • ¥15 blast算法(相关搜索:数据库)
  • ¥15 请问有人会紧聚焦相关的matlab知识嘛?
  • ¥15 网络通信安全解决方案
  • ¥50 yalmip+Gurobi