PHP & MySQL Communication. Requesting help.

[sfxworks]

Problem: Trying to insert static data into columns on a table in MySQL using PHP.

 

Mysql Version  5.1.57-community

PHP Version 5.2.*

 

Checked http://www.w3schools.com/php/php_mysql_insert.asp and other sources that dwarf w3schools.

Applied.

Ran into 101 problems involving remote access.

Ran into over 9000 problems involving re-installation of mysql.

Successfully communicated between remote server and localhost.

Successfully created a table on the localserver with a php script on a remote server.

Successfully added data to a column.

mysql_query("INSERT INTO test (name) VALUES ('ThisIsAName')");

Added function for encrypting password.

Added function for getting date.

Failed to add static data to a pre-created table.

 

Table information:

• id---INT(10) Primary Key, Not Null, Unsigned Datatype, Auto Increment
  
• playername---VarChar(255) Not Null
  
• password---VarChar(100)
  
• regiserdate---DateTime
  
• regiserip---Char(15)
  
• lastlogindate---DateTime
  
• lastloginip---Char(15)
  
• active---TinyInt(1)
  

 

 

 

Code for query:

mysql_query("INSERT INTO accounts (playername, password, email, registerdate, lastlogindate, lastloginip, registerip, active)
VALUES ('usernameTest',encryptPassword('passwordTest'),'email@email.com',$dateFormat,'$_SERVER[REMOTE_ADDR]',$dateFormat,'$_SERVER[REMOTE_ADDR]',0)");

 

Entire:

<?php
function encryptPassword($password) {
$salt = substr(hash('whirlpool', uniqid(rand(), true)), 0, 12);
$hash = hash('whirlpool', $salt . $password);
$saltPos = (strlen($password) >= strlen($hash) ? strlen($hash) : strlen($password));
return substr($hash, 0, $saltPos) . $salt . substr($hash, $saltPos);
}
$today=getdate(date("U"));
$dateFormat = "$today[year]-$today[mon]-$today[mday] $today[hours]:$today[minutes]:$today[seconds]";
//$encPass = encryptPassword($_POST['password']); Disabled until static data is successfully tested.
$con = mysql_connect("[s]localmachineaddress[/s]","[s]localmachineusername[/s]","[s]localmachinepassword[/s]");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("[s]schemaName[/s]", $con);
//mysql_query("INSERT INTO [s]tableName [/s](playername, password, email, registerdate, registerip)  Disabled until static data is successfully tested.
//VALUES ('$_POST[playername]',$encPass,'$_POST[email]',$dateFormat,'$_SERVER[REMOTE_ADDR]'"); Disabled until static data is successfully tested.
mysql_query("INSERT INTO accounts (playername, password, email, registerdate, lastlogindate, lastloginip, registerip, active)
VALUES ('usernameTest',encryptPassword('passwordTest'),'email@email.com',$dateFormat,'$_SERVER[REMOTE_ADDR]',$dateFormat,'$_SERVER[REMOTE_ADDR]',0)");
echo "Posted the following values... <br />  Your encrypted password = " . encryptPassword('passwordTest') . "<br /> The date sumbited = " . $dateFormat . "<br /> Your ip address = " . $_SERVER[REMOTE_ADDR];
echo "<br />Closing..";
mysql_close($con);
?>

 

Traced static data:

Posted the following values...

Your encrypted password = 52fa46eac9d07bfcf4764a35b2c4132a70332c8885bc780abcf748f991198fbc8a0b0075cc0223050c8cc4c835ff09b32518b32308aa1b73de8a4d74ec09a74a4c1cb592bd5d

The date sumbited = 2011-6-26 0:51:10

Your ip address = MyIpAddress

Closing..

 

Possibilities-

Argument types must match column types in

VALUES ('usernameTest',encryptPassword('passwordTest'),'email@email.com',$dateFormat,'$_SERVER[REMOTE_ADDR]',$dateFormat,'$_SERVER[REMOTE_ADDR]',0)");

If this is the case, method of doing so?

 

[revraz]

Can you post the actual SQL error?

And why don't you just use the MySQL NOW() for current date/time?

[sfxworks]

No SQL error is traced. Only the information echoed by the script.

 

MySQL NOW(). New method learned. Will try to apply.

[Pikachu2000]

Do not double post.

 

You're attempting to insert a php function call as a string, rather than the output of the function.

 

change

VALUES ('usernameTest',encryptPassword('passwordTest'),'email' . . . 

 

to

$pass = encryptPassword('passwordTest');
[sNIP]
. . . VALUES ('usernameTest', '$pass', 'email' . . .

[sfxworks]

My apologies for double posting.

 

Added line

$encpass = encryptPassword('passwordTest');

Modified query

mysql_query("INSERT INTO accounts (playername, password, email, registerdate, lastlogindate, lastloginip, registerip, active)
VALUES ('usernameTest','$encpass','email@email.com','$dateFormat','$_SERVER[REMOTE_ADDR]','$dateFormat','$_SERVER[REMOTE_ADDR]',0)");

 

No traced errors.

No new data in table.

Unsuccessful.

[Pikachu2000]

See what errors are returned, and separate the query string from the query execution so you can echo it as well when the query fails. You would not want to echo the actual error or query string on a live server, however; only for development.

 

$query = "INSERT INTO accounts (playername, password, email, registerdate, lastlogindate, lastloginip, registerip, active) VALUES ('usernameTest', '$encpass', 'email@email.com', '$dateFormat', '{$_SERVER['REMOTE_ADDR']}', '$dateFormat', '{$_SERVER['REMOTE_ADDR']}', 0 )";
mysql_query( $query ) or die ( "<br>Query: $query<br>Failed with error: " . mysql_error() );

[sfxworks]

Result

 

Query: INSERT INTO accounts (playername, password, email, registerdate, lastlogindate, lastloginip, registerip, active) VALUES ('usernameTest', 'aef6502bca2e6d92d57a4e8eaf383983cec31863eabea778d2ae5637782813138a4e758beebd620202b3484df49a44ae584e13f36b73aa3ea28d50ce5aa15740fd9a895e3371', 'email@email.com', '2011-6-26 1:58:51', '75.181.82.15', '2011-6-26 1:58:51', '75.181.82.15', 0 )
Failed with error: Incorrect datetime value: '75.181.82.15' for column 'lastlogindate' at row 1

 

Problem:

Vars mismatched.

 

Modified $query

$query = "INSERT INTO accounts (playername, password, email, registerdate, lastlogindate, lastloginip, registerip, active) VALUES ('usernameTest', '$encpass', 'email@email.com', '$dateFormat', '$dateFormat', '{$_SERVER['REMOTE_ADDR']}', '{$_SERVER['REMOTE_ADDR']}', 0 )";
mysql_query( $query ) or die ( "<br>Query: $query<br>Failed with error: " . mysql_error() );
mysql_close($con);

 

No Errors traced.

Static data successful added to database.

 

Thank you.

[Pikachu2000]

So it sounds as though you've sorted it then? If so, you can click the "Topic Solved" button to mark the thread as solved. If not, just say so . . .