Last_insert_id

[ceci]

hi.

 

i have two tables tableA and tableB in a 1-many relationship.  I need the last_id from tableB.

 

I tried this but it returns all the items.

SELECT last_insert_id(item_id) as 'lastID', a.book_id, a.title  FROM tableA a LEFT JOIN tableB b on a.item_id = b.fk_item_id ORDER BY item_id DESC 

And if I use Limit 1, you guest it, it only returns one.

 

It should return 1 for each item on tableA.

 

How do I do this? Thank you.

 

Ceci

[PFMaBiSmAd]

last_insert_id() only returns the last id after an INSERT (and in some cases for an UPDATE) query has been preformed using the same database connection.

 

The way you are using it causes it to return the item_id value.

 

What exactly are you trying to accomplish?

[ceci]

sorry i was not clear the first time around.

 

I am looking for the latest item entered from the second table (tableB) using Left Join.

 

so if tableA has 2 items

ID  itemType

1  shirts

2  pants

 

 

and tableB has 2 orders for ID 1(shirts); 3 items for ID 2 (pants)

 

id fk_item_id    Orders

1  1                  red-shirt

2  1                  white-shirt        /* last_inster_id  */

3  2                  small-size

4  2                  large-size

5  2                  medium-size    /* last_insert_id for item ID2 */

 

I am interested in the last item ordered from tableB.  HOw do I get that?

 

In other words I want to do this but in one shot:

 

// First Query

$qryResultA = mysql_query("SELECT id, item_id FROM tableA");

While($row = mysql_fetch_array($qryResultA){

      // Second Query to get the last item.

      $getItemsFromTableB = mysql_query("SELECT *, id FROM tableB WHERE fk_item_id = '$row['item_id']' ORDER BY id DESC ")

      $innerRow = mysql_fetch_array($getItemsFromTableB);

}

 

[PFMaBiSmAd]

This will probably help - http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html