Solved

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

Posted on 2013-01-20
11
285 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
  • 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
 
LVL 108

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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 108

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 108

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction A frequently asked question goes something like this:  "I am running a long process in the background and I want to alert my client when the process finishes.  How can I send a message to the browser?"  Unfortunately, the short answer …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now