2. PHPRunner General questions
  3. Insert a record into another table
This topic is locked

Insert a record into another table

 		7/27/2006 11:54:38 AM
PHPRunner General questions
B
 bbanks author

I need to insert a record into another table that is in a different Database with a different Port#and the login username and password is also different.
The record is coming from the registration form so when they register I need the username and password to go into both tables.
is this possible?
Alexey admin 7/28/2006

Bart,
sure, it's possible.

Open a new MySQL connection and execute SQL INSERT statement.

Here is the sample code for Before register event:

$conn1=mysql_connect(...);

$strSQL = "insert into table1 (username,password) values ('".$userdata["username"]."','".$userdata["username"]."')";

mysql_query($strSQL,$conn1);

mysql_close($conn1);
B
 		bbanks author 7/28/2006

Bart,

sure, it's possible.

Open a new MySQL connection and execute SQL INSERT statement.

Here is the sample code for Before register event:


Ok from what I can figure it should look like this but it's not working I don't get errors it just inserts into the first table and ignores the second.

[size=2] function BeforeRegister($userdata)

{
// Parameters:
// $userdata - Array.
// Each field on this form represented as pair
//** Insert a record into another table ****
$conn1=mysql_connect($host=[color="#ff00ff"]"[font="Courier"]);
$strSQL = [color="#ff00ff"]"insert into My_Table (username,user_password) values ('".$userdata[;
mysql_query($strSQL,$conn1);
mysql_close($conn1);
return true;
// return true [color=#0000ff]if you like to proceed with registration
// return false in other case
}


[/size]
Alexey admin 7/31/2006

Bart,
you use mysql_connect function improperly.

Here are the docs on this function:

http://www.php.net/mysql_connect
B
 		bbanks author 7/31/2006

Ok, here is what I have now.

[size=2] function BeforeRegister($userdata)

{
// Parameters:
// $userdata - Array.
// Each field on this form represented as pair
//** Insert a record into another table ****
$conn1 = mysql_connect([color="#ff00ff"]'MyServer:3306', );
$strSQL = [color="#ff00ff"]"insert into My_Table ('username','user_password') values ('".$userdata[;
mysql_query($strSQL,$conn1);
[color=#0000ff]
if (!$conn1) {
die( . mysql_error());
}
[color=#0000ff]
echo ;
mysql_close($conn1);
return true;
// return true [color=#0000ff]if you like to proceed with registration
// return false in other case
}


I get the message connected successfully and it still inserts into the first table but it is not inserting into the second database table. This Table is in a different database, where does that go? I think your gonna need to spell it out for me. I'm just not getting it.
Thanks for your help[/size]
Alexey admin 8/1/2006

Bart,
just ine more thing.

You need to select a database after connecting mysql.

So here is the code:

....

$conn1 = mysql_connect('MyServer:3306', 'MyUsername', 'MyPassword');

mysql_select_db('MyDatabase', $conn1);

$strSQL = "insert into My_Table ('username','user_password') values ('".$userdata['username']."','".$userdata['pass']."')";

mysql_query($strSQL,$conn1);

...
B
 		bbanks author 8/1/2006

Great Thank you very much that works now.
I only have one other problem with this before I can actually use it.
The first table does not have an encrypted password but the second table does. How do I send the password to the second table encrypted?
Alexey admin 8/2/2006

Bart,
use MD5 MySQL function to encrypt passwords. I.e.

$strSQL = "insert into My_Table ('username','user_password') values ('".$userdata['username']."',MD5('".$userdata['pass']."'))";
B
 		bbanks author 8/7/2006

Bart,

use MD5 MySQL function to encrypt passwords. I.e.


Ok this is really the last problem!
I have been working on it for a week and just can't get it to work.
Everything you have helped with works great, but i realized that the user_id is not an autonumber so how do I use every thing above and make the user_id the next number in line? I think it should be something like this but I don't know where it goes in the script above.

$sql = "SELECT MAX(user_id) AS total

FROM " . MyTable;
$user_id = $row['total'] + 1;
J
 		Jane 8/8/2006

Bart,
here is a code you need:

global $conn_new;

$sql = "SELECT MAX(user_id) AS total FROM My_Table";

$rs = db_query($sql,$conn_new);

$data = db_fetch_array($rs);

$user_id = $data["total"] + 1;

...

$strSQL = "insert into My_Table ('userid','username','user_password') values (".$user_id.",'".$userdata['username']."',MD5('".$userdata['pass']."'))";



where My_Table is your actual table name, userid is your actual field name.
B
 		bbanks author 8/8/2006

This is what I have from the info you sent. The table actually starts at user_id -1 and skips 0 and 1 then goes up to 36. Before I inserted your code it was adding one record with user_id 0 but wouldn't add another record after that. Now with what I have below it skips 0 and adds one record with the user_id 1 but will not add another record after that.
What am I doing wrong?

//** Insert a record into another table ****

$conn1 = mysql_connect();
mysql_select_db([color=#ff00ff]'MyDatabase', $conn1);
$sql = ;
$rs = db_query($sql,$conn1);
$user_id = $data[[color="#ff00ff"]"total"] + ;
$strSQL = [color="#ff00ff"]"insert into MyTable (user_id, username, user_email, user_password) values (".$user_id.;
mysql_query($strSQL,$conn1);
mysql_close($conn1);
return true;
// return true [color=#0000ff]if you like to proceed with registration
// return false in other case
}
B
 		bbanks author 8/8/2006

Ok I got it now.
Thank you for your help!