1

I'm trying to pull information from three different tables.

Table one is Monthly Ship Reports- where every report has a key to both the ship, and the officer.

Table two is the Ships Table, which has the ship's name. Table three is the officers table, which has the officer's name.

SELECT ship_monthly_report.*, 
       ships.ship_name, 
       officers.officers_title, 
       officers.first_name, 
       officers.last_name 
WHERE  report_key = '" . $_POST["monthly_report_ID"] . 
"LEFT JOIN Ships 
ON Ship_Monthly_Report.Ship_ID = Ships.Ship_Primary_Key 
LEFT JOIN Officers 
ON Ship_Monthly_Report.Capt_ID = Officers.Officers_PK 
ORDER BY report_line_number ASC

is the query I wrote.. which returns null. This is in a PHP script.

I guess what I'm asking is, can I do this? all the fields are correct. Am I butchering the query or am I trying to do something that is impossible?

chris85
  • 23,846
  • 7
  • 34
  • 51
Sam McLean
  • 71
  • 7
  • 1
    You are butchering it and opening yourself to injections. Can you add the PHP usage? It is best to write the query to work first then bring it to your application. – chris85 Aug 29 '17 at 20:23
  • Its more about wrong SQL syntax than about injections – Лев Макаренко Aug 29 '17 at 20:28
  • Your query is all messed up. Where goes after the joins. [SELECT reference](https://dev.mysql.com/doc/refman/5.7/en/select.html) – aynber Aug 29 '17 at 20:28
  • @ЛевМакаренко In my opinion, code that is vulnerable to SQL Injection **is** incorrect syntax. – GrumpyCrouton Aug 29 '17 at 20:30
  • Your syntax should be `select` columns `from` table (`join` with `on`s)* (zero or more `join`s) `where` parameters to limit. The where parameters should be parameterized. – chris85 Aug 29 '17 at 20:33
  • @GrumpyCrouton Its more about SQL code syntax as author mentioned before, also you are free to write about PHP and SQL injections by yourself – Лев Макаренко Aug 29 '17 at 21:10

1 Answers1

0

I think the way you structured the query is incorrect, try this:

SELECT t1.*, t2.Ship_Name, t3.Officers_Title, t3.First_Name, t3.Last_Name
FROM Ship_Monthly_Report t1
LEFT OUTER JOIN Ships t2
ON t1.Ship_ID = t2.Ship_Primary_Key
LEFT OUTER JOIN Officers t3
ON t1.Capt_ID = t3.Officers_PK
WHERE t1.report_key = *[your POST value]*
ORDER BY t1.report_line_number ASC

One of the issues with the query is that the WHERE came before the JOIN. I hope this helped!

Wishan
  • 111
  • 8