Data not getting Inserted in MYSQL using Php

Question

I am making a web application which allows user to get funds for the project,I have got most part working but I am stuck at one place where the php script returns a positive status code but no data gets inserted into Mysql database. Following is the php script i am using:

Code:

<?php
session_start();
if(!isset($_SESSION['username']))
{
    echo "Unauthorised Page Usage Please Relogin to Access All the Page features;";
    header('location:login.html');


}


$sponsor=mysql_real_escape_string($_POST['sponsorid']);
$projectid=mysql_real_escape_string($_POST['projectid']);
$pledge=mysql_real_escape_string($_POST['pledgevalue']);


$servername = "localhost";
$usernam = "root";
$password = "";
$dbname = "project";
$httpStatusCode = 400;
$httpStatusMsg  = 'Incorrect Username or Password';
$protocol=isset($_SERVER['SERVER_PROTOCOL']) ? $_SERVER['SERVER_PROTOCOL'] : 'HTTP/1.0';

$connection=new mysqli($servername,$usernam,$password,$dbname);
if (!$connection) {
    die("Connection failed: " . $connection->connect_error);
} 

$sql1="INSERT INTO `sponsor`(`spon_id`,`project_id`,`spon_amt`,`spon_date_time`) VALUES ('$sponsor','$projectid','$pledge',NOW())";
$result=$connection->query($sql1);
if ($result) {
        $Success=200;
        $httpStatusMsg=mysqli_error($connection);
        header($protocol.' '.$Success.' '.$result);


 } 

    else {
$Success=400;
    $httpStatusMsg=mysqli_error($connection);
    header($protocol.' '.$Success.' '.$httpStatusMsg);
    }
?>

Below is the ajax used to post data to a page:

   var xhttp = new XMLHttpRequest();
         xhttp.onreadystatechange = function() {
        if (this.readyState === 4) {
            if(this.status===404){
                alert(this.responseText);
                                             }
            if(this.status===200)
            {

            alert("Project backed successfully");
            window.location.reload(true);



                    }

            }
  };    
        xhttp.open("POST", "sponsor.php", true);

        xhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        var param= "sponsorid"+"="+ <?php echo json_encode($_SESSION['username']); ?>+"&"+"pledgevalue"+"="+document.getElementById("pledge").value+"&"+"projectid"+"="+<?php echo json_encode($projectid);?>;
        console.log(param);
        xhttp.send(param);



        }

I have cross referenced my sponsor table to make sure that every field is same.The code works fine on my friend`s computer. Please help me

Update: the $sql1 query is giving me Error code:1644 Problem when I ran it in Database (using XAMMP).

Please help.

Mixing APIs, undefined variables.. You should enable error reporting, it would probably tell you a lot. Also learn how to utilize prepared statements for queries dealing with variables — Qirel, May 09 '17 at 04:44
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use manual escaping and string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). Accidentally unescaped data is a serious risk. Using bound parameters is less verbose and easier to review to check you’re doing it properly. — tadman, May 09 '17 at 04:49
It's also better to supply the query string *directly* to the `prepare` call. Intermediate variables are easily confused as you've demonstrated here. — tadman, May 09 '17 at 04:49
You also need to look at using something like [JQuery](http://jquery.com) instead of rolling your own XMLHttpRequest wrapper. `$.ajax` does everything you see here in a simple, understandable syntax. — tadman, May 09 '17 at 04:51
Possible duplicate of [Can I mix MySQL APIs in PHP?](http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php) — Qirel, May 09 '17 at 04:56

score 1 · Answer 1 · answered May 09 '17 at 04:38

1

Change

$result=$connection->query($sql);

To

$result=$connection->query($sql1);

answered May 09 '17 at 04:38

gobliggg

237
2
13

that was a typo,fixed it ,Still no luck. – omkar keluskar May 09 '17 at 04:55

score 0 · Answer 2 · answered May 09 '17 at 04:44

0

You have used $sql1 as a query and when you fire query to database you are using $sql which is not available actually.

If make error reporting ON you will see an error about undefined variable $sql If still it is not solve then print generated sql query using echo $sql1; and try it executing in database.you will get exact error.

answered May 09 '17 at 04:44

CyberAbhay

494
6
17

Still No luck, i fixed the typo – omkar keluskar May 09 '17 at 04:55
you should try to check first all data is received from Ajax.Then try to print your query which you can see in console as a response. – CyberAbhay May 09 '17 at 05:06

score -1 · Answer 3 · edited May 10 '17 at 06:21

Try this:

<?php
    session_start();
    if(!isset($_SESSION['username']))
    {
        echo "Unauthorised Page Usage Please Relogin to Access All the Page features;";
        header('location:login.html');


    }


    $sponsor=mysql_real_escape_string($_POST['sponsorid']);
    $projectid=mysql_real_escape_string($_POST['projectid']);
    $pledge=mysql_real_escape_string($_POST['pledgevalue']);


    $servername = "localhost";
    $usernam = "root";
    $password = "";
    $dbname = "project";
    $httpStatusCode = 400;
    $httpStatusMsg  = 'Incorrect Username or Password';
    $protocol=isset($_SERVER['SERVER_PROTOCOL']) ? $_SERVER['SERVER_PROTOCOL'] : 'HTTP/1.0';

    $connection=new mysqli($servername,$usernam,$password,$dbname);
    if (!$connection) {
        die("Connection failed: " . $connection->connect_error);
    } 

    $sql1="INSERT INTO `sponsor`(`spon_id`,`project_id`,`spon_amt`,`spon_date_time`) VALUES ('$sponsor','$projectid','$pledge',NOW())";
    $result=$connection->query($sql1);
    if ($result) {
            $Success=200;
            $httpStatusMsg=mysqli_error($connection);
            header($protocol.' '.$Success.' '.$result);


     } 

        else {
    $Success=400;
        $httpStatusMsg=mysqli_error($connection);
        header($protocol.' '.$Success.' '.$httpStatusMsg);
        }
    ?>

"*Try this*" answers are generally frowned upon. What did you change, and why? A good answer contains an explanation too, not just a code-dump. That being said, there are still errors in that code - you cannot mix MySQL APIs. — Qirel, May 09 '17 at 04:58

Data not getting Inserted in MYSQL using Php

3 Answers3