INNER JOIN in mysql php

Question

I have a little problem with INNER JOIN in mysql query. I have two tables the first is 'kontrola' and 2nd is 'naruszenia' In 'kontrola' table I have rows:

• id
• podmiot
• miasto
• wszczeto
• zakonczono

• naruszenie_id (Foreign KEY on 'naruszenia' table) In my naruszenia table I have:

• id

• naruszenia

Now I want to display using INNER JOIN the naruszenie from 'naruszenia' table. I've create somethin linke this:

$listakontroli = $connecting->query("SELECT * FROM kontrola INNER JOIN naruszenia ON
 kontrola.naruszenie_id=naruszenia.id"); 

But the result is that when I want to display records in table I have changed the ID of first table(kontrola) and the naruszenia_id still showing id from naruszenia table. How to change it to display properly the word not id.

Answer 1

You need to use LEFT OUTER JOIN or separate the ID from the two tables. e.g.

$listakontroli = $connecting->query("SELECT kontrola.id as kid, naruszenia.id as nid, podmiot, miasto, etc* FROM kontrola INNER JOIN naruszenia ON  kontrola.naruszenie_id=naruszenia.id"); 

This way you can properly distinguish the displayed IDs

Answer 2

You could use explicit column name and refer to both the table (in this case using k an n) eg:

$listakontroli = $connecting->query("SELECT k.id
           , k.podmiot
           , k.miasto
           , k.wszczeto7
           , k.zakonczono
           , n.naruszenia 
      FROM kontrola k
      INNER JOIN naruszenia  n ON  k.naruszenie_id=n.id");