Can you define vars in loop in pure sql?

sneakyimp

I am charged with working on an SQL script which is supposed to help migrate a database from an old PHP project to a new one. The new PHP project requires alterations to the database schema which are mostly just new fields and such.

However, some changes require that I create a new record in some table, remember the primary key value that was inserted and insert a variety of related records in another table. I'm hoping to be able to make changes like this without also introducing PHP scripting in the process. Is it possible to remember the last insert_id of Table A so that I can insert records in Table X, Table Y, and Table Z? Does sql support variable definition? What about looping?

Piranha

The bad(??) news is that it is not possible in pure SQL.

The good news is that most databases provide some form of language to extend SQL. For MySQL you can see more about it here, you can get insert_id, insert into many tables, use loops and of course use variables among other things.

Just remember to develop it on a test server since it will mess the database up a couple of times before you get it right.

Sxooter

sneakyimp wrote:
I am charged with working on an SQL script which is supposed to help migrate a database from an old PHP project to a new one. The new PHP project requires alterations to the database schema which are mostly just new fields and such.

However, some changes require that I create a new record in some table, remember the primary key value that was inserted and insert a variety of related records in another table. I'm hoping to be able to make changes like this without also introducing PHP scripting in the process. Is it possible to remember the last insert_id of Table A so that I can insert records in Table X, Table Y, and Table Z? Does sql support variable definition? What about looping?

Have you got an example of what you're trying to do? It might or might not be possible in straight sql, but I'm guessing it won't be. Can't be sure until we kinda see more clearly what you're trying to do.

sneakyimp

I have a table full of Users that has a field 'headshot_url'...something like this:

USERS
id     name    headshot_url
1      Tom     /images/tom.jpg
2      Dick    

3      Harry   /images/obscene_crotch_photo.jpg

Rather than have a direct link to the photo url which is not particularly flexible, we want to implement an asset management system for photos or logos or music files, etc. which will apply to other types of tables as well. In this particular case, we need to add a photo_asset_id field to the USER table and create a corresponding record in the asset table. So that table should end up looking something like this:

USERS
id     name    headshot_asset_id
1      Tom     1
2      Dick      NULL
3      Harry   2

where the asset table looks something like this:

ASSETS
id   filename
1    images/tom.jpg
2    /images/obscene_crotch_photo.jpg

As you can see, we need to insert records into another table and map the insert id's accordingly.

My client is using MySQL and I cannot change that so any magic PGSQL would be neat to see but will not help me here.

Sxooter

I don't think you'll need any special postgresql things to make that work.
I would consider making it a bit more generic so you can have an intermediate table that defines the type your accessing and a table to hold the things.

So, you'd have a master table with the user_id in it, then a join table of something like:

user_id references user(user_id), file_type references type_list(id), file_info references file_info(id)

and the file_type would be something like:
file_type: id pk, description text, mime_type text -- that last one could reference a list of mime types if you wanted to make sure they're valid.

file_info would be something like:
file_info: id pk, description text, path text, etc...

Does that make sense? I'd draw an ERD, but I don't have any good software to do it on my laptop.

sneakyimp

I'm not sure that I follow what you suggest. The primary problem as far as I can tell is:

1) for each record with a non-empty headshot_url field in USERS, I need to create a record in ASSETS, a table which already exists and contains data from other tables.
2) I need to a new field to USERS called headshot_asset_id and populate this field with the ids of the records newly inserted into ASSETs

The heart of the problem is that I don't currently know how to perform sql queries which can a) loop or b) retain an inserted id from ASSETS long enough to perform a query to update USERS with that value.

Compounding this problem is the fact that not every record in USERS has a headshot.

YAOMK

sneakyimp wrote:
I am charged with working on an SQL script which is supposed to help migrate a database from an old PHP project to a new one. The new PHP project requires alterations to the database schema which are mostly just new fields and such.

However, some changes require that I create a new record in some table, remember the primary key value that was inserted and insert a variety of related records in another table. I'm hoping to be able to make changes like this without also introducing PHP scripting in the process. Is it possible to remember the last insert_id of Table A so that I can insert records in Table X, Table Y, and Table Z? Does sql support variable definition? What about looping?

I believe it is possible with stored procedures, take one of my mysql UDFs for example, this is a random code generator that loops and checks against a table to see that the number doesn't already exist:

-- Random numeric code generator, max-limit of 98,999 codes.
DELIMITER //
DROP FUNCTION IF EXISTS random_code //
CREATE FUNCTION RANDOM_CODE()
RETURNS INTEGER
BEGIN
DECLARE team_code INT UNSIGNED;
DECLARE code1 TINYINT UNSIGNED;
DECLARE code2 TINYINT UNSIGNED;
DECLARE code3 TINYINT UNSIGNED;
DECLARE code4 TINYINT UNSIGNED;
DECLARE code5 TINYINT UNSIGNED;
DECLARE commodity TINYINT UNSIGNED;
DECLARE counter INT UNSIGNED;
DECLARE search INT UNSIGNED;
    SET counter = 0;
    loopy: LOOP		
    SET commodity = (FLOOR(1 + (RAND() * 9)));
    IF commodity > 9 then
    SET commodity = 1;
    END IF; 
    SET code1 = commodity;
    SET commodity = (FLOOR(0 + (RAND() * 9)));
    IF commodity > 9 then
    SET commodity = 0;
    END IF; 
    SET code2 = commodity;
    SET commodity = (FLOOR(0 + (RAND() * 9)));
    IF commodity > 9 then
    SET commodity = 0;
    END IF; 
    SET code3 = commodity;
    SET commodity = (FLOOR(0 + (RAND() * 9)));
    IF commodity > 9 then
    SET commodity = 0;
    END IF; 
    SET code4 = commodity;
    SET commodity = (FLOOR(0 + (RAND() * 9)));
    IF commodity > 9 then
    SET commodity = 0;
    END IF; 
    SET code5 = commodity; 
    SET team_code = concat(code1,code2,code3,code4,code5);
    SELECT team_id INTO @search FROM team where team_id = @team_code;
    IF team_code = search AND counter < 99999 then
    SET counter = counter + 1;
    ITERATE loopy;
    ELSE
    LEAVE loopy;
    END IF;
    END LOOP;
RETURN team_code;
END; //

DELIMITER ;

You can read more on stored procedures here.

Sxooter

You're not getting what I'm saying. Instead of adding a column to your users for each type of thing you could add, use an intermediate table that defines those things. I.e. need to have a head shot for some people? Add head shot to the table that holds the categories, then add an entry to the join table pointing to the user table's user record and the catgories category for head shots, and use a generic field in the join table to hold te information needed for that headshot.

Next, need to add a bio, you can then add that to the multi-join table I was talking about.

Then, when you join those three tables together, the answers will just kinda fall out into your lap. left join if you want all records whether they have an entry in that category, inner join if you don't

sneakyimp

OK Sxooter I'm starting to think I understand what you mean and I'm digging it. You should have a 'donate' link in your signature.