Page 1 of 1

SELECT * EXCEPT one field and INSERT into table?

Posted: Wed Nov 22, 2006 8:55 am
by gmrobert
webID is an autoincrement field/column in the table $Table. I would like to add rows from a different table but have the webID autoincrement when I add the row.

I tried removing the field/column 'webID' from $SyncTable but I get an mismatch error when I try and run and INSERT query.

I tried leaving the webID field NULL in $SyncTable but then webID in $Table is '1' for all rows that I INSERT.

Here is my code:

Code: Select all

INSERT INTO {$Table} SELECT * FROM {$SyncTable} WHERE {$SyncTable}.UserID = {$UserID}
Does anyone have any ideas how I could insert a row from $SyncTable but still have the column webID autoincrement?

Thanks

Greg

using the field names

Posted: Wed Nov 22, 2006 10:19 am
by Jaxolotl
didn't you try to use

Code: Select all

INSERT INTO {$Table} SELECT field_x,field_x,etc... FROM {$SyncTable} WHERE {$SyncTable}.UserID = {$UserID} 
where field_x are the fields of your table excluding the one you don't want to insert,
instead of

Code: Select all

INSERT INTO {$Table} SELECT * FROM {$SyncTable} WHERE {$SyncTable}.UserID = {$UserID} 
??

Posted: Wed Nov 22, 2006 11:05 am
by gmrobert
I don't know the names of all the fields. I need to use a wildcard (*) or some other indirect method of refering to them.

Greg

get the field names

Posted: Wed Nov 22, 2006 11:21 am
by Jaxolotl
you can allways get the field names of your table by doing this

Code: Select all

SHOW COLUMNS FROM {$Table} ;
more info @ http://dev.mysql.com/doc/refman/4.1/en/ ... lumns.html

Posted: Wed Nov 22, 2006 11:42 am
by gmrobert
Can I get the SHOW function to output the field names as a comma separated vaules?

I can't find any examples of how to modify the output from this statement.

Thanks

Greg

show it

Posted: Wed Nov 22, 2006 12:04 pm
by Jaxolotl
Without knowing on which platform you're working I suggest this

First make your query

Code: Select all

$query = "SHOW COLUMNS FROM my_table";
once you get a resource fetch it into an array or into rows

Code: Select all


/* $field_results is my resource and $fields['0'] is the key of the field name */
	while($fields = mysql_fetch_row($field_results)){
		$field_names[] = "`".$fields['0']."`";
	}
	$field_names = implode(",",$field_names);
	echo  $field_names;
// $field_names is a string containing the comma separated field names

Posted: Wed Nov 22, 2006 3:06 pm
by feyd
SQL != PHP.

Posted: Wed Nov 22, 2006 5:11 pm
by jamiel
gmroberts ... you need to specify the column names on the insert to skip the auto-increment field. eg.

Code: Select all

INSERT INTO {$Table} (`Column2`, `Column3`) (
SELECT
    *
FROM
    {$SyncTable}
WHERE
    {$SyncTable}.UserId = {$UserID}
)
.. and make sure your column count matches * else you will need to specify column names in your select aswell.

Sorry

Posted: Thu Nov 23, 2006 2:15 am
by Jaxolotl
Sorry feyd, my answer was in PHP because I found the post on the PHP forum
I'll ask first the next time