Click here to Skip to main content
15,112,217 members
Please Sign up or sign in to vote.
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
Updated 29-Mar-14 11:23am
v5
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,.

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.
   
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,.
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.
   
v3

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900