PHP Sign up script not working! mysql insertion failed

Question

Cannot find the bug here, think so this whole script is fagged up, still can not see any signs of success as such.
The problem is when the form is hit with the specified details i.e. username , password and an e-mail address are not inserted into mysql database and I am unable to verify through PHP "How to check if details are present in database?".

   <?php
    ob_start();
    $host="localhost"; // Host name 
    $username="root"; // Mysql username 
    $password=""; // Mysql password 
    $db_name="cosmos"; // Database name 
    $tbl_name="members"; // Table name

    // Connect to server and select databse.
    mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");




    // Define $myusername and $mypassword and $mail
    $user=$_POST['myusername'];
    $pass=$_POST['mypassword'];
    $user = stripslashes($user);
    $pass = stripslashes($pass);
    $user = mysql_real_escape_string($user);
    $pass = mysql_real_escape_string($pass);
    $pass = md5($pass);
    $mail=$_POST['email'];

    $sql = "INSERT INTO `cosmos`.`members` (`id`, `username`, `password`, `email id`) VALUES (NULL, \'$user\', \'$pass\', \'$mail\'), (NULL, \'\', \'\', \'\');";
    $result=mysql_query($sql);
    echo "Pass!"; //dont know how to verify :)
    ob_end_flush();
    ?>

can you please correct this or give a new one , thanks in advance!

Answer 1

To work out whether something has entered or not use mysql_insert_id().

Answer 2

You shouldn't escape the single quotes:

$sql = "INSERT INTO `cosmos`.`members` (`username`, `password`, `email id`) VALUES ('$user', '$pass', '$mail');";

Also don't forget to do this:

$mail = mysql_real_escape_string($_POST['email']);

Answer 3

Don't escape the data quotes and remove the ";" at the end:

$sql = "INSERT INTO `cosmos`.`members` (`id`, `username`, `password`, `email id`) VALUES (NULL, '$user', '$pass', '$mail')";

To test if the query worked add:

if(mysql_error()) {
die('There was a problem inserting the data into the database');
}

Answer 4

is ** email id ** really your database field?

please do $mail = mysql_real_escape_string($mail);

to check whether the insert was successfull use mysql_insert_id() if you are using an auto increment key and your constraints are set up correctly.

i recommend checking in advance doing a select statement and to allow only certain characters in user names.

you can remove this part, it may only fuck up your login system. you are escpaing the strings anyway with mysql_real_escape_string:

$user = stripslashes($user);
$pass = stripslashes($pass);

Answer 5

To verify the insertion, do:

$result=mysql_query($sql) or die("INSERT failed: " . mysql_error());

If it worked, the script won't kill itself. If you need to do other checking, such as possibly making sure there wasn't a constraint violation (duplicate username?), then do

$result = mysql_query($sql);
if (mysql_error() === 1022) {
   $errmsg = 'Sorry, username already exists'; // or whatever you want the message to be
}

Full error code listing is document here.