CODEWITHSUNDEEP
phpmysqldatabase
Renee Rose-Perry
Renee Rose-Perry

Reputation: 222

Insert into 2 tables from 1 form. Mysql Transaction?

The user will create an article and submit an image with it.

+The article will go to the articles table.

+The image will go to images table (so that it can be used in other areas of the site).

It has been suggested I use TRANSACTIONS but I am getting errors.

    $sql ='BEGIN INSERT INTO articles(article_title, article_text, article_date)
            VALUES (?, ?, NOW())
            INSERT INTO images(article_id, image_caption)
            VALUES(LAST_INSERT_ID(),?);
            COMMIT';

 $stmt = $conn->stmt_init();
    if ($stmt->prepare($sql)) {
    $stmt->bind_param('sss', $_POST['article_name'], $_POST['description'], $_POST['image_caption']);

        $OK = $stmt->execute();

        printf("%d Row inserted.\n", $stmt->affected_rows);

        $stmt->free_result();

    } else{
        echo "Failure- article not uploaded";
    }

Upvotes: 4

Views: 2915

Answers (3)

Niek van der Steen
Niek van der Steen

Reputation: 1433

It looks like you are using PDO (nice!). With PDO, you can get your transactions in an easy way with beginTransaction() and commit()

Your code would look like:

$pdo->beginTransaction();
// .. fire your 'normal' queries.
// .. and yet some more queries
$pdo->commit();

Then, I'd personally write separate INSERT queries in just two separate statements. More readable in my opinion.

Example:

$pdo->beginTransaction();

$first = $pdo->prepare('INSERT INTO table (field, otherField) VALUES(?,?)');
$second = $pdo->prepare('INSERT INTO table (field, otherField) VALUES(?,?)');

$first->execute(array( .. your data (values) .. ));
$second->execute(array( .. your data (values) .. ));

$pdo->commit();

Upvotes: 2

Arthur Halma
Arthur Halma

Reputation: 4001

$mysqli->query("START TRANSACTION");
$stmt = $mysqli->prepare('INSERT INTO articles(article_title, article_text, article_date) VALUES (?, ?, NOW())');
$stmt->bind_param('ss', $_POST['article_name'], $_POST['description']);
$stmt->execute();
$stmt = $mysqli->prepare('INSERT INTO images (article_id, image_caption) VALUES(LAST_INSERT_ID(),?)');
$stmt->bind_param('s', $_POST['image_caption']);
$stmt->execute();
$stmt->close();
$mysqli->query("COMMIT");

Upvotes: 4

Amir
Amir

Reputation: 4111

$sql ='START TRANSACTION;
       INSERT INTO articles (article_id,article_title, article_text, article_date) VALUES (NULL,?, ?, NOW());
       INSERT INTO images (article_id, image_caption) VALUES(LAST_INSERT_ID(),?);
       COMMIT;';

Upvotes: 0

Related Questions