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