Editable database table with PHP+Ajax+jQuery

Question

I have a database named "guests" with two columns: Seat and Name. I want to show this database in a php page where the user can double click in the "name" value and edit it, updating the database when he click somewhere else (blur it).

The PHP:

<?php
include "connect.php";
$result = mysqli_query($con, "SELECT * FROM guests");
echo "<table>";
while($row = mysqli_fetch_assoc($result)){

echo "<tr><td contenteditable='true' onClick='showEdit(this);' onBlur='saveToDatabase(this,'Name','".$row['seat']."')'>" . $row['Name'] . "</td>

<td>" . $row['seat'] . "</td></tr>";
}
echo "</table>";
?>

The jQuery:

<script>
function saveToDatabase(editableObj,what,where) {
    $(editableObj).css("background","#FFF url(loaderIcon.gif) no-repeat right");
    $.ajax({
        url: "handle.php",
        type: "POST",
        data:'what='+what+'&newvalue='+editableObj.innerHTML+'&where='+where,
        success: function(data){
            $(editableObj).css("background","#FDFDFD");
        }
   });
}
</script>

The server side (handle.php):

<?php
include "connect.php";
$what = $_POST["what"];
$newvalue = $_POST["newvalue"];
$where = $_POST["where"];
$sql = "UPDATE guests SET $what='$newvalue' WHERE seat='$where'";
if ($con->query($sql) === TRUE) {
   header("Location: success.php");
} else {
    header("Location: error.php");
}
?>

Currently, after the user edit the "name" value and click somewhere else, nothing happens, and the database is not updated.

Have you checked what the $sql statement looks like? Is there an error log? — RGriffiths
– RGriffiths, Commented Aug 21, 2016 at 15:27
Zain Farooq - "what" is what column to change (in this case, "name"), "where" is in what row. In this test there is only name and seat, but i am planning to add other columns like email, phone, etc... And that's where $what comes in handy. The system needs to know what the user is editing. — Jonhz
– Jonhz, Commented Aug 21, 2016 at 15:31
RGriffiths - i don't think the server side (handle.php) is even being executed when the user loses focus of the editable object. — Jonhz
– Jonhz, Commented Aug 21, 2016 at 15:32
jsfiddle.net/ohah76hz/2 it works like you did it. You schould not make a myql query with unescaped field named as name (reserved!) — user6676773
– user6676773, Commented Aug 21, 2016 at 20:50

Fernando Torres · Accepted Answer · 2016-08-21 23:02:14Z

You don't have to use onBlur if you are doing a trigger with a onClick function. So, this code should be given your connection is ok :

<?php
include "connect.php";
$result = mysqli_query($con, "SELECT * FROM guests");
echo "<table>";
while($row = mysqli_fetch_assoc($result)){

echo "<tr><td contenteditable='true' onClick='saveToDatabase(this,'Name','".$row['seat']."')'>" . $row['Name'] . "</td>

<td>" . $row['seat'] . "</td></tr>";
}
echo "</table>";
?>

<script>
function saveToDatabase(editableObj,what,where) {
    $(editableObj).css("background","#FFF url(loaderIcon.gif) no-repeat right");
    $.ajax({
        url: "handle.php",
        type: "POST",
        data:'what='+what+'&newvalue=&where='+where,
        success: function(data){
            $(editableObj).css("background","#FDFDFD");
        }
   });
}
</script>

<?php
//handle.php
include "connect.php";
$what = $_POST["what"];
$newvalue = $_POST["newvalue"];
$where = $_POST["where"];
$sql = "UPDATE guests SET $what='$newvalue' WHERE seat='$where'";
if ($con->query($sql)) {
   header("Location: success.php");
} else {
   header("Location: error.php");
}
?>

Joe · Accepted Answer · 2018-06-28 09:26:07Z

Add something to database

<!DOCTYPE html>
<html>
<head>
    <title>index</title>
</head>
<body>
    <?php
    var_dump($_POST);
    if($_POST){
        $con = new PDO("mysql:host=localhost;dbname=database;", "root", "");
        $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $statement = $con->prepare("INSERT INTO person(name, dateofbirth, price, newsletter) VALUES(?, ?, ?, ?)");
    $statement->bindValue(1, $_POST["name"]);
    $statement->bindValue(2, $_POST["dateofbirth"]);
    $price = srt_replace(",", ".", $_POST["price"]);
    $statement->bindValue(3, $_POST["price"]);
    $statement->bindValue(4, isset($_POST["newsletter"]) ? 1 : 0);
    $statement->execute();
}
    ?>
<form method="post">
     Naam: <input type="text" name="name"/><br>
     Geboortedatum: <input type="text" name="dateofbirth"/><br>
     Prijs: <input type="text" name="price"/><br>
     Nieuwsbrief: <input type="checkbox" name="newsletter" value="xxxxxxxx"/><br>
     <br>

     <input type="submit" value="opslaan">
</form>
</body>
</html>

Joe · Accepted Answer · 2018-06-28 08:37:04Z

Index.php:

    <?php

$con = new PDO('mysql:host=localhost;dbname=names', "root", "");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$statement = $con->prepare("SELECT * FROM database");

$statement->execute();

$database= $statement->fetchAll(PDO::FETCH_OBJ);



?>

<!DOCTYPE html>
<html>
<head>
    <title></title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">
</head>
<body>
<div class="container-fluid p-3">
<h1>List of Facebook </h1>
<a href="addperson.php" class="btn btn-secondary mb-3">Add person</a>


    <table class="table table-striped table-bordered">
        <thead class="thead-dark">
            <th>ID</th>
            <th>Firstname</th>
            <th>Lastname</th>
            <th>Postcode</th>
            <th>Edit</th>
            <th>Delete</th>
        </thead>

    <tbody>
        <?php 

        foreach ($database as $row) {



            echo "<tr>";
            echo "<td>$row->id</td>";
            echo "<td>$row->firstName</td>";
            echo "<td>$row->lastName</td>";
            echo "<td>$row->postCode</td>";
            echo "<td><a href='edit.php?id=$row->id'><button class='btn btn-warning'>Edit</button></a></td>";
            echo "<td><a href='delete.php?id=$row->id'><button class='btn btn-danger'>Delete</button></a></td>";


            echo "</tr>";


        }





        ?>
    </tbody>
    </table>
    </div>
</body>
</html>

Edit.php

<!DOCTYPE html>
<html>
<head>
    <title>Edit</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">
<?php 

$con = new PDO('mysql:host=localhost;dbname=names', "root", "");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$statement = $con->prepare("SELECT * FROM database WHERE id = ?");
$statement->bindValue(1, $_GET["id"]);
$statement->execute();

$database = $statement->fetchObject();
?>

</head>
<body>

<h1>Edit</h1>
<form method="POST">

First name<input type="text" name="firstName" class="form-control" value="<?php echo $database->firstName; ?>"></input><br />
Last Name<input type="text" name="lastName" class="form-control" value="<?php echo $database->lastName; ?>" ></input> <br />
Post code<input type="text" name="postCode" class="form-control" value="<?php echo $database->postCode; ?>"></input> <br />

<input type="submit" value="submit" class="btn " value="<?php echo $database->visable; ?>" /> <br />
</form>

</body>
</html>


<?php 

if ($_POST) {
    header("location:index.php");
}






?>

delete.php

<?php 

$con = new PDO('mysql:host=localhost;dbname=names', "root", "");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$statement = $con->prepare("DELETE FROM database WHERE id = ?");

$statement->bindValue(1, $_GET["id"]);

$statement->execute();

header("location:index.php");

?>

add.php

<!DOCTYPE html>
<html>
<head>
    <title>Add person</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">
</head>
<body>
<h1>Add person</h1>
<?php

    if($_POST){

        header('location:index.php');

        $con = new PDO('mysql:host=localhost;dbname=names', "root", "");
        $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


        $statement = $con->prepare("INSERT INTO database(firstName, lastName, postCode) 
        VALUES(?, ?, ?)");
        $statement->bindValue(1, $_POST["firstName"]);
        $statement->bindValue(2, $_POST["lastName"]);
        $statement->bindValue(3, $_POST["postCode"]);
        $statement->execute();

    }

    var_dump($_POST);

?>



    <form method="POST">

        <input type="text" name="firstName" placeholder="First name" class="form-control" /> <br />
        <input type="text" name="lastName" placeholder="Last name" class="form-control"/> <br />
        <input type="text" name="postCode" placeholder="Postcode" class="form-control"/> <br />

        <input type="submit" value="submit" class="btn"/> <br />

    </form>
    </div>

</body>
</html>

Collectives™ on Stack Overflow

Editable database table with PHP+Ajax+jQuery

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related