mysql insert in php

[mrjameer]

hi,

i have 2 mysql tables

first one is user table and its fields are as follows
id--userfield1--userfield2--userfield3
1--87878978--56564545--53342323

second table is user_values and it's fields are
user_id--field_id---field_value

1.here i want to write a insert query such that i can fetch the id from users table and store in user_values.user_id

2.the field_id value should be 1 for userfield1 and 2 for userfield2 and 3 for userfield3 in user_values table

3.fetch the userfield1 value from user and store it in field_value of user_value.finally user_values table should like this

user_id--field_id---field_value
1---1---87878978
1---2---56564545
1---3---53342323.
2---1---99999999
2---2---88888888
2---3---66666666
i have 10 records in user table.how i can fetch and store these values.please guide me how i can do this.


thankig you
mrjameer.

[Psycho]

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]

[mrjameer]

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.


thanks
mrjameer.

[craygo]

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

[mrjameer]

hi

iam getting the following error

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 '), (1, 2, ), (1, 3, )' at line 2

mrjameer

[mrjameer]

hi

here 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";

?>

[Psycho]

[quote author=mrjameer link=topic=115682.msg471382#msg471382 date=1164119835]iam getting the following error

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 '), (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]

[mrjameer]

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.

thanks
mrjameer

[mrjameer]

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.

thanks
mrjameer

[mrjameer]

hi,


now it's working.thank you very much mjdamato,craygo who replied  me at correct time.

thanks
mrjameer