Insert And Retrieve Data in MySQL with $.post Noob Question

Question

Trying to insert data into MySQL database with PHP. I don't want to refresh the page. The data isn't inserted when I press the Send Message button, but the data is displayed. Please help a noob out. Here's the HTML:

<!DOCTYPE html>
<html lang="en-US">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>HTML Contact Form</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<link rel="stylesheet" href="style.css" type="text/css">
</head>
<body>
<div class="form">
    <form method="POST" action="form.php" id="foo" name="foo">
        <h1>Contact Form</h1>
        <table>
            <tr>
                <td>
                    <label for="fname">Full Name:</label><br>
                    <input type="text" name="fname" placeholder="John Doe" id="">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="email">Your Email:</label><br>
                    <input type="email" name="email" placeholder="example@gmail.com" id="">
                </td>
            </tr>
            <tr>
                <td>
                    <label for="msg">Your Message:</label><br>
                    <textarea name="msg" placeholder="Type your message..." id="" cols="30" rows="10"></textarea>
                </td>
            </tr>
            <tr>
                <td>
                    <input type="submit" name="submit" value="Send Message">
                </td>
            </tr>
        </table>
    </form>
</div>
<p id="target">
</p>
<script>
$(function() {
  $("#foo").submit(function(event){
    // Stop form from submitting normally
    event.preventDefault();  
    /* Serialize the submitted form control values to be sent to the web server with the request */
    var formValues = $(this).serialize();
    // Send the form data using post
    $.post("form.php", formValues, function(response){
      $('#target').load('show.php');
    });
  });
});
</script>
</body>
</html>

Here's form.php which is supposed to insert data into the database:

<?php
error_reporting(E_ALL);
log_errors(1);
display_errors(1);
if(isset($_POST['submit']))
{
    $name = $_POST['fname'];
    $email = $_POST['email'];
    $message = $_POST['msg'];
    
    //database details. You have created these details in the third step. Use your own.
    $host = "localhost";
    $username = "user";
    $password = "GoTn_1290";
    $dbname = "form_entriesdb";

    //create connection
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $con = mysqli_connect($host, $username, $password, $dbname);
    //check connection if it is working or not
    if (!$con)
    {
        die("Connection failed!" . mysqli_connect_error());
    }
    //This below line is a code to Send form entries to database
    $sql = $con->prepare("INSERT INTO contactform_entries (name_fld, email_fld, msg_fld) VALUES (?, ?, ?)");
    $sql->bind_param("sss", $name, $email, $message);
    $sql->execute();


  //connection closed.
    $sql->close();
    $con->close();
}
?>

And here's what displays my data, show.php:

<?php
$servername = "localhost";
$username = "user";
$password = "secret";
$dbname = "form_entriesdb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
} 


$sql2 = "SELECT id, name_fld, email_fld, msg_fld FROM contactform_entries";
$result = $conn->query($sql2);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "id: " . $row["id"]. " - Name: " . $row["name_fld"]. " " . $row["email_fld"]. " " . $row["msg_fld"] . "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

Add error checking to your PHP script when it tries to execute the query. https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments — Barmar, May 12 '22 at 17:18
And what did you discover from that? Are you getting an error, and if so, what is it? — Barmar, May 12 '22 at 17:33
I dont understand this ___The data isn't inserted when I press the Send Message button, but the data is displayed___ How is the data that is not saved being displayed? — RiggsFolly, May 12 '22 at 17:47
If the new data is being displayed by `show.php` then the insert must have happened — RiggsFolly, May 12 '22 at 17:51
_"Not getting any errors."_ - where did you actually check for them? If there were any, they would be contained in `response` inside your `$.post` callback function. Did you log that value to the console, or check the response to the AJAX request in your browser dev tools, network panel? — CBroe, May 13 '22 at 07:39

score 0 · Answer 1 · answered May 14 '22 at 23:58

I ended up using .ajax instead of .post. I also changed my filename to index.php. I can't find the website where I got my code from, but here it is:

<!DOCTYPE html>
<html>
<head>
    <title>Insert data in MySQL database using Ajax</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
</head>
<body>
<div style="margin: auto;width: 60%;">
    <div class="alert alert-success alert-dismissible" id="success" style="display:none;">
      <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
    </div>
    <form id="fupForm" name="form1" method="post">
        <div class="form-group">
            <label for="email">Name:</label>
            <input type="text" class="form-control" id="name" placeholder="Name" name="name">
        </div>
        <div class="form-group">
            <label for="pwd">Email:</label>
            <input type="email" class="form-control" id="email" placeholder="Email" name="email">
        </div>
        <div class="form-group">
            <label for="pwd">Phone:</label>
            <input type="text" class="form-control" id="phone" placeholder="Phone" name="phone">
        </div>
        <div class="form-group" >
            <label for="pwd">City:</label>
            <select name="city" id="city" class="form-control">
                <option value="">Select</option>
                <option value="Delhi">Delhi</option>
                <option value="Mumbai">Mumbai</option>
                <option value="Pune">Pune</option>
            </select>
        </div>
        <input type="button" name="save" class="btn btn-primary" value="Save to database" id="butsave">
    </form>
</div>
<p id="target">
</p>

<script>
$(document).ready(function() {
$('#butsave').on('click', function() {
var name = $('#name').val();
var email = $('#email').val();
var phone = $('#phone').val();
var city = $('#city').val();
if(name!="" && email!="" && phone!="" && city!=""){
    $.ajax({
        url: "save.php",
        type: "POST",
        data: {
            name: name,
            email: email,
            phone: phone,
            city: city              
        },
        cache: false,
        success: function(dataResult){
            var dataResult = JSON.parse(dataResult);
            if(dataResult.statusCode==200){
                $("#butsave").removeAttr("disabled");
                $('#fupForm').find('input:text').val('');
                $("#success").show();
                $('#success').html('Data added successfully !');    
$('#target').load('show.php');                  
            }
            else if(dataResult.statusCode==201){
                alert("Error occured !");
            }
            
        }
    });

    }
    else{
alert('Please fill all the fields !');
}
});
});
</script>
</body>
</html>

Here's save.php. It's the code that inserts data into the database:

<?php
    include 'database.php';
    $name=$_POST['name'];
    $email=$_POST['email'];
    $phone=$_POST['phone'];
    $city=$_POST['city'];
  $sql = $con->prepare("INSERT INTO `crud`( `name`, `email`, `phone`, `city`) VALUES (?,?,?,?)");
$sql->bind_param("ssss", $name, $email, $phone, $city);
$rc = $sql->execute();

    if (true===$rc) {
        echo json_encode(array("statusCode"=>200));
    } 
    else {
        echo json_encode(array("statusCode"=>201));
    }
      //connection closed.
$sql->close();
 $con->close();
?>

Here is show.php:

<?php
include 'database.php';
// Check connection
if ($con->connect_error) {
  die("Connection failed: " . $con->connect_error);
} 

$query = "SELECT name, email, phone, city FROM crud";

$result = $con->query($query);

if ($result->num_rows > 0) {
  // output data of each row

/* fetch associative array */
while ($row = $result->fetch_assoc()) {
    echo $row["name"]. " " . $row["email"]. " " . $row["phone"]. " " . $row["city"]."<br>";
}
} else {
  echo "0 results";
}
  

$result -> free_result();
$con->close();
?>

And here are the database connection details, database.php:

<?php
    $servername = "localhost";
    $username = "user";
    $password = "secret";
    $db="school";
    $con = mysqli_connect($servername, $username, $password,$db);
?>

The code posted is entirely functional.

Insert And Retrieve Data in MySQL with $.post Noob Question

1 Answers1