Can't retrieve all records from database [SOLVED]

[eluser]alrightythen[/eluser]
Hi

I'm developping a web site where I display all rows from a database on one of the pages.

Now when I add records everything is fine, the new record displays on the page.

But when I delete one and then add one record the last record does not display on the page. I've done some testing and apparently the retrieving stops when the id is not equal to the previous id+1.

So let's say I have a table Items with the following rows.

item_ID | item_Name | item_Description
----------------------------------
1 | item one | cool item!
2 | item two | awesome item!
5 | item five | ids 3 & 4 deleted

The page would only display items one and two. And not item five. So from now on the page will always display the first two items and not the whole table.

Does someone know what I'm doing wrong here?

This is my controller

Code:

if($q = $this->start_model->get_records())
{
    $data['records'] = $q;
}

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

My model

Code:

function get_records(){
    $this->db->select('*');
    $this->db->from('items');
    $this->db->join('subcategories', 'categories.cat_ID = items.item_ID');
        
    $q = $this->db->get();
    $table_row_count = $this->db->count_all('items');

    if($q->num_rows() > 0){

        //echo $q->num_rows();
        //echo '<br/>';
        //echo $table_row_count;

        foreach ($q->result() as $row){
                $data[] = $row;
        }
        
        return $data;
    }
}

[eluser]andrewtheandroid[/eluser]
hey i'm not the most experienced when it comes to joins but ill give it a go.


have you tried doing a manual statement? like

Code:

$sql = 'SELECT * FROM items i, subcategories s WHERE s.cat_ID = items.item_ID';
$this->db->query($sql);

and is that "subcategories" meant to be "categories" in ur code or vise versa..

[eluser]alrightythen[/eluser]
[quote author="andrewtheandroid" date="1257455046"]hey i'm not the most experienced when it comes to joins but ill give it a go.


have you tried doing a manual statement? like

Code:

$sql = 'SELECT * FROM items i, subcategories s WHERE s.cat_ID = items.item_ID';
$this->db->query($sql);

and is that "subcategories" meant to be "categories" in ur code or vise versa..[/quote]


Yes it's suppose to be categories, sorry bout that. But that wasn't the problem.

I've did it your way and there's still only 2 records. Did a sql statement in phpmyadmin en still only got 2 records so it might be my sql statement that's wrong . I just don't see it.

This is my sql statement..

Code:

SELECT * FROM items JOIN categories ON categories.cat_ID = items.item_ID

I'm using innodb tables but my database engine is myISAM I couldn't change that in mamp. But I don't think that's the problem.


So it's not a codeigniter problem anymore but I could still use some help.

[eluser]andrewtheandroid[/eluser]
ahh ok yeh i was reading up on joins for my current project a few hours ago actually. i needed to join 3 tables. I was reading
This

Could it be your case is one of those examples mentioned?

[eluser]andrewtheandroid[/eluser]
Here's my script i whacked in to test.. i did what u did whacked in 5 items and 5 descriptions.. deleted 3 & 4 and it gave me 1,2 and 5 :S

Code:

CREATE TABLE categories (categoryid INT NOT NULL, categoryname VARCHAR NOT NULL, categorydescription VARCHAR NOT NULL, PRIMARY KEY (categoryid));

CREATE TABLE items (itemid INT NOT NULL AUTO_INCREMENT, itemname VARCHAR(11) NOT NULL, itemdescription VARCHAR NOT NULL, PRIMARY KEY (itemid));

select * from items i, categories c where i.itemid = c.categoryid

when you manually view your data from both tables you sure the item 5 id corresponds?

[eluser]alrightythen[/eluser]
[quote author="andrewtheandroid" date="1257457192"]ahh ok yeh i was reading up on joins for my current project a few hours ago actually. i needed to join 3 tables. I was reading
This

Could it be your case is one of those examples mentioned?[/quote]


Ugh ok it finally works! Thanks andrew.

So I did a left join that would display all unmatching rows and I had NULL values in the joined fields. I joined on the wrong fields
instead of subcat_ID = profile_ID I'm suppose to join on subcat_ID = profile_SubcatID

Thanks again andrew

[eluser]andrewtheandroid[/eluser]
also can u describe what's in your categories table? since you are joining on cat_id = item_id.. i'm guessing there is a category that corresponds to each item? but then can't u just put that info into item?

or.. do you mean you have a category which many items belong to and u are trying to connect item into the category?

item.category = catagory.name ?

sorry if these sound like noob questions my sql isn't that fantastic haha

[eluser]andrewtheandroid[/eluser]
sweet! nps.