PHP + SQL Inner Join?

[LucasFerraz]

Hello, i guess this is the last script i need to release 1.6v of fixed macc

The problem is: [red]".$comment['author']."[/red] its returing only ID as it is in table commends and column author.
How can I get this id as player table.id and return player name?

Example: Select author from comments where body = ".strip_tags($comment['body'])."

<?php 
global $config; 
$ots = POT::getInstance();
$ots->connect(POT::DB_MYSQL, connection());
$SQL = $ots->getDBHandle();
$ide = new IDE;
try { $ide->loadInjections("view_news"); }catch(Exception $e) { error($e->getMessage());}
	echo "<div class='news'>";
	echo "<div class='message'><div class='NewsHeadline'> 
  <div class='NewsHeadlineBackground' style='background-image:url(".WEBSITE."/templates/tibiacom/images/content/newsheadline_background.gif)'> 
    <div class='NewsHeadlineText'><div style='margin-left:-17%'>".$news[0]['title']."</b></div></div> 
  </div> 
</div></div>
";
	echo "<div class='newsBody'>".$news[0]['body']."</div>";
	echo "<div class='newsFooter'></div>";
	echo "</div>";
	
	echo "<h1>Comments</h1>";
	if(!$ide->isLogged())
		alert("You need to be logged in to write comments.");
	else {
		if(count($characters) == 0)
			error("You need to have a character on the server to comment on the news.");
		else {
			echo error(validation_errors());
			echo form_open(WEBSITE."/index.php/home/view/".$id);
				echo "<br /><label>Character</label><select name='character'>";
					foreach($characters as $character) {
						echo "<option value='".$character['id']."'>".$character['name']."</option>";
					}
				echo "</select><br /><br />";
				echo "<textarea style='width: 99%;' name='body'>".@$_POST['body']."</textarea><label></label>";
				echo $captcha;
				echo "<br/><label>Captcha</label>";
				echo "<input type='text' name='captcha'/><br/>";
				echo "<input type='submit' value='Comment'>";
			echo "</form>";
		}
	}
	echo "<center>".$pages."</center>";
	foreach($comments as $comment) {
		if($ide->isAdmin())
			$delete = "<a href='#' onClick=\"if(confirm('Are you sure you want to delete this comment?')) window.location.href='".WEBSITE."/index.php/home/delete_comment/".$comment['id']."';\" ><img src='".WEBSITE."/public/images/false.gif'></a>";
		else if($ide->isLogged()) {
			if(@in_array($comment['author'], $characters)) {
				$delete = "<a href='#' onClick=\"if(confirm('Are you sure you want to delete this comment?')) window.location.href='".WEBSITE."/index.php/home/delete_comment/".$comment['id']."';\" ><img src='".WEBSITE."/public/images/false.gif'></a>";
			}
			else
				$delete = "";
		}
		else
			$delete = "";
			
		echo "<div class='comment'>";
		echo "<div class='commentBody'>".strip_tags($comment['body'])."<br>
".$delete." by <a href=\"".WEBSITE."/index.php/character/view/".$comment['author']."\">".$comment['author']."</a>
</div>";
		echo "";
		echo "</div>";
	}
	echo "<center>".$pages."</center>";
?>

 

[Ninja]

home_model.php

    public function getComments($id) {        
        require("config.php");
        $this->load->database();
        $this->load->helper("url");
        $page = $this->uri->segment(4);
        $page = (empty($page)) ? 0 : $page;
        return $this->db->query("SELECT `comments`.`id`, `body`, `time`, `author`, `players`.`name` FROM `comments` LEFT JOIN `players` ON `players`.`id` = `comments`.`author` WHERE `news_id` = '".$id."' ORDER BY comments.id DESC LIMIT ".$page.", ".$config['commentLimit'])->result_array();
    }

Then replace ".$comment['author']." with ".$comment['name']."

 

[LucasFerraz]

I will test, thanks

home_model.php

    public function getComments($id) {        
        require("config.php");
        $this->load->database();
        $this->load->helper("url");
        $page = $this->uri->segment(4);
        $page = (empty($page)) ? 0 : $page;
        return $this->db->query("SELECT `comments`.`id`, `body`, `time`, `author`, `players`.`name` FROM `comments` LEFT JOIN `players` ON `players`.`id` = `comments`.`author` WHERE `news_id` = '".$id."' ORDER BY comments.id DESC LIMIT ".$page.", ".$config['commentLimit'])->result_array();
    }

Then replace ".$comment['author']." with ".$comment['name']."

- - - Updated - - -

Working, thank you man.
I will add your credits