How to select a column in a left join if there is 2 columns with the same name ? [MySql]

Question

I have made a LEFT JOIN to get the values from 2 tables from my database.
The query is like this:

SELECT *
FROM thread
  LEFT JOIN comments ON thread.id_thread = comments.id_thread
WHERE id_type = '1'
ORDER BY data DESC, hour DESC

Then I output the values this way:

<?

while($row = mysqli_fetch_array($query))
{
echo '<div class="col-md-1"></div>';
echo '<div class="col-md-11">';
echo  $row['title'] ."<br><br>".$row['content']."<br><br>";
echo  $row['content_com'];
echo '<div class="col-md-2 pull-right">'. "date: ".$row['data']."<br>"."author: ".'<a href ="/user.php?id='.$row['username'].'">'.$row['username'].'</a>'.'</div>' ."<br><br>";
echo '<form role="form" action="commit.php" method="post"><div class="col-md-offset-1 col-md-9"><input class="form-control" type="text" name="comm"><input type="hidden" name="thread_id" value="'.$row['id_thread'].'"></div></form> <br><br><hr><br>';
echo '</div>';
}

mysqli_close($connect);
?>

Then in the commit.php (form action):

<?php
session_start();

  if(isset($_SESSION['id']))
  {
    $servername = "mysql9.000webhost.com";
    $username = "a5461665_admin";
    $password = "xenovia1";
    $dbname = "a5461665_pap";

    $connect  = mysqli_connect($servername, $username, $password, $dbname);

    $id = (isset($_GET['id'])) ? $_GET['id'] : $_SESSION['id'];

    $ctn = $_POST["comm"];

      $com = mysqli_query($connect,"INSERT INTO comments(content_com,id_thread) values ('".$ctn."', '".$_POST['thread_id']."')");

      header("location:javascript://history.go(-1)");


    if (!$connect) {
        die("Connection failed: " . mysqli_connect_error());
    }

}
else
{
  header(" url=index.php");
}


 ?>

My problem is that the hidden input box is passing to the form action the field id_thread from the table comments but I want it to pass the field id_thread from the table threads, how do I do that ??

You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php). Since you're using MySQLi, you should look into [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — M. Eriksson, Jul 03 '16 at 20:09

Iceman · Accepted Answer · 2016-07-03T21:19:40.473

6

SELECT *, thread.id_thread as mycol
FROM 
thread LEFT JOIN comments 
ON thread.id_thread=comments.id_thread 
WHERE thread.id_type = '1' 
ORDER BY data desc, hour desc

Specify column name with the table and alias it. So, SELECT * for all columns as before, now taking thread.id_thread and alias it to mycol. This will be now available as mycol and no more-name clashing.

edited Jul 03 '16 at 21:19

answered Jul 03 '16 at 20:07

Iceman

6,035
2
23
34

both tables have the field id_thread, but I want the thread -> id_thread and it gives me comments -> id_thread – kraven2g Jul 03 '16 at 20:09
@kraven2g - First off, you shouldn't use `SELECT * FROM...`... you should specify what columns you want. If two columns have the same name, use the `AS` key word, like: `SELECT table1.col1, table1.col2..., table2.col1 AS col1_2...` and so on. – M. Eriksson Jul 03 '16 at 20:12
I still need all my other columns that's why I have made a select * – kraven2g Jul 03 '16 at 21:03
@kraven2g specify all the names else try `SELECT *, thread.id_thread as mycol`. works with most engines – Iceman Jul 03 '16 at 21:08

jDolba · Answer 2 · 2016-07-03T20:18:22.043

you can use "alias" or the table name - then specify which column you want to use

SELECT T.*, comments.id_thread AS comment_thread_id
FROM thread T
LEFT JOIN comments 
    ON thread.id_thread=comments.id_thread 
WHERE id_type = '1' ORDER BY  data desc, hour desc

see, T is alis for table name, thread, T.* will select all cols from thread table, comments.id_thread will take just column ID from table comments named as comment_thread_id

score 1 · Answer 3 · edited May 23 '17 at 12:32

1

Next to using aliases/tablename you can also use USING() instead of ON to join the tables.

SELECT T.*, comments.id_thread AS comment_thread_id
FROM thread T
LEFT JOIN comments 
    USING(id_thread) 
WHERE id_type = '1' ORDER BY  data desc, hour desc

Here is a nice explanation of the difference between both methods: https://stackoverflow.com/a/11367066/3595565

edited May 23 '17 at 12:32

Community

1
1

answered Jul 03 '16 at 21:17

Philipp

2,787
2
25
27

How to select a column in a left join if there is 2 columns with the same name ? [MySql]

3 Answers3