Solved

What is the best way to update form with most recent entry

Posted on 2013-01-20
11
323 Views
Last Modified: 2013-01-26
I am using ajax to run a php/mysql insert into query. That works fine.

What I now want to know is what is the best way to capture the id of the record that I have just entered.


I was thinking of running:
Select max(id) from table1

immediately after the insert into. Will that be ok or will there be conflict in the odd change that two person enters a record at the same time?
0
Comment
Question by:Sheils
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 
LVL 6

Accepted Solution

by:
esolve earned 350 total points
ID: 38797979
SELECT @@IDENTITY

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
0
 
LVL 16

Author Comment

by:Sheils
ID: 38798211
AS I said I am calling the php file which has the query from ajax. I have tried running echo Select @@IDENTITY in the php file but this did not return the index. How do I capture the id value in current page if the php script is running in an external file
0
 
LVL 6

Expert Comment

by:esolve
ID: 38798233
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
ID: 38798275
You're going to need to stop using MySQL and choose MySQLi or PDO in the near future (see the large red warning boxes on any MySQL function page to learn more).  But that aside, you can retrieve the AUTO_INCREMENT KEY from the last insert with this function.
http://php.net/manual/en/function.mysql-insert-id.php

The id is returned on a "per connection" basis.  In traditional web design, each request to the server makes its own connection, so the information you will get back will be the last record inserted by your instance of the script.  Typically "race conditions" are not a problem for INSERT.  It's SELECT + UPDATE that has need for a locking mechanism.

This article describes the handshake between client and server when jQuery/AJAX is in play.
http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Jquery/A_10712-The-Hello-World-Exercise-with-jQuery-and-PHP.html

HTH, ~Ray
0
 
LVL 16

Author Comment

by:Sheils
ID: 38799161
Ray,

That a nice simple article you've got. Thanks.

I tried the following codes:

js
<script>

$( document ).ready(function() {

    $(".button").click(function() {

        alert($(this).attr("name"));

        var d=$("input#datepicker").val();
        var a=$("select#fldWithdrawingAccount").val();
        datastring="send=&flddate=" + encodeURIComponent(d) + "&fldWithdrawingAccount=" + encodeURIComponent(a);
        alert (datastring);
     $.get("scripts/newExpenses.php", {myArg:datastring}, function(response){
            $("#output p#target").html(response);
        });
        alert ("finish passing");
        /*$.ajax({

            type:"GET",
            url : "scripts/newExpenses.php",
            data: datastring,

           success: function(reponse) {
               $("#output p#target").html(response);
                //$('.dataenterysubform').css({"visibility":"visible"});
            },

            error : function() {
                alert("Sorry, there was a problem!");

            },


        })*/;

        return false;
    });

});

</script>

Open in new window


php
<?php
error_reporting(E_ALL);
include "connect.php";
echo "in php<br />";
echo 'GET: ';
var_dump($_GET);

echo "<br />date here:  ".$_get['flddate'];

if (isset ($_get['send'])) {
  echo "in if";
    $Date = $_get['flddate'];
    $WithdrawingAccount = $_get['fldWithdrawingAccount'];

    $sql = "INSERT INTO `tbTransactions` (`fldDate`, `fldWithdrawingAccount`,`fldType`) VALUES (STR_TO_DATE('" . $Date . "', '%d %b %Y')," . $WithdrawingAccount . ",1)";

    echo $sql;

    mysqli_query($cnn, $sql) or die(mysqli_error($cnn));


    $result=mysqli_query($cnn,"select @@IDENTITY");
    $row=mysql_fetch_array($result);
    echo $row[0];

}

echo "<br />after if";

mysqli_close($cnn);

?>

Open in new window


and got the following response:
in php
GET: array(1) { ["myArg"]=> string(53) "send=&flddate=16%20Jan%202013&fldWithdrawingAccount=4" }
date here:
after if

Open in new window


1) How do I trigger the isset

2) As you can see I have comment out the $.ajax method that I was using previously. Can the response be used with the $ajax method which works fine except for not send back response
0
 
LVL 16

Author Comment

by:Sheils
ID: 38799208
Ray,

Dis regard question 2 found a typo in code and response is know working in $.ajax. I am still be in question 1 because your approach is more straight forward, less code.
0
 
LVL 16

Author Comment

by:Sheils
ID: 38799218
esolve,

The @@IDENTITY works fine just waiting on Ray's response before I complete question and distribute points
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38799257
What is the question at this point?  If you're happy with your solution, I am happy with any distribution of points you want to make.  I think I already have enough points to orbit Saturn, so it's not really an issue for me ;-)
0
 
LVL 16

Author Comment

by:Sheils
ID: 38800046
Ray,

I would like to use the method in your article:-

$.get("scripts/newExpenses.php", {myArg:datastring}, function(response){

$("#output p#target").html(response);
        });

as I prefer it over the $.ajax method, but I can't get it to trigger the isset function. Can you tell me who to make it trigger.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38801386
It may be that the issue lies here ...

if (isset ($_get['send'])) {

In PHP, variable names are case-sensitive.  I think you might want ...

if (isset ($_GET['send'])) {

or maybe it should be ...

if (isset ($_GET['myArg'])) {
0
 
LVL 16

Author Closing Comment

by:Sheils
ID: 38823617
Thanks Guys
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question