[php] PHP & MySQLi Select and Join help

[Stuart-Hannah]

Evening,

 

Wondering if someone can point me in the right direction. I have 3 tables that have data and another table that has what I think is a link to that data (i've attached the structure to help) however I can't figure out how to this data to display in a table.

 

Hope this makes sense,

 

Stuart

DBStructure.docx

Edited July 4, 2016 by Stuart-Hannah
Add attachments

[matt40k]

Looks like a basic star schema, you've got some basic mistakes with it. For starts studentar has wrong data type for columns. Also not a fan of raw php code, you should be using a framework like Laravel.

[Stuart-Hannah]

Thanks for the reply. I am not experienced much with PHP so making it up as I go along so I have no idea where mistakes are if that makes sense.

[matt40k]

Have a look on https://laracasts.com - great for beginners, other one is Pluralsight. Its a bit hard to compress 5 years worth of training down to a forum post

 

Personally I just nag web developer to do it for me or at least get me started. Takes them a few moments, saves me days

[SimonHooker]

Hi there,

 

At it's simplest, to join those up all you need is some INNER JOIN's. I would start by altering the data types in Studenar so that CandidateID, AdjustmentID, and SubjectID match their respective PKs, so all 3 should have a data type of INT(11)

 

From there, this will just join the 4 tables up and get all data points

 

SELECT
   sar.StudentADID,
   c.*,
   s.*,
   a.*
FROM Studentar sar
INNER JOIN Candidates c
   ON c.CandidateID = sar.CandidateID
INNER JOIN Subjects s
   ON s.SubjectID = sar.SubjectID
INNER JOIN Adjustments a
   ON a.AdjustmentID = sar.AdjustmentID

 

From there you can limit, maybe add WHERE a.AdjustmentActive = 'y' ( or whatever that contians )

 

Let me know if there's anything else I can help with, I've spent a lot of time on StackOverflow helping people with MySQL queries

 

From here, you'll need to fire that query off using PHP, and then loop over the rows rendering your HTML. Afraid I tend to deal more with Coldfusion/NodeJS nowadays though so can't really help too much there.

Edited July 15, 2016 by SimonHooker

[Stuart-Hannah]

Thanks for the reply, I have since restructured the database and done lots of reading to make sure I finally understood it.

 

Stuart