mrjameer Posted November 20, 2006 Share Posted November 20, 2006 hi,i have 2 mysql tablesfirst one is user table and its fields are as followsid--userfield1--userfield2--userfield31--87878978--56564545--53342323second table is user_values and it's fields areuser_id--field_id---field_value1.here i want to write a insert query such that i can fetch the id from users table and store in user_values.user_id2.the field_id value should be 1 for userfield1 and 2 for userfield2 and 3 for userfield3 in user_values table3.fetch the userfield1 value from user and store it in field_value of user_value.finally user_values table should like thisuser_id--field_id---field_value1---1---878789781---2---565645451---3---53342323.2---1---999999992---2---888888882---3---66666666i have 10 records in user table.how i can fetch and store these values.please guide me how i can do this.thankig youmrjameer. Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/ Share on other sites More sharing options...
Psycho Posted November 20, 2006 Share Posted November 20, 2006 You will need to enclose values in single quotes if they are not numeric database fields[code]<?php$sql = "SELECT * FROM user";$result = mysql_query($sql) OR die (mysql_error());while ($row = mysql_fetch_assoc($result)) { $sql = "INSERT INTO user_values (user_id, field_id, field_value) VALUES ($row[id], 1, $row[userfield1]), ($row[id], 2, $row[userfield2]), ($row[id], 3, $row[userfield3])"; mysql_query($sql) OR die (mysql_error());}?>[/code] Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127594 Share on other sites More sharing options...
mrjameer Posted November 21, 2006 Author Share Posted November 21, 2006 hi mjdamato,thank you very much for your reply.here i have one problem.the user table is present in one database and user_values table is present in another database.how can i connect to 2 different databases.thanksmrjameer. Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127894 Share on other sites More sharing options...
craygo Posted November 21, 2006 Share Posted November 21, 2006 you will not be able to connect to 2 different databases with one sql statement. you can however query one database and in a loop, or by storing values in an array, connect to another database and insert those values. just add the connection to the query.[code]<?php// connect to db1$db1= @mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());@mysql_select_db($db1name, $db1) or die(mysql_error());// connect to db2$db2= @mysql_connect($dbhost, $dbuser, $dbpass) or die(mysql_error());@mysql_select_db($db2name, $db2) or die(mysql_error());<?php$sql = "SELECT * FROM user";$result = mysql_query($sql, $db1) OR die (mysql_error());while ($row = mysql_fetch_assoc($result)) { $sql = "INSERT INTO user_values (user_id, field_id, field_value) VALUES ($row[id], 1, $row[userfield1]), ($row[id], 2, $row[userfield2]), ($row[id], 3, $row[userfield3])"; mysql_query($sql, $db2) OR die (mysql_error());}?>[/code]Ray Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127910 Share on other sites More sharing options...
mrjameer Posted November 21, 2006 Author Share Posted November 21, 2006 hi iam getting the following errorYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '), (1, 2, ), (1, 3, )' at line 2mrjameer Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127919 Share on other sites More sharing options...
mrjameer Posted November 21, 2006 Author Share Posted November 21, 2006 hihere is my code.but it is inserting one row from users table only and displays the error message You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '), (4, 3, )' at line 3.i have more than 1 record.<?php// connect to db1$db1= @mysql_connect("localhost", "","") or die(mysql_error());@mysql_select_db("suuu", $db1) or die(mysql_error());// connect to db2$db2= @mysql_connect("localhost","root","") or die(mysql_error());@mysql_select_db("supportcenter1", $db2) or die(mysql_error());$sql = "SELECT id,userfield1,userfield2,userfield3 FROM users";$result = mysql_query($sql, $db1) OR die (mysql_error());//$num_rows = mysql_num_rows($result);while ($row = mysql_fetch_assoc($result)) { $id=$row['id']; $userfield1=$row['userfield1']; $userfield2=$row['userfield2']; $userfield3=$row['userfield3']; $sql = "INSERT INTO user_values (user_id, field_id, field_value) VALUES ($id, 1, $userfield1), ($id, 2, $userfield2), ($id, 3, $userfield3)"; mysql_query($sql, $db2) OR die (mysql_error()); } echo "inserted";?> Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127936 Share on other sites More sharing options...
Psycho Posted November 21, 2006 Share Posted November 21, 2006 [quote author=mrjameer link=topic=115682.msg471382#msg471382 date=1164119835]iam getting the following errorYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '), (1, 2, ), (1, 3, )' at line 2[/quote]There is no 3rd value in those lists. In othter word, you are not getting a vlaue for the userfields. You have [b]1,2,[nothing][/b]. It is looking for that 3rd value. If that it correct - that they have no value - then you could add a little error handling like this which will give fields a value of 0 if they have no value. Or change the 0 to NULL if that's what you want.[code]while ($row = mysql_fetch_assoc($result)) { $field1 = ($row[userfield1])?$row[userfield1]:0; $field2 = ($row[userfield1])?$row[userfield2]:0; $field3 = ($row[userfield1])?$row[userfield3]:0; $sql = "INSERT INTO user_values (user_id, field_id, field_value) VALUES ($row[id], 1, $field1), ($row[id], 2, $field2), ($row[id], 3, $field3))"; mysql_query($sql, $db2) OR die (mysql_error());}[/code] Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127965 Share on other sites More sharing options...
mrjameer Posted November 21, 2006 Author Share Posted November 21, 2006 hi mjdamato,you are right.there are some blank values in the userfield1 and userfield3 for id=4 and many more.i will check your code and i will reply to you.thanksmrjameer Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127967 Share on other sites More sharing options...
mrjameer Posted November 21, 2006 Author Share Posted November 21, 2006 hi mjdamato,it is working good.but if the field contains some text it is not inserting.it is inserting up to those rows that contains numbers .it is showing Unknown column 'aaaaa' in 'field list'.the datatype of field userfield3 is 'text'.it is not allowing to insert text which is present in users table.thanksmrjameer Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127972 Share on other sites More sharing options...
mrjameer Posted November 21, 2006 Author Share Posted November 21, 2006 hi,now it's working.thank you very much mjdamato,craygo who replied me at correct time.thanksmrjameer Quote Link to comment https://forums.phpfreaks.com/topic/27903-mysql-insert-in-php/#findComment-127987 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.