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

Question

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)

Community · Accepted Answer · 2017-05-23 11:58:28Z

4

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Mar 21, 2015 at 4:43

Adrian Cid Almaguer

7,79113 gold badges44 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

coffeecat Over a year ago

But I tried to echo the variable once it's inside the php function. It outputs it just fine. Meaning the data gets passed to the PHP function. The only problem is when it was passed on to the SQL query it just refuses to work.

Adrian Cid Almaguer Over a year ago

@coffeecat check this stackoverflow.com/q/18869908/3653989 and this stackoverflow.com/q/18968989/3653989 and this ajax-tutor.com/post-data-server.html

zipzit Over a year ago

think about it. All php commands run on the server BEFORE the code is sent to the user's browser. In this case var email, and passwd don't have any content yet. You can't use JavaScript (.getElementById().value) to obtain a value and then send it to php automatically. You can use JS to obtain a value, then call a php function on the server, but only via an ajax call

coffeecat Over a year ago

I see. I was fairly new to web programming so I don't know a lot of these stuff. Especially the server side - client side thing. I think I've read about AJAX before but got intimidated. I guess I have no other choice then. In any case, thanks for the help.

zipzit Over a year ago

easiest way to tiptoe into learning Ajax is to use the jQuery version. Do that a few times before jumping over to the XMLHttpRequest object. Use a CDN repository for jQuery, and things are pretty easy to use. By the way, this looks like a homework project. What class are you taking?

|

Choxx · Accepted Answer · 2015-03-21 05:01:26Z

0

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

answered Mar 21, 2015 at 5:01

Choxx

9452 gold badges26 silver badges47 bronze badges

Comments

zipzit · Accepted Answer · 2015-03-21 10:32:57Z

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;
    });

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related