php - 从多个表中提取 SQL 数据

Question

尝试从 SQL 查询中检索数据时遇到困难。

我在 HTML 网站上使用 PHP 运行查询。

上一页有一个按钮:

href="edit_Job.php?edit=<?php echo $result->JobID;?>">Edit Job

这是使用我的 Jobs Table 中的 JobID 将数据拉到下一页 Edit_job.php

下一页的顶部我有一些 PHP 代码:

 if(isset($_GET['edit']))
    {
        $editid=$_GET['edit'];
    }

虽然我从 Jobs 表中获取数据，但我还有其他表需要提取数据。

就像概述一样，我有三个表，我需要从每个表中提取数据的数据都有一个外键，如下所述。

Jobs =======JobID JobTitleClient SiteNameSites=======SideIDClientSiteClients=======ClientIDSiteIDClientName

• Clients Can have Multiple Jobs and Sites.
• Jobs Can have Multiple Sites.
• Sites Can Have Multiple Jobs but only one Client.

How would it be possible to retrieve data from these tables using PHP? I have been searching the internet I have tried inner joins, joins, select from where etc.

I can get results on PHPMYADMIN using

SELECT Jobs.Sitename, Sites.Site FROM Jobs INNER JOIN Sites ON Jobs.Sitename = Sites.site

但这不是来自 EDIT 的具体信息

任何帮助，将不胜感激

Answer 1

看起来您需要一些 many to many表格以获取您想要的信息。这将需要对数据库进行一些重新设计。

在多对多表中，该表仅包含需要配对的两列 ID。像这样制作表格:

Jobs_Sites=======JobIDSiteID    Clients_Jobs=======ClientIDJobID

Here is a full example of a DB that would accomplish this.

CREATE TABLE IF NOT EXISTS `Jobs` (
  `JobID` int(6) unsigned NOT NULL,
  `JobTitle` varchar(200) NOT NULL,
  PRIMARY KEY (`JobID`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Sites` (
  `SiteID` int(6) unsigned NOT NULL,
  `SiteName` varchar(200) NOT NULL,
  `ClientID` int(6) unsigned NOT NULL,
  PRIMARY KEY (`SiteID`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Clients` (
  `ClientID` int(6) unsigned NOT NULL,
  `ClientName` varchar(200) NOT NULL,
  PRIMARY KEY (`ClientID`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Jobs_Sites` (
  `JobID` int(6) unsigned NOT NULL,
  `SiteID` int(6) unsigned NOT NULL,
  PRIMARY KEY (`JobID`, `SiteID`)
) DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Clients_Jobs` (
  `ClientID` int(6) unsigned NOT NULL,
  `JobID` int(6) unsigned NOT NULL,
  PRIMARY KEY (`ClientID`, `JobID`)
) DEFAULT CHARSET=utf8;


INSERT INTO `Jobs` (`JobID`, `JobTitle`) VALUES
  (1, 'Big job'),
  (2, 'Small job'),
  (3, 'Cheap job'),
  (4, 'Difficult job');

INSERT INTO `Sites` (`SiteID`, `SiteName`, `ClientID`) VALUES
  (1, 'Park', 1),
  (2, 'City', 2),
  (3, 'Road', 1);

INSERT INTO `Clients` (`ClientID`, `ClientName`) VALUES
  (1, 'Bob'),
  (2, 'Hannah'),
  (3, 'Jimmy');

INSERT INTO `Jobs_Sites` (`JobID`, `SiteID`) VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (3, 2),
  (3, 3),
  (4, 3);

INSERT INTO `Clients_Jobs` (`ClientID`, `JobID`) VALUES
  (1, 1),
  (1, 2),
  (2, 3),
  (3, 4),
  (3, 1);

然后你可以做这样的查询:

获取职位信息

SELECT j.JobId,
       j.JobTitle,
       s.SiteID,
       s.SiteName,
       c.ClientId,
       c.ClientName
FROM Jobs j
     JOIN Jobs_Sites js ON j.JobID = js.JobID
     JOIN Sites s ON s.SiteID = js.SiteID
     JOIN Clients_Jobs cj ON cj.JobID = j.JobID
     JOIN Clients c ON c.ClientID = cj.ClientID
WHERE j.JobID = 1

结果

JobId   JobTitle    SiteID  SiteName    ClientId    ClientName1       Big job     1       Park        1           Bob1       Big job     2       City        1           Bob1       Big job     3       Road        1           Bob1       Big job     1       Park        3           Jimmy1       Big job     2       City        3           Jimmy1       Big job     3       Road        3           Jimmy

Get Client info

SELECT j.JobId,
       j.JobTitle,
       s.SiteID,
       s.SiteName,
       c.ClientId,
       c.ClientName
FROM Jobs j
     JOIN Jobs_Sites js ON j.JobID = js.JobID
     JOIN Sites s ON s.SiteID = js.SiteID
     JOIN Clients_Jobs cj ON cj.JobID = j.JobID
     JOIN Clients c ON c.ClientID = cj.ClientID
WHERE c.ClientID = 2

结果

JobId JobTitle SiteID SiteName ClientId ClientName
3 廉价工作 2 城市 2 汉娜
3 廉价工作 3 路 2 汉娜

如果这不是你想要的，这应该让你指向正确的方向。