php mysql pdo data inserting problem

General PHP Help
#1

Hi i have written a php signup script with pdo, but it does not inserting any data to database. My script

[php]1.$mysql_table = ‘users’;2.$success_page = ‘signupsuccess.php’;3.$error_message = “”;4.if ($_SERVER[‘REQUEST_METHOD’] == ‘POST’ && $_POST[‘form_name’] == ‘signupform’)5.{6. $newusername = $_POST[‘username’];7. $newemail = $_POST[‘email’];8. $newpassword = $_POST[‘password’];9. $confirmpassword = $POST[‘confirmpassword’];10. $newfullname = $POST[‘fullname’];11. $newrole = $POST[‘role’];12. $code = ‘NA’;13. $active = ‘1’;14.if ($newpassword != $confirmpassword)15. {16. $error_message = ‘Password and Confirm Password are not the same!’;17. }18. else19. if (!preg_match("/^[A-Za-z0-9!@$]{1,50}$/", $newusername))20. {21. $error_message = ‘Username is not valid, please check and try again!’;22. }23. else24. if (!preg_match("/^[A-Za-z0-9!@$]{1,50}$/", $newpassword))25. {26. $error_message = ‘Password is not valid, please check and try again!’;27. }28. else29. if (!preg_match("/^[A-Za-z0-9!@$.’ &]{1,50}$/", $newfullname))30. {31. $error_message = ‘Fullname is not valid, please check and try again!’;32. }33. else34. if (!preg_match("/^.+@.+…+$/", $newemail))35. {36. $error_message = ‘Email is not a valid email address. Please check and try again.’;37. }38. else39. if (isset($_POST[‘captcha’],$_SESSION[‘random_txt’]) && md5($_POST[‘captcha’]) == $_SESSION[‘random_txt’])40. {41. unset($_POST[‘captcha’],$_SESSION[‘random_txt’]);42. }43. else44. {45. $error_message = ‘The entered code was wrong.’;46. }47. if (empty($error_message))48. {49. try50. {51. $pdo = new PDO(‘mysql:host=localhost;dbname=blog’, ‘Avik’, ‘’);52. }53. catch (PDOException $e)54. {55. $output = ‘Unable to connect to the database server.’;56. echo $output;57. exit();58. }59. try60. {61. $sql = “SELECT username FROM “.$mysql_table.” WHERE username = :username”;62. $statement = $pdo->prepare($sql);63. $statement->bindValue(’:username’, $newusername);64. $statement->execute();65. $result = $statement->fetchAll(PDO::FETCH_ASSOC);66. if (count($result) > 0)67. {68. $error_message = ‘Username already used. Please select another username.’;69. }70. }71. catch (PDOException $e)72. {73. $output = ‘Unable to send query’;74. echo $output;75. exit();76. }77. }78. var_dump($error_message);79. if (empty($error_message))80. {81. try82. {83. $sql = “INSERT INTO “.$mysql_table.” VALUES (username = :username, password = :password, fullname = :fullname, email = :email, role = :role, active = :active, code = :code)”;84. $statement = $pdo->prepare($sql);85. $statement->bindValue(’:username’, $newusername);86. $statement->bindValue(’:password’, md5($newpassword));87. $statement->bindValue(’:fullname’, $newfullname);88. $statement->bindValue(’:email’, $newemail);89. $statement->bindValue(’:role’, $newrole);90. $statement->bindValue(’:active’, $active);91. $statement->bindValue(’:code’, $code);92. $statement->execute();93. }94. catch (PDOException $e)95. {96. $output = ‘Unable to connect to the database server.’;97. echo $output;98. exit();99. }100.101. }[/php]

For more info.please go here, i asked daniweb. They were unable to help.

#2

Fixed the code so it’s readable

[php]<?php
$mysql_table = ‘users’;
$success_page = ‘signupsuccess.php’;
$error_message = “”;

if ($_SERVER[‘REQUEST_METHOD’] == ‘POST’ && $_POST[‘form_name’] == ‘signupform’) {
$newusername = $_POST[‘username’];
$newemail = $_POST[‘email’];
$newpassword = $_POST[‘password’];
$confirmpassword = $POST[‘confirmpassword’];
$newfullname = $POST[‘fullname’];
$newrole = $POST[‘role’];
$code = ‘NA’;
$active = ‘1’;
if ($newpassword != $confirmpassword) {
$error_message = ‘Password and Confirm Password are not the same!’;
}
elseif (!preg_match("/^[A-Za-z0-9!@$]{1,50}$/", $newusername)) {
$error_message = ‘Username is not valid, please check and try again!’;
}
elseif (!preg_match("/^[A-Za-z0-9!@$]{1,50}$/", $newpassword)) {
$error_message = ‘Password is not valid, please check and try again!’;
}
elseif (!preg_match("/^[A-Za-z0-9!@$.’ &]{1,50}$/", $newfullname)) {
$error_message = ‘Fullname is not valid, please check and try again!’;
}
elseif (!preg_match("/^.+@.+…+$/", $newemail)) {
$error_message = ‘Email is not a valid email address. Please check and try again.’;
}
elseif (isset($_POST[‘captcha’], $_SESSION[‘random_txt’]) && md5($_POST[‘captcha’]) == $_SESSION[‘random_txt’]) {
unset($_POST[‘captcha’], $_SESSION[‘random_txt’]);
}
else {
$error_message = ‘The entered code was wrong.’;
}

if (empty($error_message)) {
    try {
        $pdo = new PDO('mysql:host=localhost;dbname=blog', 'Avik', '');
    }

    catch(PDOException $e) {
        $output = 'Unable to connect to the database server.';
        echo $output;
        exit();
    }

    try {
        $sql = "SELECT username FROM " . $mysql_table . " WHERE username = :username";
        $statement = $pdo->prepare($sql);
        $statement->bindValue(':username', $newusername);
        $statement->execute();
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        if (count($result) > 0) {
            $error_message = 'Username already used. Please select another username.';
        }
    }

    catch(PDOException $e) {
        $output = 'Unable to send query';
        echo $output;
        exit();
    }
}

var_dump($error_message);
if (empty($error_message)) {
    try {
        $sql = "INSERT INTO " . $mysql_table . " VALUES (username = :username, password = :password, fullname = :fullname, email = :email, role = :role, active = :active, code = :code)";
        $statement = $pdo->prepare($sql);
        $statement->bindValue(':username', $newusername);
        $statement->bindValue(':password', md5($newpassword));
        $statement->bindValue(':fullname', $newfullname);
        $statement->bindValue(':email', $newemail);
        $statement->bindValue(':role', $newrole);
        $statement->bindValue(':active', $active);
        $statement->bindValue(':code', $code);
        $statement->execute();
    }

    catch(PDOException $e) {
        $output = 'Unable to connect to the database server.';
        echo $output;
        exit();
    }
}

}[/php]

#3

I must commend you for using parameterized queries, thank you!

I would suggest trying your queries directly when having problems, either in console, in a db-app, phpmyadmin, something. Your insert statement is invalid, you are mixing insert and update markup.

Insert statements look like this

INSERT INTO table_name (column, column2, column3, column4) VALUES (value, value2, value3, value4)

[hr]

Please do not use MD5/Sha1 for passwords, it is not safe.
You should do this: http://www.phphelp.com/forum/mysql-database/password-issues/msg75827/#msg75827

And one thing, you should reconsider your try/catches. You could use one try/catch for the entire file, you don’t need to have try/catches everywhere :slight_smile:

Sponsor our Newsletter | Privacy Policy | Terms of Service