4

I am having difficulty when trying to retrieve data from and SQL Query.

I am running the Querys using PHP on a HTML website.

The previous page has a button with : 

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

This is using the JobID in my Jobs Table to pull the data to the next page Edit_job.php

Top of the next page I have a bit of PHP Code :

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

Whilst I am getting the data from the Jobs table I have other tables where I need to pull data.

Just as an overview I have three tables where i need the data to pull data from each table has a foreign key as described below.

Jobs 
=======
JobID 
JobTitle
Client 
SiteName

Sites
=======
SideID
Client
Site

Clients
=======
ClientID
SiteID
ClientName
  • 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

but this is not specific information from EDIT

any Help would be appreciated

Improve this question
2
  • 1
    if you have the same client id or name in all those tables just relate to that to call any aspect for that user Commented Nov 21, 2019 at 17:40
  • I am trying that but I cannot seem to get anywhere with it Commented Nov 21, 2019 at 20:33

1 Answer 1

Reset to default
1

It looks like you are going to need some many to many tables to get the info you want. This will require a bit of a DB redesign.

In a many-to-many table, the table only contains two columns of IDs you need to pair together. Make tables like this:

Jobs_Sites
=======
JobID
SiteID    

Clients_Jobs
=======
ClientID
JobID

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);

Then you can do queries like this:

Get Job 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 j.JobID = 1

Result

JobId   JobTitle    SiteID  SiteName    ClientId    ClientName
1       Big job     1       Park        1           Bob
1       Big job     2       City        1           Bob
1       Big job     3       Road        1           Bob
1       Big job     1       Park        3           Jimmy
1       Big job     2       City        3           Jimmy
1       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

Result

JobId   JobTitle    SiteID  SiteName    ClientId    ClientName
3       Cheap job   2       City        2           Hannah
3       Cheap job   3       Road        2           Hannah

If this isn't exactly what you want this should get you pointed in the right direction.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thank you for your input, it was muchley appreciated I was able to get it working over the Christmas break :)
Wonderful. If my answer helped you, could you upvote and accept as the answer? Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.