Login

04-02-2013, 04:33 PM

[eluser]ccalby[/eluser]
Hello,

I am trying to produce a link using the 'id' from the table ideas.
The issue, is that (because of the LEFT JOIN) it uses the users.id rather than the ideas.id in the link.

Here is the controller:

Code:
public function index()

 {

  $query = $this->database->get_latest();

  $data['ideas'] = $query;

  $this->load->view('ideas', $data);

 }

Here is the model:

Code:
function get_latest()

 {

  $this->db->select('*');

  $this->db->from('ideas');

  $this->db->join('users', 'users.id = ideas.creator');

  $query = $this->db->get();

  return $query->result();

 }

Here is the view:

Code:
&lt;html lang="en"&gt;

&lt;head&gt;

 &lt;meta charset="utf-8"&gt;

 &lt;title&gt;Welcome to CodeIgniter&lt;/title&gt;

 &lt;link rel="stylesheet" href="&lt;?php echo $this-&gt;config-&gt;item('base_url');?&gt;/assets/style.css"&gt;

&lt;/head&gt;

&lt;body&gt;

<div id="container">

 <h1>Welcome to CodeIgniter!</h1>

 <div id="body">

  <table width="100%" border="1" cellpadding="1">

    <tr>

   <th scope="col" width="10%">Votes</th>

   <th scope="col" width="50%">Title</th>

   <th scope="col">Creator</th>

    </tr>

   &lt;?php 

   $siteurl= $this->config->item('base_url'); ?&gt;

  &lt;?php if(isset($ideas)) : foreach($ideas as $row) : ?&gt;

  &lt;?php

   echo "<tr>";

   echo "<td>$row->votes</td>";

   echo "<td>";

   echo "<a >id\">";

   echo "$row->title";

   echo "</a>";

   echo "</td>";

   echo "<td>$row->username</td>";

   echo "</tr>";

  ?&gt;

 &lt;?php endforeach; ?&gt;

  </table>

 &lt;?php else : ?&gt;

 <h2>NO IDEAS YET!</h2>

 &lt;?php endif; ?&gt;

 </div>

 &lt;?php echo $this->load->view('templates/footer') ?&gt;

</div>

&lt;/body&gt;

&lt;/html&gt;

Any help would be great thanks Smile

04-02-2013, 04:50 PM

[eluser]RaGe10940[/eluser]
In your SQL statements/active record you should be able to explicitly state your table.row like in my model example :

Code:
function waiting() {

 try {

     $sql = "SELECT DISTINCT

     session.session_id as id,

     session.anum,

     student.first,

if you notice my session.session_id as id, session. < - - - table session_id < - - - - row in the table and in mysql using AS after you can then later give it a unique value to you.

Now I am not 100% sure as I have no use for "active record" as I just use PDO, but I am pretty sure you should be able to explicitly state which table.row you are trying to use AS id.

I hope this offers some light to your question in hopes of an answer.

04-03-2013, 02:57 AM

[eluser]TheFuzzy0ne[/eluser]
Rather than just using $this->db->select('*'), why not specify the actual columns you want? That way, you can alias any clashes.

Code:
$this->db->select(array(

    'user.id AS user_id',

    'ideas.creator AS creator_id',

    'ideas.id AS idea_id',

    /* ... */

));

04-03-2013, 04:23 AM

[eluser]RaGe10940[/eluser]
Even better solution is Fuzzy's. I knew that (well I hoped lol) that Active Record would allow you to alias a row. Thanks for the clarification Fuzzy.

04-03-2013, 10:47 AM

[eluser]ccalby[/eluser]
Thanks very much guys!! Works very well.