ajax to insert php variables into database

Question

I'm working on Student Attendance Marking. I want to use AJAX for this purpose but am not able to insert data into database. Following is my code:-

HTML:

<a id="Present" href="#" class="btn btn-md btn-success" onClick="Present(<?php echo $id_1; ?>);">Present</a><br/><br/>

Javascript:

<script>
function Present(Pid){
    var Pyear = <?php echo $selected_year; ?>;
    var Pmonth = "<?php echo $selected_month; ?>";
    var Pday = <?php echo $selected_day; ?>;
    var Pdate = "<?php echo $selected_date; ?>";
    $.ajax({
        type: "POST",
        url: 'mark_present.php',
        data: { id : Pid, year : Pyear, month : Pmonth, day : Pday, date : Pdate },
        dataType: "JSON",
        success: function(data){
            $("#message").html(data);
            window.location.href = 'mark_attendance.php';
            //window.location.reload();
        },
        error: function() {
            alert("Failure");
        }
    });
}

mark_present.php:

<?php 
include "db.php";
    $student_id = $_POST['id'];
    $year = $_POST['year'];
    $month = $_POST['month'];
    $day = $_POST['day'];
    $date = $_POST['date'];

    $sql = "INSERT INTO `student_attendance` SET student_id = '$student_id', year = '$year', month = '$month', day = '$day', date = '$date', status = '1' ";
    $result = mysql_query($sql);

    if($result){
        return json_encode(array("message"=>true));
        //echo "success";
    }else{
        return json_encode(array("message"=>false));
        //echo "error";
    }
?>

@CMiller OP is not mixing insert and update, it is also the way of insert http://mysqlresources.com/documentation/data-manipulation/insert-single-row-insertion-set-clause — Prateik Darji, Dec 23 '17 at 05:12
Please provide what error you are getting, are you able to print data on the php page sent using ajax? — Prateik Darji, Dec 23 '17 at 05:13
Unless you echo a number (or json) you need to quote strings passed to javascript variables. Check your browser dev tools console for errors — charlietfl, Dec 23 '17 at 05:14
In Console Log, I'm getting this error: Uncaught ReferenceError: December is not defined at Present (mark_attendance.php?day=23&month=December&year=2017&class=10&date=23-12-2017&search=:807) at HTMLAnchorElement.onclick (VM5824 mark_attendance.php?day=23&month=December&year=2017&class=10&date=23-12-2017&search=:395) — Simbolae, Dec 23 '17 at 05:14
Check if your `student_id`, `year`, `month` and `day` are set to type `INT` or `VARCHAR` in the database table. If they are `INT` that may be the problem, because you are entering them as strings. — Ivan86, Dec 23 '17 at 05:15
@Prateik. Sorry I did not know that. I deleted the comment so as not to mislead anyone. — C Miller, Dec 23 '17 at 05:17
Thanks @charlietfl, yes it needed to have quotes, but now there is not the success function executing, instead error function gets executed after inserting into database — Simbolae, Dec 23 '17 at 05:18
You are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. See [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). You're also vulnerable to [XSS](https://www.owasp.org/index.php/Cross-site_Scripting_(XSS)) attacks. This code is *extremely dangerous* and should not be used for any purpose. — elixenide, Dec 23 '17 at 05:23
There are so many things wrong with the given code, it would take an essay to explain it all. A summary, using multiple `id`'s on elements which most likely got multi, not using event handlers instead of onClick. not passing params to the event with data-* attributes, not using an object instead of setting many vars, quotes, not checking variables before using, **mysql_** and no protection against sql injection. — Lawrence Cherone, Dec 23 '17 at 05:26
I almost wonder if Stackoverflow could grep mysql_query in questions and automatically throw up a warning. (humor) — C Miller, Dec 23 '17 at 05:27

Suresh Suthar · Answer 1 · 2017-12-23T05:16:18.673

-1

<?php 
include "db.php";
$student_id = $_POST['id'];
$year = $_POST['year'];
$month = $_POST['month'];
$day = $_POST['day'];
$date = $_POST['date'];

$sql = "INSERT INTO student_attendance(student_id,year,month,day,date,status) value('$student_id', '$year', '$month', '$day', '$date', '1') ";
$result = mysql_query($sql);
?>

Mistake is on your insert query.

edited Dec 23 '17 at 05:16

answered Dec 23 '17 at 05:13

Suresh Suthar

794
8
15

Prateik Darji · Accepted Answer · 2017-12-23T05:23:58.790

-1

var Pyear = "<?php echo $selected_year; ?>";
var Pmonth = "<?php echo $selected_month; ?>";
var Pday = "<?php echo $selected_day; ?>";
var Pdate = "<?php echo $selected_date; ?>";

you need to use " to get php variable to javascript check this link

Change your PHP code to

<?php 
include "db.php";
$student_id = $_POST['id'];
$year = $_POST['year'];
$month = $_POST['month'];
$day = $_POST['day'];
$date = $_POST['date'];

$sql = "INSERT INTO `student_attendance` SET student_id = '$student_id', year = '$year', month = '$month', day = '$day', date = '$date', status = '1' ";
$result = mysql_query($sql);
if(!$result){
    return json_encode(array("message"=>"There is some error".mysql_error());
} else {
    return json_encode(array("message"=>"Record inserted successfully"));
}
?>

And change your success function to

.success : function (data){
    console.log(data);
 }
.error : function(data){
    console.log(data);
 }

you will get the error in console or success message

edited Dec 23 '17 at 05:23

answered Dec 23 '17 at 05:19

Prateik Darji

2,297
1
13
29

This is vulnerable to [XSS](https://www.owasp.org/index.php/Cross-site_Scripting_(XSS)) (among other problems). – elixenide Dec 23 '17 at 05:23
yes for that OP needs to upgrade the code and also should not use mysql_* instead use of mysqil_* functions or pdo – Prateik Darji Dec 23 '17 at 05:28
How to reload the page after success? – Simbolae Dec 23 '17 at 06:08
use window.location.href = 'location of your page' – Prateik Darji Dec 23 '17 at 06:10
It's not working, Neither can I get data back from the PHP file to display on main page nor is it reloading the page, just the data insertion is ok – Simbolae Dec 23 '17 at 06:25
can u pelase update your question with latest changes – Prateik Darji Dec 23 '17 at 06:27
can u please make comment on `$('#message').html() portion` and before that please check in console if you getting any error or something over there – Prateik Darji Dec 23 '17 at 07:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/161846/discussion-between-simbolae-and-prateik-darji). – Simbolae Dec 23 '17 at 07:18

ajax to insert php variables into database

2 Answers2