CODEWITHSUNDEEP
phpmysqlsql
zomboble
zomboble

Reputation: 833

How to get value from sql of newly increment id PHP

I will first show you the code, it's easier to discuss this way.

 $message = $_GET['r'];
    $message = str_replace("%20", " ", $message);
    echo $message;

    mysql_query("INSERT INTO `uniform` (`note`,`cadetID) 
    VALUES ('$message','$cadet')");

    $query = mysql_query("SELECT `uniformID` FROM `cadets` WHERE id = '$cadet'");
    while ($row = mysql_fetch_array($query)) {
      $uniformA = $row['uniformID'];
    }

    if($uniformA == "0"){
      mysql_query("UPDATE `cadets` SET `uniformID` = '" .  ."'
      WHERE id = '$cadet'");
    }

Don't worry about sanatisation for now.

I got to the line: 

mysql_query("UPDATE `cadets` SET `uniformID` = '" .  ."' WHERE id = '$cadet'");

and realised I need the ID of the sql insert

mysql_query("INSERT INTO `uniform` (`note`,`cadetID) VALUES ('$message','$cadet')");

Is there an efficient way of getting the newly incremented ID from the database? Table structure as follows:

cadet(id[primary key, auto increment],...,uniformID)

uniform(id[prim key, auto inc], note, cadetID)

Data:

Cadet: 1,...,[1,2,3] <- all in the [] are in cadetID, array delimited by ','

Uniform: 1, need a new blahh blahh, 1

Uniform: 2, new shoes needed, 1

And so forth.

So, in E-R speak, one cadet can have many uniform ID's.

TL;DR I need to store the new ID in the database (auto incremented by SQL db) on a insert into

Upvotes: 1

Views: 816

Answers (3)

VolkerK
VolkerK

Reputation: 96159

MySQL stores the last auto_increment value per session/connection. You can fetch this value both on the client (i.e. you php script's) side or simply use it on the server (i.e. the MySQL) side via the LAST_INSERT_ID() function

see

self-contained example (using PDO - you wanted to look into it anyway ;-) )

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);

$stmtUniform = $pdo->prepare('INSERT INTO so_uniforms (labelid) VALUES (?)');
$stmtAssignToCadet = $pdo->prepare('
  UPDATE
    so_cadets
  SET
    uniform_id=LAST_INSERT_ID()
  WHERE
    id=?
');

// add uniform and assign to cadet #1
$stmtUniform->execute( array('uniform1') );
$stmtAssignToCadet->execute( array(1) );

// add uniform and assign to cadet #3
$stmtUniform->execute( array('uniform2') );
$stmtAssignToCadet->execute( array(3) );

// add uniform and assign to cadet #4
$stmtUniform->execute( array('uniform3') );
$stmtAssignToCadet->execute( array(4) );

// show cadets<->uniforms
$query = '
    SELECT
        c.id, u.labelid
    FROM
        so_cadets as c
    LEFT JOIN
        so_uniforms as u
    ON
        c.uniform_id = u.id
';

foreach( $pdo->query($query, PDO::FETCH_ASSOC) as $row ) {
    echo join(', ', $row), "\n";
}

// boilerplate
function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE so_cadets (
            id int auto_increment,
            name varchar(32),
            uniform_id int default 0,
            primary key(id)
        )
    ');

    $pdo->exec('
        CREATE TEMPORARY TABLE so_uniforms (
            id int auto_increment,
            labelid varchar(32),
            primary key(id),
            unique key(labelid)
        )
    ');

    $stmt = $pdo->prepare('INSERT INTO so_cadets (name) VALUES (?)');
    foreach( range('A','F') as $c ) {
        $stmt->execute( array('cadet'.$c));
    }
}

prints

1, uniform1
2, 
3, uniform2
4, uniform3
5, 
6,

Upvotes: 2

000
000

Reputation: 3950

You can also use mysqli functions

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "INSERT INTO `uniform` (`note`,`cadetID) VALUES ('$message','$cadet')";
$mysqli->query($query);

echo $mysqli->insert_id; //echoes the newly incremented id

Upvotes: 1

Peon
Peon

Reputation: 8020

I would suggest you change that to PDO and access it simply as this:

$query = "INSERT INTO `uniform` ( `note`, `cadetID` ) VALUES ( ?, ? )";
$params = array( $message, $cadet );

$stmt = $db->prepare( $query );
$stmt->execute( $params );

$last_id = $db->lastInsertId();

Here is nice tutorial and how to do that: http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

But in your case, you can use a query like this ( although it is not as safe ):

SELECT LAST_INSERT_ID();

Upvotes: 2

Related Questions