使用ajax和json将注释添加到mysql中

Question

As shown from the diagram, I have two tables in my mysql and I would like the system to add and retrieve comment without refreshing the page.

I have three php pages involved in this function and they are 'DB.php', 'comment.php' and 'action.php' The codes are as shown:

DB.php

<?php
    $conn = mysql_connect('localhost','Practical4','1234') or die (mysql_error);
    $db=mysql_select_db('Practical4', $conn) or die (mysql_error);
?>

comment.php

<!-- ajax script -->
<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript">
    $(function() {
        $(".submit_button").click(function() {
            var textcontent = $("#content").val();
            var name = $("#name").val();
            var dataString = 'content=' + textcontent + '&name=' + name;
            if (textcontent == '') {
            alert("Enter some text..");
            $("#content").focus();
        }
        else {
            $("#flash").show();
            $("#flash").fadeIn(400).html('<span class="load">Loading..</span>');
            $.ajax({
                type: "POST",
                url: "action.php",
                data: dataString,
                cache: true,
                success: function(html){
                    $("#show").after(html);
                    document.getElementById('content').value='';
                    $("#flash").hide();
                    $("#content").focus();
                }  
            });
        }
        return false;
    });
});
</script>
<div>

         <!-- retrieve hotel id from hotel table -->
<?php
include('DB.php');

$id=$_GET['id'];

$query = mysql_query("select * from hotel where name='$id'");
while($rows=mysql_fetch_array($query)){

    $name=$rows['name'];
    $price=$rows['price'];
    $duetime=$rows['dueTime'];
    $address=$rows['location'];
}
?>

  <!-- post form -->
<form method="post" name="form" action="">
<h3>Add Comment for <?php echo $name;?><h3>
<input type="text" name="name" id="name" value="<?php echo $name;?>" hidden > <br>
<textarea cols="30" rows="2" name="content" id="content" maxlength="145" >
</textarea><br />
<input type="submit" value="Post" name="submit" class="submit_button"/>

</form>
</div>
<div class="space"></div>
<div id="flash"></div>
<div id="show"></div>

action.php

<?php
include('DB.php');
$check = mysql_query("SELECT * FROM comment order by commentID desc");
if(isset($_POST['content']))
{
$content=$_POST['content'];
$name=$_POST['name'];

mysql_query("insert into comment (content,name) values ('$content','$name')");
echo '<div class="showbox">'.$content.'</div>';
}
?>

So far the code is working well, but I'd like to use JSON instead of using datastring in my ajax script, Any idea on how to change this?

Changed action.php using mysqli:

<?php

$DBServer = 'localhost'; // e.g 'localhost' or '192.168.1.100'
$DBUser   = 'Practical4';
$DBPass   = '1234';
$DBName   = 'Practical4';

$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);

// check connection
if ($conn->connect_error) {
  trigger_error('Database connection failed: '  . $conn->connect_error, E_USER_ERROR);
}

$v1="'" . $conn->real_escape_string('content') . "'";
$v2="'" . $conn->real_escape_string('name') . "'";

$sql="INSERT INTO comment (content,name) VALUES ($v1,$v2)";

if($conn->query($sql) === false) {
  trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
} else {
  $last_inserted_id = $conn->insert_id;
  $affected_rows = $conn->affected_rows;
  echo '<div class="showbox">'.$v1.'</div>';
}

?>

Answer 1

If you say your code works as is, but you just want to use JSON to send the data, all you need to change is the following in comment.php

<script type="text/javascript">
    $(function() {
        $(".submit_button").click(function() {
            var textcontent = $("#content").val();
            var name = $("#name").val();
            var dataString = {'content': textcontent, 'name': name}; \\ HERE
            if (textcontent == '') {
                alert("Enter some text..");
                $("#content").focus();
            }
            else
            {
                $("#flash").show();
                $("#flash").fadeIn(400).html('<span class="load">Loading..</span>');
                $.ajax({
                    type: "POST",
                    url: "action.php",
                    data: dataString,
                    dataType: 'json', \\ AND HERE
                    cache: true,
                    success: function(html){
                        $("#show").after(html);
                        document.getElementById('content').value='';
                        $("#flash").hide();
                        $("#content").focus();
                    }  
                });
            }
            return false;
        });
    });
</script>

dataString has been converted to json, and the dataType has been added to your ajax request.

On a side note, if you're going to use mysql_* functions you really need to sanitize the variables you're passing into the database. Ideally you should make use of prepared statements provided by PDO or mysqli.

EDIT:

Modify your success call to this;

success: function(html) {

    console.log(textcontent + ' : ' + name + ' : ' + html);

    $("#show").after(html);
    document.getElementById('content').value='';
    $("#flash").hide();
    $("#content").focus();
}

Then as a test comment out all of your action.php and simply add this;

var_dump($_POST);

Check the console...do you get what you expect? You should see the variables as in the form.