Solved

obtain id of just added mysql record in php

Posted on 2014-02-03
11
397 Views
Last Modified: 2014-02-03
Hi
My code inserts an entry into a table and then I want to display the primary key of the newly added entry. The code below appeared to work, but I have just realised it is also the reason for two identical entries in the table with consecutive primary keys.
I believe this is the offending line -
$organisation_id = $mysqli->insert_id;
All I want is to get $organisation_id for display
What should I change to get just one entry added to the table?

// update tblAgents
                $upd_tblReferringOrganisations_sql = "INSERT INTO tblReferringOrganisations (organisation_id, site_code, organisation_name, property_name, street_number, street_name,
                                                        area, town_city, postcode, telephone_number_1, telephone_number_2, fax_number, referrer_type, region, date_record_created, subsidiary_of)
                                                   VALUES ('".$_POST["organisation_id"]."', '".$_POST["site_code"]."',  '".$_POST["organisation_name"]."', '".$_POST["property_name"]."',
                                                   '".$_POST["street_number"]."', '".$_POST["street_name"]."',   '".$_POST["area"]."',
                                                   '".$_POST["town_city"]."',  '".$_POST["postcode"]."',
                                                   '".$_POST["telephone_number_1"]."', '".$_POST["telephone_number_2"]."',  '".$_POST["fax_number"]."',
                                                '".$_POST["referrer_type"]."', '".$_POST["region"]."',  now(), '".$_POST["subsidiary_of"]."'
                                                        )";

                $upd_tblReferringOrganisations_res = mysqli_query($mysqli, $upd_tblReferringOrganisations_sql) or die(mysqli_error($mysqli));



$mysqli->query($upd_tblReferringOrganisations_sql);
$organisation_id = $mysqli->insert_id;
printf ($organisation_id);



        mysqli_close($mysqli);


        $display_block .= "<p>Referring Organisation $organisation_id has been added.   <a href=\"tfpagents.php\"> return to menu</a>?</p><br/>

                <form method=\"post\" action=\"tfpaddreforg.php\">
                <input type=\"hidden\" name=\"loop\" value='first'>
                <input type=\"submit\" name=\"submit\" value=\"Select\"></p>
                </form>";
}
 echo $display_block; 

Open in new window

Question edited to add the code to the Code snippet
0
Comment
Question by:peter_lawrie
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 39829564
Waht about $mysqli->insert_id
Look here

HTH
Ivo Stoykov
0
 

Author Comment

by:peter_lawrie
ID: 39829585
Yes, that's exactly what I used, but it created a second duplicate table entry with the next consecutive key!
Peter
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 39829660
did you test this?
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n", $mysqli->insert_id);

Open in new window

It works fine for me...

Ivo Stoykov
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39829683
This article shows how to use MySQLi correctly.  You must escape external variables!  You must test for errors.  MySQLi is not a black box.  It can and will fail for reasons that are sometimes outside of your control, and your script needs to report these failures.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

But that said, there is nothing in the code you posted here that would cause duplicate entries, unless you somehow ran the query twice.  You can prevent duplicate entries by marking column(s) UNIQUE.  Then if your script tries to INSERT a duplicate record, SQL will throw error number 1062.  You can catch this as an exception and ignore the duplicate INSERT or notify the client about the issue.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39829914
insert_id will return the last automatically generated ID, but judging by your query, you are manually populating the organisation ID from a form value: $_POST["organisation_id"]

In order for insert_id to work, you should set the organisation_id in your table to be an auto-increment field and NOT set it in your INSERT query (it will auto generate!).

At the moment, if it's not auto-generated, then just use the form values:

$organisation_id = $_POST["organisation_id"]
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:peter_lawrie
ID: 39829952
Hi Chris
The table is set to auto-increment on the primary key,
so, as you suggested, I removed organisation_id from the INSERT query, but I still get a duplicate entry with an identical date/time stamp.
All the code does is display a form if !POST and does an INSERT from the form data otherwise.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39829962
Show us your full code again. If the field's set to auto-increment and you're not passing it through your query, then you won't get a duplicate.

I'm also assuming that the organisation_id is your primary key
0
 

Author Comment

by:peter_lawrie
ID: 39830103
Hi
Thanks for the reply. I have three almost identical pieces of code doing the same thing maintaining three different reference tables. Not surprising as I copied and amended to create them. I have modified the attached code to exclude the primary key which is set to auto-increment organisation_id
It's almost certainly a stupid mistake on my part, but I can't see it.
Peter
tfpaddreforg.php
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39830184
Right - it looks like you're mixing OOP with procedural, and running the same query twice:

//procedural
$upd_tblReferringOrganisations_res = mysqli_query($mysqli, $upd_tblReferringOrganisations_sql) or die(mysqli_error($mysqli));

//OOP
$mysqli->query($upd_tblReferringOrganisations_sql);

Open in new window

You should stick with OOP and only run the query once.

I'm guessing that in one of your include files, you are creating a new instance of a db connection:

$mysqli = new mysqli("host", "user", "password", "dbname");

$upd_tblReferringOrganisations_sql = "SQL String for your INSERT Query";
$mysqli->query($upd_tblReferringOrganisations_sql);
$organisation_id = $mysqli->insert_id;
printf ($organisation_id);

Open in new window

0
 

Author Closing Comment

by:peter_lawrie
ID: 39830398
Thanks Chris, that fixed it - No more duplicate entries.
I've more than 40 years programming experience, but I'm finding php/mysql hard going.
I spent years assuming as computers became more powerful, they would increasingly look after the semantics of programming and leave programmers to be creative.
This stuff is worse than programming in assembler!
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 39830425
Great. Please you got it working.

Keep at it - it will click and once it does, you'll wonder why you ever struggled - it does get easier.

The modern approach really is Object Oriented Programming (OOP) so you need to get your head around that (irrespective of language). Once you get that, then things like mySQLi and PDO (PHP Data Objects) become a breeze and you'll really see the power and flexibility in using them.

Currently your code is a bit of mix between modern (OOP) and old-school which is probably why it's not making a great deal of sense - it's like having a book written in partly English and partly Ancient Greek!

For your DB stuff, you really should look into Prepared statement (mySQLi and PDO use them). Makes you code easier, safer and much more robust.

Good luck with it all :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 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

19 Experts available now in Live!

Get 1:1 Help Now