Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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

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
Sheils
Asked:
Sheils
  • 6
  • 3
  • 2
2 Solutions
 
esolveCommented:
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
 
SheilsAuthor Commented:
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
 
esolveCommented:
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
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
 
SheilsAuthor Commented:
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
 
SheilsAuthor Commented:
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
 
SheilsAuthor Commented:
esolve,

The @@IDENTITY works fine just waiting on Ray's response before I complete question and distribute points
0
 
Ray PaseurCommented:
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
 
SheilsAuthor Commented:
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
 
Ray PaseurCommented:
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
 
SheilsAuthor Commented:
Thanks Guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now