Column count error

Question

I'm getting the following error: Column count doesn't match value count at row 1 from this query:

$movetohistory = mysql_query("INSERT INTO $storehistory SELECT * FROM $storetbl WHERE $time - datecreated >= 432000") or die ("Query failed: " . mysql_error() . " Actual query: " . $query);

I've echo'd out $storehistory, $storetbl, $time & all is fine. Both tables have identical columns / datatype. The only thing I could assume is that since $storetbl doesn't have All fields filled out, it's getting a column count error. HOWEVER, I thought specifying SELECT *ALL would over ride that.

If you could point me in the right direction, I'd appreciate that. And if you need more snippets, let me know.

Thanks.

Answer 1

Because your subquery is returning more columns than can be inserted:

INSERT INTO $storehistory SELECT * FROM $storetbl WHERE $time - datecreated >= 432000"

For clarity, you should parenthesize your subquery:

INSERT INTO $storehistory (SELECT * FROM $storetbl WHERE $time - datecreated >= 432000)"

EDIT:

I just executed this query, and it worked perfectly with two identical tables:

insert into users_copy select * from users

I would double-check that your data structures are identical.

Answer 2

This kind of code is very hard to maintain in the face of database changes.

I suggest you explicitly list the column in both the INSERT and the SELECT portions. It is possible, for instance, to change the column order of a table in production, or add or remove columns.