How to assign integer value of mysql row unique ID to PHP variable

Question

Long time user. First time question (big thanks to all for making stack overflow such an awesome resource). I have a mysql database call from my PHP 7.4 function where I want to pull the unique, auto_increment id from the row I am working on with my query. However, I keep getting the following message:

Notice: Undefined index: id in /Users/username/Sites/tbd/functions.php on line 57

I have searched for an answer and have not found anything other than reference to using mysql_insert_id() for the most recently inserted id. However, my function is called right after opening the db so there is no previous activity for mysql_insert_id() to call upon.

Here's the code:

     $query = 'SELECT file_path, file_name FROM uploadTable WHERE action="pending"';
     if ($result = $db->query($query)) {
        /* fetch associative array */
        $row = $result->fetch_assoc();
        $filepath = $row['file_path'];
        $filename = $row['file_name'];
        $id = $row['id'];  // NEED TO KNOW WHICH ROW TO UPDATE LATER
        /* FREE RESULT SET */
        $result->free();
    }

Since id is an integer I have tried with no quotes and double quotes. Same result. Other than the id issue the query is succesful and my other variables are populated correctly. I thought about a type declaration for the $id variable, but PHP does not support that structure as I understand it. Any help is much appreciated. Thanks.

score 1 · Accepted Answer · edited Jun 08 '20 at 21:33

1

Your $row consists of all columns that are in SELECT statement.

In your case it would be file_path and file_name, but no id selected.

Change your query to

SELECT id, file_path, file_name FROM uploadTable WHERE action="pending"

edited Jun 08 '20 at 21:33

Barmar

741,623
53
500
612

answered Jun 08 '20 at 21:32

Justinas

41,402
5
66
96

Thanks Barmar. Don't know how I missed that but I bet you saved me an hour of chasing down the rabbit hole. This is my first question, so hopefully checking it as the answer that helped me gives you the credit. Thank you again! – Snuff Jun 08 '20 at 21:42

score 1 · Answer 2 · answered Jun 08 '20 at 21:32

1

You need to select the id column:

$query = 'SELECT id, file_path, file_name FROM uploadTable WHERE action="pending"';

This assumes that id is the name of the unique ID column in your table. Replace it with the actual column name in your table.

answered Jun 08 '20 at 21:32

Barmar

741,623
53
500
612

How to assign integer value of mysql row unique ID to PHP variable

2 Answers2