PHP Get Maximum Value From DB and Set Start Value for Loop

[experience40]

I'm importing products from a CSV file and each imported 'product row' is assigned a stock_id which increments as each row is imported

I need to retrieve the existing highest stock_id from the DB and use this as my start value for each row when importing another file so i dont duplicate stock_id's (multiple columns on the CSV so each column on the same row needs to have the same stock_id)

function get_stock_id() {
		//DB connection
		require("../config/db/connection.php");
	
		$get_stock_id = $conn->prepare("SELECT MAX(stock_id) AS max_stock_id FROM stock_1"); 
		$get_stock_id->execute();			
		$stock_id_result=$get_stock_id->get_result();		
		$row = mysqli_fetch_assoc($stock_id_result); 
		$stock_id = $row['max_stock_id'];
	}

As each row is imported the stock_id increases +1 (stock_id ++;), and when using the above code the next row rechecks the 'current' max stock_id leading to increasing start values for the stock_id so a bit like a dog chasing its tail

Instead of assigning stock_id to all columns on the same row for example to 1 1 1 1 1 its assigning 1 2 3 4 5 so all columns have different stock_id's

If i manually set the stock_id = x; or use the above code and add to a session with $_SESSION["stock_id"] = $stock_id; the stock_id start value is set and all row columns have the same ID

Looking for the best way to get the highest stock_id value and use this a start value for the whole loop which doesnt increment for each row

Edited November 10, 2023 by experience40

[Barand]

Define the table you are importing into so its stock_id is auto_incementing and leave it to the database.

As for your existing function ...

• Don't connect the db every time you call the function (connecting is the slowest component). Instead, connect once in your script and pass the connection variable to the function when you call it.
• The function should return the id.
• Use PDO instead of mysqli - it's better and easier.

function get_stock_id($pdo) 
{
        $res = $pdo->query("SELECT MAX(stock_id) AS max_stock_id FROM stock_1"); 
        return res->fetchColumn();
}

 

[experience40]

Each CSV column is inserted into a seperate DB row so i'm unable to leave it to the DB to auto_increment, so the columns needs to link by being assigned a stock_id 

 

 

[Barand]

Something like this, maybe....

BEFORE

TABLE: stockid;        TABLE: product
+----+                 (empty)
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

CSV DATA
------------------------
"A1","A2","A3","A4","A5"
"B1","B2","B3","B4","B5"
"C1","C2","C3","C4","C5"

RUN CODE

$fp = fopen('products.csv', 'r');

// prepare product insert query
$stmt = $pdo->prepare("INSERT INTO product (stock_id, prod_name) VALUES (?, ?)");

while ($row = fgetcsv($fp))  {
    $pdo->exec("INSERT INTO stockid (id) VALUES (NULL)");
    $stock_id = $pdo->lastInsertId();   // get next stock id
    
    foreach ($row as $prod)  {
        $stmt->execute([ $stock_id, $prod ]);
    }
}

fclose($fp);

AFTER

TABLE: stockid;        TABLE: product
+----+                 +----+----------+-----------+
| id |                 | id | stock_id | prod_name |
+----+                 +----+----------+-----------+
| 1  |                 | 1  | 4        | A1        |
| 2  |                 | 2  | 4        | A2        |
| 3  |                 | 3  | 4        | A3        |
| 4  |                 | 4  | 4        | A4        |
| 5  |                 | 5  | 4        | A5        |
| 6  |                 | 6  | 5        | B1        |
+----+                 | 7  | 5        | B2        |
                       | 8  | 5        | B3        |
                       | 9  | 5        | B4        |
                       | 10 | 5        | B5        |
                       | 11 | 6        | C1        |
                       | 12 | 6        | C2        |
                       | 13 | 6        | C3        |
                       | 14 | 6        | C4        |
                       | 15 | 6        | C5        |
                       +----+----------+-----------+

 

[experience40]

thanks for your advice Barand, very much appreciated!

I managed to fix this in the end, looking more carefully through the code i had 2 loops (small loop in a larger loop) which was affecting the call to the stock_id and affecting the incrementing 

I have now moved the initial call outside both loops and then made a reference to it in the larger loop which appears to worked

 

Edited November 10, 2023 by experience40

[mac_gyver]

getting the current highest MAX() value and incrementing it in your code is not concurrent safe. if there are multiple instances of your script running at the same time (you must assume that this is possible), they will all get the same starting value, resulting in duplicate ids.

the correct way of doing this is to have a table with an autoincrement primary index, insert a row of 'main' data, get the last insert id from that query and use it when inserting the data related to each 'main' row of data.