php - MySQL : insert only if another table condition ... AND retry if not

Question

Tables

product	review
product_id	product_id
status	author
	text

question from:https://stackoverflow.com/questions/65879484/mysql-insert-only-if-another-table-condition-and-retry-if-not

Answer 1

Don't use a separate query to get min/max product IDs. Use a JOIN with the product table in an INSERT ... SELECT.

$stmt = $con->prepare('
    INSERT INTO review (product_id, author, text)
    SELECT product_id, ?, ?
    FROM product
    WHERE status = 1
    ORDER BY RAND()
    LIMIT 1');
$stmt->bind_param("ss", $data["name"], $data["commentary"]);

Then call $stmt->execute() in the insert loop.