INSERT/SELECT with differing column count

[mwstewart]

Hello all, I just need a little help with an INSERT statement.

I have 2 tables, one for confirmed users and one for temporary. I'm using an INSERT query to transfer data between the 2 once a user validates their registration by Email:

/move user out of the unactivated table...
			mysql_query("INSERT INTO tblUsers
			SELECT UserID, UserName, Password, FirstName, Surname, Email, Street, City, County, PostCode, Phone, CountryID, RegisteredOn 
			FROM tblUsersUnactivated  

			WHERE ActivationID = '$_GET[activationid]' ") or DB_Error();

		//and in to the main user table
		mysql_query("DELETE FROM tblUsersUnactivated WHERE ActivationID = '$_GET[activationid]' ") or DB_Error();  

This all worked fine when the tables had the same number of columns, but I've now added 3 colums to the registered users column and now I'm getting a #1136. How do I get around this? I've set up default values for the 3 new colums so I'm hoping it's just a case of adding '', '', '' in to my query somewhere (but where...?)

So in summary:
I want to INSERT data into tblUsers - some fields taken from tblUsersUnactivated , and some I specify.

Thanks in advance,
Mark

[Piranha]

The best thing is to redesign the database to have everyone in the same table. You can add a column STATUS that can be set to different values (unconfirmed, confirmed, left, honorymember...) and when you do queries you check what value that column is.

But if you want it in 2 tables I can think of two different ways. The first:

$sql = "INSERT INTO tblUsers (id, name, password.... all the columns you want to use from the other table)
            SELECT UserID, UserName, Password, FirstName, Surname, Email, Street, City, County, PostCode, Phone, CountryID, RegisteredOn
            FROM tblUsersUnactivated  

            WHERE ActivationID = '$_GET['activationid']' ";

Then you can update table with the other values.

The second is to first select the values, handle them in PHP and then insert them in the second table.

By the way, you should look at [man]mysql_real_escape_string[/code] to avoid SQL injection.

[mwstewart]

Many thanks for your help, Piranha. I used the second method you suggested which was to use a seperate SELECT query - this worked great.

Thanks again,
Mark