(php) can't get id from table

Question

I have two tables and I want to add data to the first table and get id of the newly inserted data from first table and use it to add the data to second table

$statement = $dbh->prepare("INSERT INTO sis_university (name,site,tel,email,adress) VALUES (:name,:site,:tel,:email,:adress);");
    if($statement->execute(array(':name' => $name,':site' => $site,':tel' => $tel,':email' => $email, ':adress' => $adress))){

        $sql="SELECT id FROM sis_university WHERE site='$site' LIMIT 1";

        $result = mysql_query($sql);
        $row = mysql_fetch_array($result);
        $uniid = $row['id'];

        $statement = $dbh->prepare("INSERT INTO sis_admin (username,password,name,tel,email,adress,type,universityid) VALUES (:username,:password,:name,:tel,:email,:adress,:type,:universityid);");
        if($statement->execute(array(':username' => $username,':password' => $password,':name' => $adminname,':tel' => $admintel, ':email' => $adminemail, ':adress' => $adminadress, ':type' => $admintype, ':universityid' => $uniid))){
            echo "success";
        }else{
            echo "failure" . " " . $uniid;
        }
    }else{
        echo "failure";
    }

data is been added successfully into first table but $uniid is null and data is not getting added to second teble

you appear to be mixing regular, deprecated `mysql_` functions and `PDO` syntax with the placeholders in the sql — Professor Abronsius, Sep 25 '16 at 16:40
you really don't expect your code to work, not with mixing different APIs it won't. — Funk Forty Niner, Sep 25 '16 at 17:15

score 3 · Answer 1 · answered Sep 25 '16 at 16:35

3

You could use mysql_insert_id function http://php.net/manual/es/function.mysql-insert-id.php

Just execute after mysql_query

$id = mysql_insert_id() ;

answered Sep 25 '16 at 16:35

Oscar Gallardo

2,240
3
27
47

score 1 · Accepted Answer · answered Sep 25 '16 at 16:30

1

You can use insert-id for get the id of last inserted row

answered Sep 25 '16 at 16:30

Jithin Kuriakose

317
5
22

score 1 · Answer 3 · answered Sep 25 '16 at 16:31

Heads up, you're using a deprecated MySQL extension. Either Switch to MySQLi or pdo.

To get the last id inserted you can us:

The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute.

If you insist on mysql deprecated there is also mysql_insert_id

score 1 · Answer 4 · answered Sep 25 '16 at 16:32

Note: Usage of Mysql is outdated and it is better you use mysqli.* or PDO along with the prepared statements.

Method: 1

In your Query you can get the latest Inserted ID with the help of mysqli_insert_id().

The mysqli_insert_id() is to run after the query is executed.

Method: 2

As per your structure you can use the following code in order to get the ID after inserted.

Ensure using the echo statement for this and see to that whether you get data over here.

echo $sql="SELECT id FROM sis_university WHERE site='".$site."' LIMIT 1";
exit; // This break the execution over here.

And see to that whether you get the query right.

Example:

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

mysqli_query($con,"INSERT INTO Persons (FirstName,LastName,Age)
VALUES ('Glenn','Quagmire',33)");

// Print auto-generated id
echo "New record has id: " . mysqli_insert_id($con);

mysqli_close($con);
?>

(php) can't get id from table

4 Answers4