How to interpret this CodeIgniter with SQL Block?

Question

1.00/5 (2 votes)

See more:

Hi,

I've been analyzing this sql code:

SQL

$this->db->select('ut.Username,ct.Title,ct.ContentID',false); 
                    $this->db->from('username_table AS ut');
                    $this->db->join('content_table AS ct', 'ct.UserID = ut.UserID');
                    $query = $this->db->get('content_table');
                    return $query->result_array();

I only have 5 rows in my content_table, but when I convert the returned query it echos 25, why is that?

ADDED Codeblock:

Maybe this will make things simplier but is there a way to just call the specific column to be use as a counting mechanism for the foreach something like:

PHP

foreach($contents.GETCOLUMNHERE as $contents_item)

so I'll just call the content column to mach the loops count?

PHP

<?php foreach ($contents as $contents_item): 
?>
    <h2><?php echo $contents_item['Title'] ?></h2>
    <div id="main">
        <?php echo $contents_item['Username'] ?>
    </div>
    <p><a href="contents/<?php echo $contents_item['ContentID'] ?>">View article</a></p>
<?php endforeach ?>

Posted 28-Mar-14 6:18am

KatsuneShinsengumi

Updated 29-Mar-14 10:23am

v5

Add a Solution

Comments

KatsuneShinsengumi 28-Mar-14 12:24pm

when I add a new row in the content_table it echos 36.

Rob Grainger 28-Mar-14 12:38pm

That looks nothing like SQL to me, PHP possibly?

PIEBALDconsult 28-Mar-14 12:48pm

Yeah, that's not SQL.

KatsuneShinsengumi 28-Mar-14 20:04pm

Sorry yeah, this is in codeigniter I have a tag of codeigniter above,.

2 solutions

Solution 1

Can you change it to $this->db->join('content_table AS ct', 'ct.UserID = ut.UserID', 'INNER');

My guess is its behaving like a cross join so each record is just joining to every other record in the table. Inner Join should take care of it assuming the userid in your user table is unique and doesn't have duplicates in it.

Posted 28-Mar-14 9:07am

j snooze

Comments

KatsuneShinsengumi 28-Mar-14 20:06pm

I'll check it later on once I get back and shoot some feedback here thanks,

KatsuneShinsengumi 29-Mar-14 15:27pm

I've changed the line join line, it still produce 36 although I only have 6 rows,.

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

KatsuneShinsengumi · Accepted Answer · 2014-03-29T11:57:00

Hey guys I get it now,

Here's the right code:

PHP

$this->db->select('ut.Username, ct.Title, ct.ContentID'); 
                    $this->db->from('username_table AS ut, content_table AS ct');
                    $this->db->where('ut.UserID = ct.UserID');
                    //$this->db->join('ct.UserID = ut.UserID');
                    $query = $this->db->get();
                    return $query->result_array();

The problem is this line:

PHP

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

Because I call this table it adds from the actual SELECT that I already made.

How to interpret this CodeIgniter with SQL Block?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to interpret this CodeIgniter with SQL Block?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us