Find the last primary key of another table

PHP
1

I have 2 tables which I have INSERT statements in a loop.
Everything goes smoothly with the territories table, but for some reason, the second INSERT places a 0 as the second field (territory_id)
I’m trying to input the primary key of the first table instead though.

foreach($territory as $key => $value) {
$qry = "INSERT INTO territories (city,state) VALUES ('".$key."','".$value."')";
$sth = $dbh->query($qry); 
$qry1 = "INSERT INTO provider_territory (provider_id,territory_id) VALUES ('".$primaryKey."','".($dbh->lastInsertId())."')"; 
$sth = $dbh->query($qry1); 
}

Thanks

2

Do the lastInsertId right after the query.

foreach($territory as $key => $value) {
    $qry = "INSERT INTO territories (city,state) VALUES ('".$key."','".$value."')";
    $sth = $dbh->query($qry);
    $territory_id = $dbh->lastInsertId();
     
    $qry1 = "INSERT INTO provider_territory (provider_id,territory_id) VALUES ('".$primaryKey."','".$territory_id."')"; 
    $sth1 = $dbh->query($qry1); 
}

Note: you really should be binding this input.

3

should be

$qry1 = "INSERT INTO provider_territory (provider_id,territory_id) VALUES ('".$primaryKey."',LAST_INSERT_ID())";

you can get the id directly in the next query rather than using PHP to retrieve it. You only need PHP to retrieve it if you need it for more than one query following.

1 Like
4

thanks felgall!
whats binding"?

5

That’s where you prepare the SQL statement first without providing the actual values and then supply the actual values to use via a second database statement.

This does two things.

  1. The query itself is in the prepare statement while the data is in a different call where it can’t be mixed up with the SQL - making injection completely impossible.
  2. If you are going to run the same query multiple times you can prepare it once and then supply the different data in a loop which makes all the database accesses more efficient because the query itself only needs to be built once.

For example with mysqli

if (!($stmt = $mysqli->prepare("INSERT INTO test(id) VALUES (?)"))) {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}
if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

or PDO

$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));

Both examples copied from http://php.net

6

will give the PDO version a whirl

1 Like
7

it workeed1 is this better

foreach($territory as $key => $value) {
//using the PDO way
$qry = "INSERT INTO territories (city,state) VALUES (:key,:value)";
$stmt = $dbh->prepare($qry);
$stmt->execute(array(
':key' => $key,
':value' => $value
));

$qry = "INSERT INTO provider_territory (provider_id,territory_id) VALUES (:primaryKey,LAST_INSERT_ID())";
$stmt = $dbh->prepare($qry);
$stmt->execute(array(
':primaryKey' => $primaryKey
));
}
8

Put the prepare statement before the loop - you only need to run it once.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.