PHP Trying to insert data into two different tables using prepared statment

Question

I've been working on this for a few days now and can not seem to find where i am going wrong, I imagine its something silly but as my university tutor has never used prepared statements before he has been of little to no use.

The first statement works a treat with no problems, the second doesn't input any of my data into my database. My goal is to take the information passed through the form (which i can include didn't want to bombard with information as i'm sure that is not the problem)and take the PictureID which is the primary key in my pictures table and insert this aswel as the other inforamtion into my pictureprice table.

any help would be welcomed, I'm fairly new to the site so be gentle please:)

<?php

include_once "dbh.php";

if (empty($imageTitle) || empty($imageDesc)) {
    header("Location:changes.php?upload=empty");
    exit();
} else {
    $sql = "SELECT * FROM pictures;";
    $sqltwo = "SELECT * FROM pictureprice;";
    $stmt = mysqli_stmt_init($conn);
    if (!mysqli_stmt_prepare($stmt, $sql)) {
        header("Location: changes.php?sqlerror=failed");
        exit();
    } else {     //Gallery order//
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);
        $rowCount = mysqli_num_rows($result);
        $setImageOrder = $rowCount + 1;



        $sql = "INSERT INTO pictures (PhotographerID, PictureFolderPath, 
        imageDesc, imgFullNameGallery, orderGallery) VALUES (?, ?, ?, ?, 
         ?);";
        if (!mysqli_stmt_prepare($stmt, $sql)) {
            header("Location: changes.php?sqlerror=failedtoinputdata");
            exit();
        } else {
            mysqli_stmt_bind_param($stmt, "issss", $_SESSION['PhotographerID'], $fileDestination, $imageDesc, $imageFullName, $setImageOrder);
            mysqli_stmt_execute($stmt);
            move_uploaded_file($fileTempName, $fileDestination);

            $result = mysqli_stmt_get_result($stmt);
            $row = mysqli_fetch_assoc($result);
            $photoID = $row["PictureID"];     //new
            header("Location:changes.php?upload=success11");
        }

        $sqltwo = "INSERT INTO pictureprice 
      (PictureID, PictureSize, PictureSize2, PictureSize3, PictureSize4, 
      PicturePrice, PicturePrice2, PicturePrice3, PicturePrice4) VALUES (?, 
         ?, ?, ?, ?, ?, ?, ?, ?);";
        if (!mysqli_stmt_prepare($stmt, $sqltwo)) {
            header("Location: changes.php? 
       sqlerror=failedtoinputdatapictureprice");
            exit();
        } else {
            mysqli_stmt_bind_param($stmt, "issssiiii", $photoID, $picturesize1, $picturesize2, $picturesize3, $picturesize4, $price1, $price2, $price3, $price4);
            mysqli_stmt_execute($stmt);
            header("Location:changes.php?upload=success");
        }

Why does your second SQL start with `SELECT * FROM pictureprice INSERT INTO pi...`? — Nigel Ren, Oct 29 '18 at 14:29
Sorry it did start with INSERT INTO but ive been trying all sorts of different methods i will re amend back to how it was originally. Sorry — Ginn Man, Oct 29 '18 at 14:30
you dont bind the parameters to the second statemant mysqli_stmt_bind_param ?, ?, ?, ?, ?, ?, ?, ? are not replaced by vars mysqli_stmt_bind_param need to be called again — talsibony, Oct 29 '18 at 14:31
Ow right, I was under the assumption that you have to bind the parameters for the information to be locked into the the placeholders.. sorry i couldnt see your whole answer until just now, how would one go about creating placeholders for vars — Ginn Man, Oct 29 '18 at 14:33
@talsibony I thought that was the line `mysqli_stmt_bind_param($stmt, "issssiiii", $photoID, $picturesize1...`? — Nigel Ren, Oct 29 '18 at 14:34
but you have redirect so it will never get to the point you want when mysqli_stmt_bind_param executed — talsibony, Oct 29 '18 at 14:34
@talsibony - https://stackoverflow.com/questions/3553698/php-should-i-call-exit-after-calling-location-header, so as they don't have `exit;` it will carry on. — Nigel Ren, Oct 29 '18 at 14:35
but in my url it doesn't say success11 it says success so i thought it was still trying to execute the code.. I just took out the header location and it didn't seem to make a difference:/ — Ginn Man, Oct 29 '18 at 14:37
Always worth adding some checking from https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-information-in-different-environments — Nigel Ren, Oct 29 '18 at 14:43

score 2 · Accepted Answer · answered Oct 29 '18 at 14:38

2

I think the problem is that the you are trying to get the photo ID from an INSERT statement...

    $result = mysqli_stmt_get_result($stmt);
    $row = mysqli_fetch_assoc($result);
    $photoID = $row["PictureID"];     //new

This probably won't fetching anything meaningful (as far as I can tell).

To get an auto increment value you would normally call...

$photoID = mysqli_insert_id($conn);

answered Oct 29 '18 at 14:38

Nigel Ren

56,122
11
43
55

Aahhh right, i see where you are coming from i never knew this, this is the first time i've ever tried to fetch an auto increment number w3 schools or previous documentation never said. Thankyou for you help i will look into this – Ginn Man Oct 29 '18 at 14:42
Looking into this function i can see you've already pointed me in the right direction, Thank you very much for you help I will be trying this right away soon as i understand the function. Thanks again Nigel i do appreciate any and all help – Ginn Man Oct 29 '18 at 14:44
Your solution worked perfectly, the reply took so long because it wouldn't work until i realised i also forgot to execute the statement *smacks head* – Ginn Man Oct 29 '18 at 15:34

PHP Trying to insert data into two different tables using prepared statment

1 Answers1