Reload the query after adding the record to mysql database with ajax without reloading the page

Question

I have a page where user can add the records to the mysql database. On the same page there is a list existing records in the database.

I use ajax to add the record to database, sorry for old version of mysql, will get updated after I get this thing to work first:

$(document).ready(function(){
//on the click of the submit button 
$("#btn_submit").click(function(){
 //get the form values
 var username = $('#hdn_username').val();     
 var name = $('#txt_name').val();     
 var brand = $('#txt_brand').val(); 

 //make the postdata
 var postData = 'username='+username+'&name='+name+'&brand='+brand;

 //call your input.php script in the background, when it returns it will call the success function if the request was successful or the error one if there was an issue (like a 404, 500 or any other error status)

 $.ajax({
    url : "input.php",
    type: "POST",
    data : postData,
    success: function(data,status, xhr)
    {
        //if success then just output the text to the status div then clear the form inputs to prepare for new data
        $("#status_text").html(data);
        $('#name').val('');
        $('#brand').val('');
    },
    error: function (jqXHR, status, errorThrown)
    {
        //if fail show error and server status
        $("#status_text").html('there was an error ' + errorThrown + ' with status ' + textStatus);
    }
});
}); });

My html is:

<table class="simplet" width="640" border="0" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th width="300" colspan="2">Product Name</th>
<th width="150">Brand</th>
<th width="100">Quantity</th>
<th width="60">Price</th>
<th width="30">Delete</th>
</tr>
</thead>
<tbody>
<tr><td colspan="6"><div id="status_text_list" /></td></tr>

[insert_php]

$current_user= wp_get_current_user();
$username= $current_user->user_login;

mysql_connect("localhost","xxxxx","xxxx");//database connection
mysql_select_db("xxxx");

$query= "SELECT * FROM wp_userdata WHERE username='$username'";
$result= mysql_query($query);
if (!$result) { die("Error db request: <br />". mysql_error()); }

while ($result_row = mysql_fetch_array($result, MYSQL_ASSOC)){ echo '<tr class="odd-row"><td width="30"></td><td>'.$result_row['product_name'].'</td><td>'.$result_row['product_brand'].'</td><td>'.$result_row['product_quantity'].'</td><td>'.$result_row['product_link'].'</td><td><a class="link-delete" href="#" data-delete-id="' . $result_row['id'] . '">X</a></td></tr>';
}


[/insert_php]

</tbody></table>
<br />

<table border="1">
  <tr>
    <td align="center">Add Products</td>
  </tr>
  <tr>
    <td>
         <form onsubmit="return false">
      <table>
<input type="hidden" id="hdn_username" name="username" value="[insert_php]echo $username;[/insert_php]">
        <tr>
          <td>Product Name</td>
          <td><input type="text" id="txt_name" name="name" size="50">
          </td>
        </tr>
        <tr>
          <td>Brand</td>
          <td><input type="text"  id="txt_brand" name="brand" size="50">
          </td>
        </tr>
<tr>
                <td></td>
                <td><div id="status_text" /></td>
            </tr>
        <tr>
          <td></td>
          <td align="right"><input type="submit" id="btn_submit" name="submit" value="Submit"></td>
        </tr>
</form>
        </table>
      </td>
    </tr>
</table>

And my input.php is:

mysql_connect("localhost","xxxx","xxxx");//database connection
mysql_select_db("xxxx");




//inserting data order
$order = "INSERT INTO wp_userdata
            (username, product_name, product_brand)
            VALUES
            ('$_POST[username]',
             '$_POST[name]',
            '$_POST[brand]')";

//declare in the order variable
$result = mysql_query($order);  //order executes
if($result){
    echo ("DATA SAVED SUCCESSFULLY");
} else{
    echo("<br>Input data is fail");
}

When I click the Submit button, form values sent to input.php and the success message is displayed, you can see it here - http://suppliesprices.com/?page_id=4

What I want to achieve is to add the new data row to the displayed list without reloading the page.

I was thinking to render it with javascript and add to a success ajax call, but not sure if it going to work.

How do I change my code to get the desired effect?

Tyler Marien · Accepted Answer · 2014-08-14 15:22:12Z

1

To add another row to the table, you can use the jQuery append function. You can do this inside you success function like so:

success: function(data,status, xhr)
{
    //if success then just output the text to the status div then clear the form inputs to prepare for new data
    if (data.status == 'success') {
        $("#status_text").html('DATA SAVED SUCCESSFULLY');
        $('#name').val('');
        $('#brand').val('');

        $('.simplet tbody').append('<tr class="odd-row"><td width="30"></td><td>'+data.product.product_name+'</td><td>'+data.product.product_brand+'</td><td>'+data.product.product_quantity+'</td><td>'+data.product.product_link+'</td><td><a class="link-delete" href="#" data-delete-id="' + data.product.id + '">X</a></td></tr>');
    } else {
        $("#status_text").html(data.message);
    }
},

You will want to update your PHP code to return some more information. Most commonly you would return JSON containing information about whether the transaction succeeded and a copy of the information that was inserted into your database (or an error message if it failed). You could do something like the following:

if($result){
    $id = mysql_insert_id();
    $retVal = array(
        'status' => 'success',
        'product' => array(
            'id' => $id,
            'product_name' => $_POST['name'],
            'product_brand' => $_POST['brand'],
            'product_quantity' => 0,
            'product_link' => '/?product_id=' . $id // not sure if this is the correct format or not 
        )
    );
} else {
    $retVal = array(
        'status' => 'error',
        'message' => 'Product could not be inserted'
    );
}

header('Content-Type: text/json');
echo json_encode($retVal);

You definitely should update the database code to follow better security practices, but this should get you started.

edited Aug 14, 2014 at 15:22

answered Aug 12, 2014 at 22:01

Tyler Marien

7624 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Acidon Over a year ago

when I apply the code< I was getting some mysql errors

Tyler Marien Over a year ago

Oops, shouldn't be passing $result into mysql_insert_id(). I edited it. Give that a try.

Acidon Over a year ago

I get

{"status":"success","product":{"id":96,"product_name":"sdf","product_brand":"ffff","product_quantity":0,"product_link":"\/?product_id=96"}}

displayed in my div id="status_text_list", and now row added to the table

Tyler Marien Over a year ago

Take a look at the edit now. I never updated your code from above. Feel free to pass whatever you want into the status DIV on success and failure. The above is just an example of how you could do it.

Tyler Marien Over a year ago

You are missing the if statement and didn't change the line below it. Make sure you have the line starting with "if". And make sure to update the line below it to match the one above.

|

developerwjk · Accepted Answer · 2014-08-12 23:38:58Z

0

Two things you can do.

Option 1:

Just use a regular button type='button' rather than a submit button type='submit'. A submit button by default changes the page, while a regular button does not.

Option 2:

$("#btn_submit").click(function(event){
  event.preventDefault();
 //get the form values

See docs for event.preventDefault. The default action of a submit button is to submit by going to the page defined in the form's action attribute (or back to the same page if you didn't specify an action attribute). This call prevents that default action so you can use Ajax instead.

And on the form, you can get rid of the onsubmit="return false" because that will not work.

Edit:

A bug I found in your Ajax. You're using GET syntax with POST.

Do not use: var postData = 'username='+username+'&name='+name+'&brand='+brand; followed by data : postData, in your Ajax with POST.

Rather, use in your ajax data: { username: username, name: name, brand: brand }. With POST the = and & are not used.

Also, in PHP: $_POST[username] is wrong. Should be $_POST['username'].

edited Aug 12, 2014 at 23:38

answered Aug 12, 2014 at 21:48

developerwjk

8,6342 gold badges20 silver badges33 bronze badges

3 Comments

Acidon Over a year ago

i tried the second approach but doesnt work, here - suppliesprices.com/?page_id=4

developerwjk Over a year ago

And the result is what? With the first solution, if it fails, you definitely should not change pages nor refresh this page. It just won't do anything and you'll have to figure out where else you have a bug in your code.

Acidon Over a year ago

I've changed the ajax part still no luck suppliesprices.com/?page_id=4 , php works fine without "'" when I add it it breaks

Collectives™ on Stack Overflow

Reload the query after adding the record to mysql database with ajax without reloading the page

2 Answers 2

7 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related