Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
gmrobert
Forum Newbie
Posts: 22 Joined: Wed Oct 04, 2006 9:07 am
Post
by gmrobert » Wed Nov 22, 2006 8:55 am
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
Jaxolotl
Forum Contributor
Posts: 137 Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy
Post
by Jaxolotl » Wed Nov 22, 2006 10:19 am
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 » Wed Nov 22, 2006 11:05 am
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
gmrobert
Forum Newbie
Posts: 22 Joined: Wed Oct 04, 2006 9:07 am
Post
by gmrobert » Wed Nov 22, 2006 11:42 am
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
Jaxolotl
Forum Contributor
Posts: 137 Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy
Post
by Jaxolotl » Wed Nov 22, 2006 12:04 pm
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
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Wed Nov 22, 2006 3:06 pm
SQL != PHP.
jamiel
Forum Contributor
Posts: 276 Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom
Post
by jamiel » Wed Nov 22, 2006 5:11 pm
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.
Jaxolotl
Forum Contributor
Posts: 137 Joined: Mon Nov 13, 2006 4:19 am
Location: Argentina and Italy
Post
by Jaxolotl » Thu Nov 23, 2006 2:15 am
Sorry feyd , my answer was in PHP because I found the post on the PHP forum
I'll ask first the next time