CODEWITHSUNDEEP
javascriptphphtmlmysqlsql
coffeecat
coffeecat

Reputation: 45

PHP/Javascript + MySQL data is not being retrieved when query has variables

So I was trying to make a simple login with PHP. I can retrieve rows just fine but in this specific case there seems to be a problem in getting the data I needed:

When I execute a query with the condition in the WHERE clause via a variable, I never get the data from the column. I don't get an empty set either.

I tested the connection was just fine because when I override the variable inside the method the result returns just fine.

For instance, when I do this:

$sql = "SELECT name FROM customers WHERE email='" . $email . "'";
$result = $mysql->executeSql($conn, $sql);

if(!empty($result)){
    while($row = mysqli_fetch_array($result)){
        echo  $row['name'];
    }
}else{
    echo "EMPTY SET";
}

It doesn't return anything at all.

But if I do this:

$email = '[email protected]';
$sql = "SELECT name FROM customers WHERE email='" . $email . "'";
$result = $mysql->executeSql($conn, $sql);

if(!empty($result)){
    while($row = mysqli_fetch_array($result)){
        echo  $row['name'];
    }
}else{
    echo "EMPTY SET";
}

It fetches the data just fine. I wonder what is the cause of this. I already tried checking the $email data type via the gettype() method and it says it's a string. I also tried trimming it before passing it on to the query but to no avail as well.

What could possibly be the cause of this? Here is my entire sample code for the test login:

<HTML>
<HEAD>
    <?php
        function verifyLogin($email, $passwd){
            require('MySQL.php');

            $mysql = new MySQL;

            $conn = $mysql->connectToMysql("127.0.0.1", "root", "", "fivestarhotel");
            $sql = "SELECT name FROM customers WHERE email='" . $email . "'";
            $result = $mysql->executeSql($conn, $sql);

            if(!empty($result)){
                while($row = mysqli_fetch_array($result)){
                    echo  $row['name'];
                }
            }else{
                echo "EMPTY SET";
            }
        }
    ?>

    <SCRIPT>
        var email = "";
        var passwd = "";

        function buttonPressed(){
            email = document.getElementById("tEmail").value;
            passwd = document.getElementById("tPasswd").value;

            document.write("<?php verifyLogin('" + email + "','" + passwd + "');?>");
        }
    </SCRIPT>
</HEAD>
<BODY>
    <INPUT type="text" id="tEmail" /> <BR/>
    <INPUT type="password" id="tPasswd" /> <BR/>
    <INPUT type="button" value="Go" onclick="buttonPressed()"/>
</BODY>
</HTML>

And here is the MySQL.php for the abstraction of the connection and query execution:

<?php
class MySQL{
    var $conn;

    function connectToMySql($servername, $username, $password, $dbName){            
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbName);

        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
        return $conn;
    }

    function executeSql($conn, $sql){           
        $result = mysqli_query($conn,$sql);
        return $result;
    }
}
?>

Sample database (please don't mind the empty password yet):

mysql> select * from customers;
+----+------------------------+-------------+---------------------------+---------+
| id | name                   | contact     | email                     | password |
+----+------------------------+-------------+---------------------------+----------+
|  2 | Percival M. Micael Jr. | 09000000000 | [email protected] |          |
|  3 | Richard Traballo       | 09000000000 | [email protected]    |          |
|  4 | Richard Gwapo          | 09000000000 | [email protected]    |          |
|  5 | Darrel Ayien           | 09000000000 | [email protected]     |          |
|  6 | Dummy                  | 09000000000 | [email protected]           |          |
|  7 | Dummy2                 | 09000000000 | [email protected]         |          |
|  8 | Dummy3                 | 09000000000 | [email protected]         |          |
|  9 | Dummy4                 | 09000000000 | [email protected]         |          |
+----+------------------------+-------------+---------------------------+----------+
8 rows in set (0.00 sec)

Upvotes: 2

Views: 902

Answers (3)

zipzit
zipzit

Reputation: 3997

Here is code I've used in the past...

<div id="form-login">
  <form action="reload()" method="post">   
    <fieldset>
      <legend>Please Log In</legend>
      <div id="form-error"></div>
      <div class="form-row1">
        <label for="user" class="required">User:</label>
        <input name="user" type="text" id="user" maxlength="50" />
      </div>
      <div class="form-row2">
        <label for="password" class="required">Password:</label>
        <input type="password" name="password" id="password" maxlength="50">
      </div>
      <div class="form-row2 text-right" style="margin-right: 3px;">
        <input id="submit"  class="submit-button" type="submit" name="Submit" value="Submit" >
        <input id="cancel"  class="submit-button" type="button" value="Cancel" >
      </div>
    </fieldset>
  </form>
</div>

and elsewhere in JavaScript:

  // process the login via ajax
    $('#submit_login').click(function () {
        $('#form-error').hide();
        $('#submit').hide();
        $.ajax({
            type: 'POST',
            url: './php/login.php',
            data: 'email_address=' + $('#email').val() + '&password=' + $('#password').val(),
            dataType: 'json',
            success: function (data) {
                if (data.status == -1) {
                    $('#form-error').html(data.msg).slideDown('slow');
                } else if (data.status == 0) {
                    window.location.replace(data.url);  
                }
                $('#submit').show();
            },
            error: function (XMLHttpRequest, textStatus, errorThrown) {
                $('#form-error').html('Oops, Functional Error').slideDown('slow');
                $('#submit').show();
            }
        });
        return false;
    });

Upvotes: 0

Adrian Cid Almaguer
Adrian Cid Almaguer

Reputation: 7791

You can't do something like this. 

<SCRIPT>
    var email = "";
    var passwd = "";

    function buttonPressed(){
        email = document.getElementById("tEmail").value;
        passwd = document.getElementById("tPasswd").value;

        document.write("<?php verifyLogin('" + email + "','" + passwd + "');?>");
    }
</SCRIPT>

Javascript is client side and PHP is server side. Your javascript code only write this line <?php verifyLogin('" + email + "','" + passwd + "');?> in your HTML page.

You need use at least Ajax. Check this:

Using ajax to send form data to php

How to send data to php file using ajax

http://www.ajax-tutor.com/post-data-server.html

Upvotes: 4

Choxx
Choxx

Reputation: 945

you must use AJAX in this case. Send the variable name emailid values from the html to another php through ajax as request. In php, get the emailid variable value and there run your sql query to to get other required details. Store the result in a php variable, echo it from the php and receive it in the javascript using ajax response. for more info about ajax, visit this http://www.w3schools.com/ajax/ajax_xmlhttprequest_response.asp

Upvotes: 0

Related Questions