SELECT * EXCEPT one field and INSERT into table?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gmrobert
Forum Newbie
Posts: 22
Joined: Wed Oct 04, 2006 9:07 am

SELECT * EXCEPT one field and INSERT into table?

Post 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
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

using the field names

Post 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} 
??
gmrobert
Forum Newbie
Posts: 22
Joined: Wed Oct 04, 2006 9:07 am

Post 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
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

get the field names

Post 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
gmrobert
Forum Newbie
Posts: 22
Joined: Wed Oct 04, 2006 9:07 am

Post 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
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

show it

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

SQL != PHP.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
User avatar
Jaxolotl
Forum Contributor
Posts: 137
Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy

Sorry

Post 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
Post Reply