kraven2g kraven2g - 5 months ago 13
MySQL Question

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

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 ??

Answer
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.