MySQL: Inserting data based on insert_id, in a single query?

[eluser]mddd[/eluser]
I am working on an import script. It will handle large amounts of data so I want to make it as efficient as I can. Part of the script is writing a product to the database, along with a record in a table that links products to categories.

So, basically:

Code:

INSERT INTO products (col1, col2) VALUES (val1, val2)
INSERT INTO products_categories (product, category) VALUES (product_id, category_id)

Of course, I only know the product_id for query #2 after I have inserted the product into the database through query #1.
So this will always take two calls to the database. Or is there a way to make both into one query? Something like (and I know this is not functioning code):

Code:

INSERT INTO products_categories (product, category) VALUES (
INSERT_ID_FROM_QUERY( INSERT INTO products (col1, col2) VALUES (val1, val2) ), category_id
)

Does something like this even exist?

[eluser]erik.brannstrom[/eluser]
I don't know of any way to accomplish this, at least not so you would actually get any performance gain. However, if you are to run a large amount of queries at the same time you should read up on using transactions. Maybe you already know about this, but if not, check out http://ellislab.com/codeigniter/user-gui...tions.html and do some Google searches on the subject.

[eluser]mddd[/eluser]
Thanks Erik. Good tip to look to transactions to ensure data integrity. I agree that they will not help much in terms of performance..

If anyone has any more tips?

[eluser]mattpointblank[/eluser]
Unless this code is going to run on every pageload, it's not really an issue running it as two queries. It's better to have code that requires multiple database hits to be used for adding/updating content, rather than viewing it or something instead. Unless your site is getting tons of use this should be fine.

One alternative could be a stored procedure, but that will still end up doing two queries either way.

EDIT: You can insert several rows at once with an INSERT query. Maybe if you're doing lots of bulk inserts, loop through all your first INSERT queries, store the resulting ID in an array (along with the data for the second query) and run all of those queries at the end.

[eluser]resolv_25[/eluser]
This is not uncommon situation, need of the last insert ID.
PHP has function to accomplish that, and CI as well.

It can be something like this:

Code:

$values = array('col1' => $val1, 'col2' => $val2);

$this->db->insert($this->products, $values);
$last_id =  $this->db->insert_id();

// insert $last_id into products_categories

[eluser]mddd[/eluser]
@resolv_25:
Thanks. I know how 'insert_id' works. The trouble is, I am doing large imports on a regular basis. This puts a strain on the database. So I am optimizing the number of calls I have to do. That is why I was looking to combine as many queries as I can.

@mattpointblank:
Thanks. Combining inserts does limit the number of queries and that is a very good thing.

@all:
I haven't been able to find a way to insert data into two tables from a single query while. Finally, I made a system to generate a unique id for every record in advance, and use that id in both tables. Eliminating the need to know the insert_id from the database at all. This (not checking the id), combined with putting 10 or 20 rows in each insert made the whole import about 20 times faster.

[eluser]resolv_25[/eluser]
Ok, I see what you mean.
I suggest you to use Stored Procedures, great SQL way of programming.

I hope that you are using MySQL Query Browser, phpMyadmin has a problems using Stored Proedures properly.

So, within a MySQL Query Browser, right click - Create Stored Procedure
Give a name, let's say ImportData
Then within a procedure put code like this:

Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `YourDB`.`ImportData`$$
CREATE DEFINER=`user`@`localhost` PROCEDURE `YourDB`.`ImportData` (
    IN val1Param INTEGER,
    IN val2Param INTEGER,
    IN category_idParam INTEGER
)
BEGIN
    DECLARE lastid INTEGER;
    INSERT INTO products(col1,col2) VALUES (val1Param,val2Param);
    SET lastid = (SELECT LAST_INSERT_ID());
    INSERT INTO products_categories (product, category) VALUES (lastid, category_idParam);  

END$$

DELIMITER ;

Execute it within MySQL Query Browser (button or alt+enter).
You can create Stored Procedure from a command line as well if you don't like MySQL Query Browser.

IN val1Param INTEGER (and so on) are values that you are putting in the first table.
I have put INTEGER for example, but you must use exactly the same Data Type as is in your column.
Most probably your ID is INTEGER.

Once it is creating, calling Procedure is simple, within the model:

Code:

$q = "call ImportData($val1Param, $val2Param, $category_idParam)";  
$this->db->query($q);

Of course, you will put as many parameters you want within the procedure.
If your category_id needs to be taken from another table, use the same way - declare another variable within the stored procedure which will get this value.

This works fast, in one connection you put data in 2 or more tables, I guess this is what you want.

[eluser]mddd[/eluser]
Thanks resolv_25. You are right that it is a good way to reduce traffic between the php server and mysql server.
I should consider using procedures more often.