Can't Select a table and insert something into it at the same time (Mysql)

Question

I have this registration form, when the user is finished with the form he will finally submit it, but I would like to check if the username and email is already there or not, easy right ? It's been 2 days trying to figure this out but no luck. Excuse me I'm not using the latest version of MySQL since this is the only version I learned at class. I will learn the improved one.

I've done a lot of research on Google, I found that this is some king of LOCK, that when we're inserting the table get locked ..

if(isset($_POST['button'])){


    $query_global = mysql_query("SELECT Username FROM users WHERE Username = '".$_POST['username']."' ") or die(mysql_error());

        $row = mysql_num_rows($query_global);

        if($row == 1){
            $error_username = "The username is already registered, please choose another one <br>";
        }

        $query_email = mysql_query("SELECT Email FROM users WHERE Email = '".$_POST['email']."' ") or die(mysql_error());

        $row_email = mysql_num_rows($query_email);

        if($row_email == 1){
            $error_email = "This email : '".$_POST['email']."' is already registered ";
        }

    if(isset($_POST['Username'])) { $Username = $_POST['Username']; }
    if(isset($_POST['email'])) { $email = $_POST['email']; }



    $nom = $_POST['nom'];
    $sexe = $_POST['sexe'];
    $email = $_POST['email'];
    $tel = $_POST['tel'];
    $adresse = $_POST['adresse'];
    $ville = $_POST['ville'];
    $pseudo = $_POST['pseudo'];
    $mdp = $_POST['mdp'];
    $date = $_POST['date'];
    $profession = $_POST['profession'];


    // location where initial upload will be moved to
    $target = "images/" .$_FILES['uploaded']['name'] ;

    // find thevtype of image
    switch ($_FILES["uploaded"]["type"]) {
    case $_FILES["uploaded"]["type"] == "image/gif":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/jpeg":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/pjpeg":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/png":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/x-png":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;

    default:
        $error[] = 'Seulement les JPG, PNG ou GIF sont acceptés!.';
    }

    $error="";

    if (!$error) {

    $query = "INSERT INTO Users Values ('', '".$nom."', '".$sexe."', '".$email."', ".$tel.", '".$adresse."', '".$ville."',  '".$pseudo."', '".$mdp."', curdate(), '$target', '".$date."', '".$profession."')";
    $add_user = mysql_query($query) or die(mysql_error());

    header('Location: Login/index.php');

        }

    }


    //display any errors
    if (!empty($error))
    {
            $i = 0;
            echo "<p><span class='error'>";
            while ($i < count($error)){
            echo $error[$i].'<br />';
            $i ++;}
            echo "</span></p>";
    }

Even if all this works out is there any way to stop the form from submitting when the inputs are wrong, I mean I've tried this in another file and it worked but even if the inputs are wrong the form gets submitted.

Can't wait to hear your answers.

You should section your code off into [functions](http://php.net/manual/en/functions.user-defined.php) - it's mostly your indentation/structure which complicates your task. Then read up on database escaping, better yet [avoiding mysql](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). — mario, Nov 26 '14 at 01:50
You were right I actually sectioned the insert into query into a function and everything is working fine now ! thank you !! — Anas Abouzaradi, Nov 26 '14 at 11:06

score 0 · Answer 1 · answered Nov 26 '14 at 01:49

0

You do not have anything that stops the script from running

if($row_email == 1){
        $error_email = "This email : '".$_POST['email']."' is already registered ";
    }
else{

//all the rest of your code here

}

You are setting $error to nothing right before checking it's value not sure what's the purpose:

$error="";

if (!$error) {

answered Nov 26 '14 at 01:49

Tom

432
2
9

Thank you for your answer, you're right about the error problem, but the problem really isn't about the error, I sectioned the insert into query into a function and it seems to work fine now. Thanks again. – Anas Abouzaradi Nov 26 '14 at 11:11

score 0 · Answer 2 · answered Nov 26 '14 at 02:00

I think you're confusing yourself with all your error variables.

Instead of storing your errors in different variables, use a single array to hold all your errors. If the form is submitted and the array of errors is empty, then it would mean that everything worked out. Here's how it might look:

$errors = array(); // this will contain all your errors

if (isset($_POST['button'])) {

    // store all your submitted values
    $nom = $_POST['nom'];
    $sexe = $_POST['sexe'];
    $Username = $_POST['Username'];
    $email = $_POST['email'];
    $tel = $_POST['tel'];
    $adresse = $_POST['adresse'];
    $ville = $_POST['ville'];
    $pseudo = $_POST['pseudo'];
    $mdp = $_POST['mdp'];
    $date = $_POST['date'];
    $profession = $_POST['profession'];

    // check username
    $query_global = mysql_query("SELECT Username FROM users WHERE Username = '". $Username ."'") or die(mysql_error());

    $row = mysql_num_rows($query_global);

    if($row == 1){
        $errors[] = "The username is already registered, please choose another one <br>";
    }

    // check email
    $query_email = mysql_query("SELECT Email FROM users WHERE Email = '". $email ."'") or die(mysql_error());

    $row_email = mysql_num_rows($query_email);

    if($row_email == 1){
        $errors[] = "This email : '". $email ."' is already registered ";
    }

    // location where initial upload will be moved to
    $target = "images/" .$_FILES['uploaded']['name'] ;

    // find thevtype of image
    switch ($_FILES["uploaded"]["type"]) {
        case $_FILES["uploaded"]["type"] == "image/gif":
            move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
            break;
        case $_FILES["uploaded"]["type"] == "image/jpeg":
            move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
            break;
        case $_FILES["uploaded"]["type"] == "image/pjpeg":
            move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
            break;
        case $_FILES["uploaded"]["type"] == "image/png":
            move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
            break;
        case $_FILES["uploaded"]["type"] == "image/x-png":
            move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
            break;
        default:
            $errors[] = 'Seulement les JPG, PNG ou GIF sont acceptés!.';
    }

    // create user if no errors
    if (empty($errors)) {
        $query = "INSERT INTO Users Values ('', '".$nom."', '".$sexe."', '".$email."', ".$tel.", '".$adresse."', '".$ville."',  '".$pseudo."', '".$mdp."', curdate(), '$target', '".$date."', '".$profession."')";
        $add_user = mysql_query($query) or die(mysql_error());

        header('Location: Login/index.php');

    // otherwise display errors
    } else {
        $i = 0;
        echo "<p><span class='error'>";
        while ($i < count($errors)){
            echo $errors[$i] . '<br />';
            $i++;
        }
        echo "</span></p>";
    }
}

Thank you for your answer, I used this improved code into my site. You're right about the error problem I am confusing my self, but the problem really isn't about the errors, I sectioned the insert into query into a function and it seems to work fine now. Thanks again ! — Anas Abouzaradi, Nov 26 '14 at 11:12

score 0 · Answer 3 · answered Nov 26 '14 at 05:59

You are defining error in two types one is array and another one is string I think there is no need to define errors string at this time.

Array

$errors[]

String

 $error="";

Also store the already register email and user values in error array. Try below.

if(isset($_POST['button'])){


    $query_global = mysql_query("SELECT Username FROM users WHERE Username = '".$_POST['username']."' ") or die(mysql_error());

        $row = mysql_num_rows($query_global);

        if($row == 1){
            $error_username = "The username is already registered, please choose another one <br>";
        $error[] = "The username is already registered, please choose another one <br>";
        }

        $query_email = mysql_query("SELECT Email FROM users WHERE Email = '".$_POST['email']."' ") or die(mysql_error());

        $row_email = mysql_num_rows($query_email);

        if($row_email == 1){
            $error_email = "This email : '".$_POST['email']."' is already registered ";
        $error[] = "This email : '".$_POST['email']."' is already registered ";
        }

    if(isset($_POST['Username'])) { $Username = $_POST['Username']; }
    if(isset($_POST['email'])) { $email = $_POST['email']; }



    $nom = $_POST['nom'];
    $sexe = $_POST['sexe'];
    $email = $_POST['email'];
    $tel = $_POST['tel'];
    $adresse = $_POST['adresse'];
    $ville = $_POST['ville'];
    $pseudo = $_POST['pseudo'];
    $mdp = $_POST['mdp'];
    $date = $_POST['date'];
    $profession = $_POST['profession'];


    // location where initial upload will be moved to
    $target = "images/" .$_FILES['uploaded']['name'] ;

    // find thevtype of image
    switch ($_FILES["uploaded"]["type"]) {
    case $_FILES["uploaded"]["type"] == "image/gif":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/jpeg":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/pjpeg":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/png":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;
    case $_FILES["uploaded"]["type"] == "image/x-png":
        move_uploaded_file($_FILES["uploaded"]["tmp_name"],$target);
        break;

    default:
        $error[] = 'Seulement les JPG, PNG ou GIF sont acceptés!.';
    }



    if (empty($error)) {

    $query = "INSERT INTO Users Values ('', '".$nom."', '".$sexe."', '".$email."', ".$tel.", '".$adresse."', '".$ville."',  '".$pseudo."', '".$mdp."', curdate(), '$target', '".$date."', '".$profession."')";
    $add_user = mysql_query($query) or die(mysql_error());

    header('Location: Login/index.php');

        }

    }


    //display any errors
    if (!empty($error))
    {
            $i = 0;
            echo "<p><span class='error'>";
            while ($i < count($error)){
            echo $error[$i].'<br />';
            $i ++;}
            echo "</span></p>";
    }

Thank you for your answer, I used a section of this code into my site. You're right about the error problem I am confusing my self, but the problem really isn't about the errors, I sectioned the insert into query into a function and it seems to work fine now. Thanks again ! — Anas Abouzaradi, Nov 26 '14 at 11:13

Can't Select a table and insert something into it at the same time (Mysql)

3 Answers3