Retrieving last inserted iD then uploading to MySQL. #PDO #PHP

Question

I have a form that uploads Textarea Information and an Image to MySQL simultaneously.

The Textarea iD is AUTO_INCREMENT while the Image iD must be the textarea's copy. I've tried within the $_POST to get lastInsertId(); which returns Fatal error: Call to undefined function lastInsertId().

This is my current code for the isset that uploads the information to the Function and/or query:

if(isset($_POST['update']) && isset($_FILES['photo1'])){
    $update = $_POST['update'];
    $data = $Wall->Insert_Update( $uiD, $update);

    $name       = $_FILES['photo1']['name'];
    $tmp_name   = $_FILES['photo1']['tmp_name'];
    $target = "uploads/". $_FILES['photo1']['name'];

    if (move_uploaded_file($tmp_name,$target)) {
        $sth = $db->prepare("SELECT post_iD FROM posts WHERE uid_fk = :uiD");
        $sth->execute(array(':uiD' => $uiD));

        $post_iD = lastInsertId();
        $sth = $db->prepare('INSERT INTO user_uploads (image_path, uid_fk, image_id_fk) VALUES (:image_path, :uiD, :image_id_fk)');
        $sth->execute(array(':image_path' => $target, ':uiD' => $uiD, ':image_id_fk' => $post_iD));
    }
}

Here is the Insert_Update that is uploading the textarea:

PUBLIC FUNCTION Insert_Update( $uiD, $update){
$sth = $this->db->prepare("SELECT post_iD,message FROM posts WHERE uid_fk = :uiD ORDER by post_iD DESC LIMIT 1");
$sth->execute(array(':uiD' => $uiD));

$result = $sth->fetch();

        if ($update!=$result['message']){

            $sth = $this->db->prepare("INSERT INTO posts ( message, uid_fk, ip, created) VALUES ( :update, :id, :ip, :time)");
            $sth->execute(array(':update' => $update, ':id' => $uiD, ':ip' => $_SERVER['REMOTE_ADDR'], ':time' => time()));

            $sth = $this->db->prepare("
                                        SELECT M.post_iD, M.uid_fk, M.message, M.created, U.username 
                                        FROM Posts M, users U 
                                        WHERE M.uid_fk=U.uiD 
                                        AND M.uid_fk = ? 
                                        ORDER by M.post_iD DESC LIMIT 1");
            $sth->execute(array($uiD));

            $result = $sth->fetchAll();
            return $result;
            } else {
            return false;
        }
}

FORM:

<form method="POST" action="" enctype="multipart/form-data">
    <textarea name="update" id="update" class="_iType"></textarea>
    <input type="file" name="photo1">
    <input type="submit" value="post" class="update_button"> 
</form>

More Information within the database which I doubt it'll be useful.

posts table : where the textarea information will be located.

*post_iD | message | uid_fk | ip | created |*
user_uploads table : Where the image location will be located.

*image_iD | image_path | uid_fk | image_id_fk*

NOTICE: image_id_fk should equal post_iD ( which is obvious ).

How is the lastInsertId(); suppose to be used?

EDIT 1: After upload completes the inserted value for the image_id_fk equals 0, instead of the post_iD value. Any ideas for this reason?

sybear · Accepted Answer · 2013-06-28T07:08:35.673

1

It can be used right after you successfully execute an INSERT statement. However, you have to always check whether the execution was successful (or are there affected rows) because there can be last insert ID from previously executed statement.

$sth = $this->db->prepare("INSERT INTO posts ( message, uid_fk, ip, created) VALUES ( :update, :id, :ip, :time)");
$sth->execute(array(':update' => $update, ':id' => $uiD, ':ip' => $_SERVER['REMOTE_ADDR'], ':time' => time()));

$this->db->lastInsertId(); //Upon success, is available

For example: You have a function that creates a new post. This function on success can return the last insert ID value, so you can display that value to user. Like:

if (isset($_GET['id'])){
  $id = $manager->createPost($_GET['id']) ;
  if (is_numeric($id)){
    echo "Your article has been created. ID: {$id}" ; //Or link to it.
  }
}

Update:

Here is my suggestion to your problem: lastInsertId always retuns 0:

Your table, where you insert values, must have PRIMARY KEY AUTO_INCREMENT field, preferrably with type int.

edited Jun 28 '13 at 07:08

answered Jun 28 '13 at 06:52

sybear

7,837
1
22
38

Thank you for being very detailed on that function. What if lastInsertId(); isn't retrieving any values at all, and uploading the value of 0 to the database? as that's what it's currently doing! – Attis Jun 28 '13 at 07:02
Do you have an `AUTO_INCREMENT` field in your DB? – sybear Jun 28 '13 at 07:03
Not for the image_id_fk which that must equal the post_iD which does have AUTO_INCREMENT. – Attis Jun 28 '13 at 07:04
Okey, you must have a unique `AUTO_INCREMENT` id for each item in your table. – sybear Jun 28 '13 at 07:06
Well, as I mentioned in the thread itself. image_id_fk should equal the last inserted id so that I can retrieve the image within a loop. textarea = post_iD. image = image_id_fk = post_iD. So they need to match, because they've been uploaded together. Even then, should I have AUTO_INCREMENT for the image_id_fk, wouldn't it have a different id value? EDIT: you're right and it does have the auto increment but I'm just trying to match both id's so I can pull the data together in a loop. – Attis Jun 28 '13 at 07:08
I think your `image_ID` should be `AUTO_INCREMENT` and `PRIMARY KEY` – sybear Jun 28 '13 at 07:11
image_iD has AUTO_INCREMENT and PRIMARY KEY. Though that isn't the issue I'm having. EX: The issue is when I press the upload button, textarea gives an id of 333, and the image_id_fk gives an id of 0, I'd like the image_id_fk to match the textarea id, so that I may return both the textarea information and image together. – Attis Jun 28 '13 at 07:16
I am sorry, it is just a bit hard to figure out what you need :( – sybear Jun 28 '13 at 07:23
It's no problem at all, you helped most and I got it to work so I appreciate your help. You were correct tho, that was what I needed and it was a mistake of my part I just needed to delete a query that was useless. – Attis Jun 28 '13 at 07:25

score 0 · Answer 2 · answered Jun 28 '13 at 06:51

0

You are accessing pdo lastinsertid in wrong way. It requires database connection object.

$post_iD = lastInsertId();

should be

$post_iD = $db->lastInsertId();

answered Jun 28 '13 at 06:51

Yogesh Suthar

30,424
18
72
100

You are correct my good man. I forgot to mention when it does upload it always enters a value of 0 never the lastinsertiD value. – Attis Jun 28 '13 at 06:53
@iBrazilian Can you clarify this? – Yogesh Suthar Jun 28 '13 at 06:57
Of course. Instead of inserting the lastInsertId(); it uploads the value of 0. As if lastInsertId(); wasn't retrieving any values at all. ON MySQL database, image_id_fk = 0 instead of ex: post_iD = 344 while image_id_fk = 0. Was that understandable? – Attis Jun 28 '13 at 07:00
@iBrazilian Before using `lastInsertId()` you are using select statement. So do you want the id of select statement? – Yogesh Suthar Jun 28 '13 at 07:16
Yes. That's the point, since I have a form and I'm uploading a a file and text information, I'd like both of them to have the same iD so that I can pull the information together. Or I'd like the LastInsertId() to return the same post_iD that the Insert_Update function is uploading. – Attis Jun 28 '13 at 07:18
@iBrazilian `lastInsertId` will give you auto increment id when the last executed sql query will be `INSERT` query, but as I can see you use `SELECT` query before using `lastInsertId` and because of that it gives you `0`. – Yogesh Suthar Jun 28 '13 at 07:24

Retrieving last inserted iD then uploading to MySQL. #PDO #PHP

2 Answers2