Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-20
11
Medium Priority
?
330 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 1400 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 600 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 111

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 111

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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