CODEWITHSUNDEEP
javascriptphpjquerymysqlfunction
Tunna182
Tunna182

Reputation: 343

Add SQL to function called 'onclick'

I have a button called "Add" which relates to each person in a list. 

  echo <td><input type=\"submit\" id=\"PlayerAdded".$id."\" value=\"Add\" 
  onclick=\"add('".$id."','".$name."','".$_SESSION['GameID']."');\"></input></td>";

When "Add" is clicked, I want to add the persons details to a table (working fine), and also add a new row including their name and ID into a table in my DB (this is my problem).

I am aware that PHP won't work in a JS function. I'm also aware that I should never access the database on my site from JS due to the security issues.

So how can I have a button which onclick calls a function which adds to the database?

I tried writing my function in PHP, but it wasn't doing anything when I clicked the button. See my function below:

function add(id,name,game){
    var t = ("</td><td>");
    var str = ("<tr id='Players" + id + "'><td>")
    var ctr = ("</td></tr>")

    var PID = ("<input type = 'hidden' name='ID" + id + "' value='" + id + "'></input>" + id)
    var Pnam = ("<input type='hidden' name='Name" + id + "' value='" +  name + "'></input>");
    var place = ("<select name='Place" + id + "'><option value='17'>17th</option><option value='16'>16th</option><option value='15'>15th</option><option value='14'>14th</option><option value='13'>13th</option><option value='12'>12th</option><option value='11'>11th</option><option value='10'>10th</option><option value='9'>9th</option><option value='8'>8th</option><option value='7'>7th</option><option value='6'>6th</option><option value='5'>5th</option><option value='4'>4th</option><option value='3'>3rd</option><option value='2'>2nd</option><option value='1'>1st</option></select>")
    var points = ("<form action='leaderboards.php' method='post' target='_blank'><input type='hidden' name='playerid' value='" + id + "'></input><input type='hidden' name='name' value='" + name + "'></input><input type='submit' value='View'></form>");
    var cash = ("$<input name='Cash" + id + "' placeholder=' 0'></input>");
    var ticket = ("<select name='Ticket" + id + "'><option value='No'>No</option><option value='Yes'>Yes</option>");
    var del = ("<input type='button' value='Delete' onclick='remove(" + id + ")'> </input>")

   $('#PlayerAdded').before(str+ PID + t + Pnam + name + t + place + t + points + t + cash + t + ticket + t + del + ctr);

// This is where I want to insert into the db. Similar to 
      // <?php
      // $sql = "INSERT INTO results (`col`, `col2`, `col3`) VALUES ('$id', '$name', '$game')"; 
      // mysqli_query($dbcon,$sql); ?>
//but I want to be able to do it inside this java script function.

  }

Upvotes: 1

Views: 6922

Answers (2)

Susheel Singh
Susheel Singh

Reputation: 3854

Remove the bracket before the variables, which you have written for every variable in function.

Add the ajax piece at the end of your function code.

function add(id, name, game) {
    var t = "</td><td>";
    var str = "<tr id='Players" + id + "'><td>"
    var ctr = "</td></tr>"

    var PID = "<input type = 'hidden' name='ID" + id + "' value='" + id + "'></input>" + id;
    var Pnam = "<input type='hidden' name='Name" + id + "' value='" + name + "'></input>";
    var place = "<select name='Place" + id + "'><option value='17'>17th</option><option value='16'>16th</option><option value='15'>15th</option><option value='14'>14th</option><option value='13'>13th</option><option value='12'>12th</option><option value='11'>11th</option><option value='10'>10th</option><option value='9'>9th</option><option value='8'>8th</option><option value='7'>7th</option><option value='6'>6th</option><option value='5'>5th</option><option value='4'>4th</option><option value='3'>3rd</option><option value='2'>2nd</option><option value='1'>1st</option></select>";
    var points = "<form action='leaderboards.php' method='post' target='_blank'><input type='hidden' name='playerid' value='" + id + "'></input><input type='hidden' name='name' value='" + name + "'></input><input type='submit' value='View'></form>";
    var cash = "$<input name='Cash" + id + "' placeholder=' 0'></input>";
    var ticket = "<select name='Ticket" + id + "'><option value='No'>No</option><option value='Yes'>Yes</option>";
    var del = "<input type='button' value='Delete' onclick='remove(" + id + ")'> </input>";

    $('#PlayerAdded').before(str + PID + t + Pnam + name + t + place + t + points + t + cash + t + ticket + t + del + ctr);

    // making ajax call to insert.php and posting the data
    $.ajax({
        method: "POST",
        url: "insert.php",
        data: {
            "id": id,
            "name": name,
            "game": game
        },
         beforeSend:function(){
            // show something before data is saved to db.
         }
    }).done(function(msg) {
        $("body").append(msg); //debugging purpose
    }).fail(function( jqXHR, textStatus ) {
        alert( "Request failed: " + textStatus );
    });
}

insert.php

<?php
   if(isset($_POST['id']) && isset($_POST['name']) && isset($_POST['game'])){
      $id= $_POST['id'];
      $name= $_POST['name'];
      $game= $_POST['game'];   

    //write your connection logic

    // never write bare queries there is a chance of sql injection.
    //Instead use prepared statement, prepare your query then bind the above values to it, then excute. 
    //write your insert logic below.
}
?>

Upvotes: 4

Mi-Creativity
Mi-Creativity

Reputation: 9654

You need to post the values you want to insert to your database via AJAX, append this to your add() function:

$.post('insertnew.php', { id:id,name:name,game:game }, function(data){
    alert(data);
});

insertnew.php:

<?php
   if(isset($_POST['id']) && isset($_POST['name']) && isset($_POST['game'])){
    // sanitize your data here, then:
      $id = $_POST['id'];
      $name = $_POST['name'];
      $game = $_POST['game'];

   //connect to your db, or instead include your dbconnect.php
      $link= new mysqli('localhost', 'my_user', 'my_password', 'world');

   // check connection
     if (mysqli_connect_errno()) {
         printf("Connect failed: %s\n", mysqli_connect_error());
         exit();
     }
   //use prepared statement, prepare your query then bind the above values to it, then excute
     $stmt = mysqli_prepare($link, "INSERT INTO results VALUES (?, ?, ?)");
     mysqli_stmt_bind_param($stmt, $id, $name, $game);

     if(mysqli_stmt_execute($stmt)){
          echo "Record has been added successfully";
     }else{
          echo "Sorry, record could not be inserted";
     }
   }
?>

http://php.net/manual/en/mysqli.prepare.php

http://php.net/manual/en/mysqli-stmt.bind-param.php

Upvotes: 0

Related Questions