Solved

obtain id of just added mysql record in php

Posted on 2014-02-03
11
402 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
[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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 110

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 43

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
 

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 43

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 43

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 43

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article discusses how to implement server side field validation and display customized error messages to the client.
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 and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

733 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