Inserting hash into mysql table.

[Anon]

Hello,

I am transferring a website to a new server using a different script and I'm trying to move my users from the old database to the new one:

---

$link = mysql_connect("oldserver", "oldname", "oldpass")
or die("Could not connect");
mysql_select_db("olddb", $link)
or die("Could not select database");
$user_res = mysql_query("SELECT DISTINCT
userid, username, useraddress, password FROM oldtable ORDER BY userid, username, useraddress, password")
or die("Invalid query");
while ($row = mysql_fetch_assoc($user_res)) {
$newlink = mysql_connect("newserver", "newid", "newpass")

or die("Could not connect");
mysql_select_db("newdb", $newlink);
$newresult = mysql_query("INSERT INTO newtable (uname, email, pass) VALUES ('{$row["username"]}', '{$row["useraddress"]}', '{$row["userpassword"]}')", $newlink)
or die("Invalid query");

 }

mysql_free_result($user_res);

mysql_close($link);

mysql_close($newlink);

---

I keep getting an "invalid query" error when executing the 2nd mysql_query on $newlink. It parses ok. Any help would be greatly appreciated!

Susan

[Anon]

Susan,

Your " are causing your problems in $newresult.

I'd break it down to:

$newquery = "INSERT INTO newtable (uname, email, pass) VALUES ('" . $row["username"] . "', '" . $row["useraddress"] . "', '" . $row["userpassword"] . "', $newlink )";

$newresult = mysql_query($newquery) or die ("Invalid query");

HTH

[Anon]

Hi Rincewind,

Thanks for the suggestion, although that produced the same error. Then I tried this variation:

$newname = $row["username"];
$newmail = $row["useraddress"];
$newpass = $row["userpassword"];

$newquery = "INSERT INTO new (uname, email, pass) VALUES ('" . $newname . "', '" . $newmail . "', '" . $newpass . "'), $newlink ";

The statement below works (so I know I am able to execute a query on that table):

$newquery = "INSERT INTO new (uname, email, pass) VALUES ('one', 'two', 'three')";

I'm kind of wondering if the problem is because mysql wants this type of syntax:

1.7.4.2 SELECT INTO TABLE

MySQL Server doesn't yet support the Oracle SQL extension: SELECT ... INTO TABLE .... MySQL Server supports instead the ANSI SQL syntax INSERT INTO ... SELECT ..., which is basically the same thing. See section 6.4.3.1 INSERT ... SELECT Syntax.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID
FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;

Alternatively, you can use SELECT INTO OUTFILE... or CREATE TABLE ... SELECT.

Are you supposed to not use the hash and just INSERT INTO newtable (fields) SELECT FROM oldtable.field ??

Maybe I would be better off writing the stuff to a file and then closing the first table and opening the 2nd one to INSERT INTO??

Thanks for any help!

[Anon]

The original query I wrote:

$newresult = mysql_query("INSERT INTO newtable (uname, email, pass) VALUES ('{$row["username"]}', '{$row["useraddress"]}', '{$row["userpassword"]}')", $newlink)
or die("Invalid query");

actually worked when I retyped it! I had transposed two letters in the table name :-) (red faced). At least I will never forget how to insert a hash into a table.

Cheers,

Susan