Check Database result based on condition

Question

My problem is as follows. I want javascript to check if a value exists in the database. I have a javascript function which checks if an email adress exists in the database. That one is working fine, so i made copy of it and changed it to match a different input field, which should check if a value (woonplaats) exists. Right now, it always shows the message 'plaatsnaam bestaat niet' even though it does exist. The HTML, PHP code with the SQL which checks the database and the javascript are all in the same PHP file. I know the PHP check echo's but i only want the javascript to perform a check.

Underneath my code:

HTML:

<form action="" onSubmit="return !!checkMailStatus() & checkCityStatus();" method="post" id="registration">
<input type="email" name="email" id="email" placeholder="E-mailadres" required/>
<input type="text" name="keyword" id="keyword" placeholder="Woonplaats" value="">
<button type="submit" name="register" class="btn-red">Registreren!</button>
  </form>

PHP:

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

    $email = $_POST['email'];
    $keyword = $_POST['keyword'];

    $check_d = $db->query("SELECT id FROM users WHERE email='".$email."'");
    $check_d = $check_d->num_rows;
    if($check_d == 1) {
        echo 'E-mailadres bestaat al';
    }

    $check_pnl = ("SELECT woonplaats 
                   FROM `locatie` 
                   WHERE    woonplaats='".$keyword."'");
    $check_pnl = $check_pnl->num_rows;
    if($check_pnl == 0) {
        echo 'Plaatsnaam Bestaat Niet';
    }

Javascript:

<script>
function checkMailStatus(){
    //alert("came");
var email=$("#email").val();
$.ajax({
    type:'post',
        url:'',
        data:{email: email},
        success:function(msg){
        alert('Gebruik een ander e-mail adres'); 
        return false; //prevent submit from submitting     
        }

 });
}
</script>
<script>
function checkCityStatus(){
    //alert("came");
var keyword=$("#keyword").val();
$.ajax({
    type:'post',
        url:'',
        data:{keyword: keyword},
        success:function(msg){
        alert('Plaatsnaam bestaat niet');
        return false; //prevent submit from submitting     
        }

 });
}
</script>

BASED ON THE AWNSERS OF Pioz AND RAJDEEP PAUL I CHANGED THE FOLLOWING:

in index.php I changed the javascript code to:

<script>

        var regForm     = $("#registration");

        regForm.submit(function(evt){
            // PREVENT FORM FROM DEFAULT BEHAVIOUR: SUBMITTING...
            evt.preventDefault();

            // IF BOTH CONDITIONS ARE SATISFIED... SUBMIT THE FORM MANUALLY...
            if(checkMailStatus() && checkCityStatus()){
                // SHIP THE FORM
                $(this).submit();
            }

        });


        function checkMailStatus(){
            var email       = $("#email").val();
            var returnVal   = 0;
            $.ajax({
                type:'post',
                    url:'check.php',
                    data:{email: email, checkMail: true},

                    success:function(msg){
                        if(msg == "0"){
                            alert('Gebruik een ander e-mail adres'); 
                        }else{
                            returnVal = 1;
                        }
                    }

             });
             return returnVal;
        }

        function checkCityStatus(){
            var keyword     = $("#keyword").val();
            var returnVal   = 0;
            $.ajax({
                type:'post',
                    url:'check.php',
                    data:{keyword: keyword, checkCity: true},

                    success:function(msg){
                        if(msg == "0"){
                            alert('Plaatsnaam bestaat niet');  
                        }else{
                            returnVal = 1;
                        }  
                    }
            });
             return returnVal;
        }
    </script>

I ALSO CREATED CHECK.PHP:

if($_POST['checkMail'] == "true"){ 
        $check_d    = $db->query("SELECT id FROM users WHERE email='".$email."'");
        $check_d    = $check_d->num_rows;
        if($check_d == 0) {
            echo "0";
        }else{
            // ECHO A FLAG TO SHOW THAT EVERYTHING WORKS FINE: ZERO ISSUE WAS FOUND
            echo "1";
        }
        }

        if($_POST['checkCity'] == "true"){
        $check_pnl  = ("SELECT woonplaats 
                       FROM `locatie` 
                       WHERE    woonplaats='".$keyword."'");
        $check_pnl  = $check_pnl->num_rows;
        if($check_pnl == 0) {
            echo "1";
        }else{
            // ECHO A FLAG TO SHOW THAT EVERYTHING WORKS FINE: ZERO ISSUE WAS FOUND
            echo "0";
        }
        }

Answer 1

Except you have the 2 functions in 2 separate files, there is no real necessity in creating more than one script block for both functions. If you will, here is another route for ya:

HTML

    <form action="" method="post" id="registration">
        <input type="email" name="email"    id="email"      placeholder="E-mailadres" required/>
        <input type="text"  name="keyword"  id="keyword"    placeholder="Woonplaats"    value="">
        <button type="submit" name="register" class="btn-red">Registreren!</button>
    </form>

JAVASCRIPT

    <script>

    // MAKE SURE YOU ADD THE FOLLOWING TO YOUR JAVASCRIPT AS YOU ARE USING JQUERY, NOT RAW JS.

    (function ($) {             //<== THIS LINE IS NEW
        $(document).ready(function(e) {     //<== THIS LINE IS NEW
        var regForm     = $("#registration");

        regForm.submit(function(evt){
            // PREVENT FORM FROM DEFAULT BEHAVIOUR: SUBMITTING...
            evt.preventDefault();

            // IF BOTH CONDITIONS ARE SATISFIED... SUBMIT THE FORM MANUALLY...
            if(checkMailStatus() && checkCityStatus()){
                // SHIP THE FORM
                $(this).submit();
            }

        });


        function checkMailStatus(){
            var email       = $("#email").val();
            var returnVal   = 0;
            $.ajax({
                type:'post',
                    url:'check.php',
                    dataType:'json',
                    data:{email: email},

                    success:function(msg){
                        if(msg){
                            if(msg.message){
                                alert(msg.message); 
                            }
                        }else{
                            returnVal = 1;
                        }
                    }

             });
             return returnVal;
        }

        function checkCityStatus(){
            var keyword     = $("#keyword").val();
            var returnVal   = 0;
            $.ajax({
                type:'post',
                    url:'check.php',
                    dataType:'json',
                    data:{keyword: keyword},

                    success:function(msg){
                        if(msg){
                            if(msg.message){
                                alert(msg.message); 
                            }                                
                        }else{
                            returnVal = 1;
                        }  
                    }
            });
             return returnVal;
        }
        });      //<== THIS LINE IS NEW
    })(jQuery); //<== THIS LINE IS NEW
    </script>

PHP

            <?php

        $response = array("message"=>null);
        if(isset($_POST['register'])) {

            $email      = $_POST['email'];
            $keyword    = $_POST['keyword'];

            $check_d    = $db->query("SELECT id FROM users WHERE email='".$email."'");
            $check_d    = $check_d->num_rows;
            if($check_d != 1) {
                $response['message']    = 'E-mailadres bestaat al';
            }else{
                // ECHO A FLAG TO SHOW THAT EVERYTHING WORKS FINE: ZERO ISSUE WAS FOUND
                $response['message']    =  null;
            }

            $check_pnl  = ("SELECT woonplaats 
                           FROM `locatie` 
                           WHERE    woonplaats='".$keyword."'");
            $check_pnl  = $check_pnl->num_rows;
            if($check_pnl == 0) {
                $response['message']    =  'Plaatsnaam Bestaat Niet';
            }else{
                // ECHO A FLAG TO SHOW THAT EVERYTHING WORKS FINE: ZERO ISSUE WAS FOUND
                $response['message']    =  null;
            }
        }
        die(json_encode($response));
    ?>

NOTES:

Be advised that the PHP Script should not leave in the same File as the HTML Form and the JS. Otherwise the echo is reflected on the Page. Move thus the PHP to a separate file to avoid this anomaly.

Answer 2

There are few things you need to change in your code, such as:

• As I said in my comment, you have to send your AJAX request to a different page and process it there. So create a check.php page to process your AJAX request.
• Instead of creating two different functions i.e checkMailStatus() and checkCityStatus(), use only one function, for example checkStatus() function to validate your input fields.
• Add dataType setting to your AJAX request and send json object as response from the server. dataType is the type of data you're expecting as response from the server.
• Use prepared statements, it will prevent your database from any kind of SQL injection attack. See this SO Q/A.

I've assumed that both E-mailadres and Woonplaats are required fields.

So, on index.php page your HTML and jQuery script should be like this:

HTML:

<form action="" onSubmit="return checkStatus(this);" method="post" id="registration">
    <input type="email" name="email" id="email" placeholder="E-mailadres" value="" required>
    <input type="text" name="keyword" id="keyword" placeholder="Woonplaats" value=""  required>
    <button type="submit" name="register" class="btn-red">Registreren!</button>
</form>

jQuery:

<script>
    function checkStatus(f){
        var email=$("#email").val();
        var keyword=$("#keyword").val();

        $.ajax({
            type:'post',
            url:'check.php',
            data:{email: email, keyword: keyword},
            dataType: 'json',
            success:function(data){
                if(data.status == "success"){
                    f.submit();
                }else{
                    alert(data.msg);
                }
            }
        });
        return false;
    }
</script>

And on check.php page, process your AJAX request like this:

<?php

    // Your connection code
    $conn = new mysqli("localhost", "USERNAME", "PASSWORD", "DATABASE_NAME");

    if(isset($_POST['email'], $_POST['keyword'])){
        $email = $_POST['email'];
        $keyword = $_POST['keyword'];

        $stmt = $conn->prepare("SELECT id FROM users WHERE email=?");
        $stmt->bind_param("s", $email);
        $stmt->execute();
        $stmt->store_result();
        if($stmt->num_rows == 0){

            $stmt = $conn->prepare("SELECT woonplaats FROM `locatie` WHERE woonplaats=?");
            $stmt->bind_param("s", $keyword);
            $stmt->execute();
            $stmt->store_result();
            if($stmt->num_rows){
                echo json_encode(array('status' => 'success', 'msg' => 'no error'));
            }else{
                echo json_encode(array('status' => 'error', 'msg' => 'Place name does not exist'));
            }

        }else{
            echo json_encode(array('status' => 'error', 'msg' => 'Use a different email address'));
        }
    }else{
        echo json_encode(array('status' => 'error', 'msg' => 'missing fields'));
    }

?>

---

Don't forget to change this line $conn = new mysqli("localhost", "USERNAME", "PASSWORD", "DATABASE_NAME"); in your check.php page.