emo_noel10 emo_noel10 - 26 days ago 13
SQL Question

CodeIgniter, Active Records getting second table values

I have a liitle bit confuse about this...

I create just a simple blog and I can't get my desired output from the post, all I wanted is I want to get the firstname and lastname from the second table by using the

userID
from the first table.

controllers/posts.php:

<?php

class Posts extends CI_Controller {

function __construct() {
parent::__construct();
$this->load->model('post');
}

function index() {
$data['posts'] = $this->post->get_posts();
$data['users'] = $this->post->get_users();
$this->load->view('post_index', $data);
}

function post($postID) {
$data['post'] = $this->post->get_post($postID);
$this->load->view('post', $data);
}

function correct_permissions($required) {
$user_type = $this->session->userdata('user_type');
if ($required == "User") {
if ($user_type) {
return true;
}
} elseif ($required == "Blogger") {
if ($user_type == "Blogger") {
return true;
}
}
}

function deletepost($postID) {
$user_type = $this->session->userdata('user_type');
if ($user_type != 'Blogger') {
echo "<script>alert:('Please log in to continue.');</script>";
redirect(base_url());
}
$this->post->delete_post($postID);
redirect(base_url() . 'posts');
}

}


models/post.php

<?php

class Post extends CI_Model{
function get_posts($num=50,$start=0){
$this->db->select()->from('posts')->where('active',1)->order_by('date_added','desc')->limit($num,$start);
$query = $this->db->get();
return $query->result_array();
}

function get_post($postID){
$this->db->select()->from('posts')->where(array('active'=>1,'postID'=>$postID))->order_by('date_added','desc');
$query=$this->db->get();
return $query->first_row('array');
}

function get_user($userID){
$this->db->select()->from('users')->where(array('userID'=>$userID));
$query=$this->db->get();
return $query->first_row('array');
}

function get_users(){
$userID = $this->session->userdata('userID');
$this->db->select('firstname','lastname')->from('users')->where('userID',$userID);
$query = $this->db->get();
return $query->result_array();
}

function insert_post($data){
$this->db->insert('posts',$data);
return $this->db->insert_id();
}

function update_post($postID,$data){
$this->db->where('postID',$postID);
$this->db->update('posts',$data);
}

function delete_post($postID){
$this->db->where('postID',$postID);
$this->db->delete('posts');
}
}


and my code from views/post_index.php

<div class="panel">
<?php if (!isset($posts)) { ?>
<p>There are currently no posts on my blog.</p>
<?php
} else {
foreach ($posts as $row) {
?>
<div class="panel-heading">
<h2 href="<?= base_url() ?>posts/post/<?= $row['postID'] ?>"><?= $row['title'] ?></h2>
</div>
<div class="panel-body">
<p><?= substr(strip_tags($row['post']), 0, 200) . "..." ?></p>
<br><br>
<div class="panelver">
<h6 href="<?= base_url() ?>posts/post/<?= $row['userID'] ?>">Posted by:
<?php
if($row['userID'] == 0) {
echo "Someone";
} else {
echo $this->db->select('firstname','lastname')->from('users')->where('userID',$row['userID']);
}
?></h6>
<p href="<?= base_url() ?>posts/post/<?= $row['postID'] ?>">Added last: <?= $row['date_added'] ?></p>
</div>
<p><a href="<?= base_url() ?>posts/post/<?= $row['postID'] ?>">Read More</a> - - - <a href="<?= base_url() ?>edit/editpost/<?= $row['postID'] ?>">Edit</a> | <a href="<?= base_url() ?>posts/deletepost/<?= $row['postID'] ?>">Delete</a></p>
<hr/>
</div>
<?php
}
}
?>
</div>


the output:

Blog Posts


this is a post
blah, blah, blah..... the "posted by:" must output the author name getting userID from the second table value using the userID from the first table as the output below, help me! T_T...

Posted by: 2

Added last: 2014-09-20 11:30:00

Read More - - - Edit | Delete

but when i do this code:

<div class="panel">
<?php if (!isset($posts)) { ?>
<p>There are currently no posts on my blog.</p>
<?php
} else {
foreach ($posts as $row) {
?>
<div class="panel-heading">
<h2 href="<?= base_url() ?>posts/post/<?= $row['postID'] ?>"><?= $row['title'] ?></h2>
</div>
<div class="panel-body">
<p><?= substr(strip_tags($row['post']), 0, 200) . "..." ?></p>
<br><br>
<div class="panelver">
<h6 href="<?= base_url() ?>posts/post/<?= $row['userID'] ?>">Posted by:
<?php
if($row['userID'] == 0) {
echo "Someone";
} else {
echo $this->db->select('firstname','lastname')->from('users')->where('userID',$row['userID']);
}
?></h6>
<p href="<?= base_url() ?>posts/post/<?= $row['postID'] ?>">Added last: <?= $row['date_added'] ?></p>
</div>
<p><a href="<?= base_url() ?>posts/post/<?= $row['postID'] ?>">Read More</a> - - - <a href="<?= base_url() ?>edit/editpost/<?= $row['postID'] ?>">Edit</a> | <a href="<?= base_url() ?>posts/deletepost/<?= $row['postID'] ?>">Delete</a></p>
<hr/>
</div>
<?php
}
}
?>
</div>


this will happen:

Blog Posts

this is a post
blah, blah, blah..... the "posted by:" must output the author name getting userID from the second table value using the userID from the first table as the output below, help me! T_T...

Posted by:
A PHP Error was encountered

Severity: 4096

Message: Object of class CI_DB_mysql_driver could not be converted to string

Filename: views/post_index.php

Line Number: 42

Added last: 2014-09-20 11:30:00

Read More - - - Edit | Delete

hope you all understand the structure of my code, more help will much appreciate.
godbless you all!

P.S: I'm a noob programmer and new to CodeIgniter :)

Answer

Ok.So what i get from your question is you have two tables with userId stored in one and user details in another.So try this as you need to get the row before echoing it.

echo $this->db->select('firstname','lastname')->from('users')->where('userID',$row['userID'])->get()->row();

the above code can also be written like this :

$this->db->select('firstname','lastname')->where('userID',$row['userID'])->get('users')->row();

Let me know if you have any doubts.

Comments