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...?)

 

Thanks in advance,

Mark

[pocobueno1388]

<?php

//move user out of the unactivated table...

mysql_query("INSERT INTO tblUsers (col1, col2, col3, col4, etc.) VALUES ('$val1', '$val2', '$val3', '$val4')");

mysql_query("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();  

?>

 

You never finished your INSERT query.....All you do is list all the columns in the database then put what value you want to insert into it.

[mwstewart]

Hi there, thanks. That query pulls the specified fields (UserID, UserName, Password, FirstName, Surname, Email, Street, City, County, PostCode, Phone, CountryID, RegisteredOn) from table tblUsersUnactivated and inserts them in to tblUsers - it all works fine when the two tables have identical fields, but now they don't.

 

I need to INSERT fields from the second table tblUsersUnactivated as well as 3 additional fields not on the second table (which can be blank for now) - How do I do that in one query?

[pocobueno1388]

The way you are doing it doesn't seem right. Why not just pull the information up that you need from a SELECT query and insert the information in its own INSERT query instead of combining the two together?

 

Then you won't have the problem with the second table, because you could just pull that information as well. Maybe I am just confused on what you are trying to do. Is there a reason it won't work the way I am suggesting?

[mwstewart]

Ahh OK I'll do it in 2 seperate queries then. I just wondered if there was a way to combine it in one.

 

Thanks 

[mwstewart]

2 seperate quesries sorted it. Thanks!

[pocobueno1388]

Yeah, I don't think there would have been a way to do it in one query in this situation. I could be wrong...but I can't see a way.

 

Glad it worked =D