image upload with foreign key

Question

I'm trying to add an image upload function to my form, that uses an own table. The problem is, it needs the 'cardid' which is a foreign key, because that's how I want to store which image belongs to which card. The problem is, I can't get the cardid because I want to upload the image on the same page where the user creates the card. So, it has no ID yet. My 'cards' table looks like this:

id - name - phone - more fields - userid

and the'cardimages' table:

id - cardid - image - name

here's my code:

<?php
session_start();
header('Content-type: text/html; charset=utf-8');
require_once("db_connect.php");

// Prepare an insert statement
$sql = "INSERT INTO cards (name, phone, phone2, email, zipcode, address, company, job, description, userid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

if($stmt = mysqli_prepare($conn, $sql)){
    // Bind variables to the prepared statement as parameters
    mysqli_stmt_bind_param($stmt, "ssssissssi", $name, $phone, $phone2, $email, $zipcode, $address, $company, $job, $description, $userid);


    if(isset($_POST['name'])){
        $name = $_POST['name'];
    }
    if(isset($_POST['phone'])){
        $phone = $_POST['phone'];
    }
    if(isset($_POST['phone2'])){
        $phone2 = $_POST['phone2'];
    }
    if(isset($_POST['email'])){
        $email = $_POST['email'];
    }
    if(isset($_POST['zipcode'])){
        $zipcode = $_POST['zipcode'];
    }
    if(isset($_POST['address'])){
        $address = $_POST['address'];
    }
    if(isset($_POST['company'])){
        $company = $_POST['company'];
    }
    if(isset($_POST['job'])){
        $job = $_POST['job'];
    }
    if(isset($_POST['description'])){
        $description = $_POST['description'];
    }

    if(isset($_SESSION['id'])){
        $userid = $_SESSION['id'];
    }


if(isset($_POST['upload'])){
    $name = $_FILES['file']['name'];
    $target_dir = "upload/";
    $target_file = $target_dir . basename($_FILES["file"]["name"]);

    // Select file type
    $imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));

    // Valid file extensions
    $extensions_arr = array("jpg","jpeg","png","gif");

    // Check extension
    if( in_array($imageFileType,$extensions_arr) ){

        // Convert to base64 
        $image_base64 = base64_encode(file_get_contents($_FILES['file']['tmp_name']) );
        $image = 'data:image/'.$imageFileType.';base64,'.$image_base64;
        $sql = "SELECT id FROM cards";
        if(isset($_POST['id'])){
            $cardid = $_POST['id'];
        }

        // Insert record
        $sql = "INSERT INTO cardimages(cardid, name, image) VALUES ('".$cardid."', '".$name."','".$image."')";

        // Upload file
        move_uploaded_file($_FILES['file']['tmp_name'],'upload/'.$name);

    }

}


    // Attempt to execute the prepared statement
    if(mysqli_stmt_execute($stmt)){
        header("Location: addbusiness.php?message=1");
    } else{
        echo "ERROR: Could not execute query: $sql. " . mysqli_error($conn);
    }
} else{
    echo "ERROR: Could not prepare query: $sql. " . mysqli_error($conn);
}




// Close statement
mysqli_stmt_close($stmt);

// Close connection
mysqli_close($conn);
?>

You can insert card row, next select last row from db and use idcard for upload image. — Simone Rossaini, Mar 04 '20 at 14:42
You need ID from table 'cars', so insert row into cars (and id will be create), next use SELECT query for select last row insert and catch id for upload image with card ID. you need example? — Simone Rossaini, Mar 04 '20 at 14:49

score 0 · Accepted Answer · edited Mar 06 '20 at 18:57

I change the two insert, add select for catch last insert id card then i upload image and insert in DB

<?php

session_start();
header('Content-type: text/html; charset=utf-8');
require_once "db_connect.php";

// Prepare an insert statement
$sql = "INSERT INTO cards (name, phone, phone2, email, zipcode, address, company, job, description, userid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = mysqli_prepare($conn, $sql);
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "ssssissssi", $name, $phone, $phone2, $email, $zipcode, $address, $company, $job, $description, $userid);
if (mysqli_stmt_execute($stmt)) {
    if (isset($_POST['name'])) {
        $name = $_POST['name'];
    }
    if (isset($_POST['phone'])) {
        $phone = $_POST['phone'];
    }
    if (isset($_POST['phone2'])) {
        $phone2 = $_POST['phone2'];
    }
    if (isset($_POST['email'])) {
        $email = $_POST['email'];
    }
    if (isset($_POST['zipcode'])) {
        $zipcode = $_POST['zipcode'];
    }
    if (isset($_POST['address'])) {
        $address = $_POST['address'];
    }
    if (isset($_POST['company'])) {
        $company = $_POST['company'];
    }
    if (isset($_POST['job'])) {
        $job = $_POST['job'];
    }
    if (isset($_POST['description'])) {
        $description = $_POST['description'];
    }

    if (isset($_SESSION['id'])) {
        $userid = $_SESSION['id'];
    }

    if (isset($_POST['upload'])) {
        $name = $_FILES['file']['name'];
        $target_dir = "upload/";
        $target_file = $target_dir . basename($_FILES["file"]["name"]);

        // Select file type
        $imageFileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

        // Valid file extensions
        $extensions_arr = array("jpg", "jpeg", "png", "gif");

        // Check extension
        if (in_array($imageFileType, $extensions_arr)) {

        // Convert to base64
            $image_base64 = base64_encode(file_get_contents($_FILES['file']['tmp_name']));
            $image = 'data:image/'.$imageFileType.';base64,'.$image_base64;
            $selectid = $conn->prepare("SELECT id FROM cards DESC limit 1");
            $selectid->execute();
            $resultid = $selectid->get_result();
            $res = $resultid->fetch_array();
            $cardid = $res['id'];

            // Insert record
            $imagequery = $conn->prepare("INSERT INTO cardimages(cardid, name, image) VALUES (?,?,?)");
            $imagequery->bind_param('iss', $cardid, $name, $image);
            $imagequery->execute();

            // Upload file
            move_uploaded_file($_FILES['file']['tmp_name'], 'upload/'.$name);
        }
    }

    header("Location: addbusiness.php?message=1");
} else {
    echo "ERROR: Could not execute query: $sql. " . mysqli_error($conn);
}

// Close statement
mysqli_stmt_close($stmt);

// Close connection
mysqli_close($conn);

Another change is all query prepared. remember alwys do it.Reference

I get this error with your code: ```ERROR: Could not execute query: INSERT INTO cards (name, phone, phone2, email, zipcode, address, company, job, description, userid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?). Column 'name' cannot be null``` I changed the image's 'name' to 'imgname' but still get this. — MowerQQ, Mar 04 '20 at 16:16
If I change the line above bind_param to this ```if($stmt = mysqli_prepare($conn, $sql)){``` and delete the line under it, then it works, but the database doesn't get the image data. — MowerQQ, Mar 04 '20 at 16:21
name cannot be null, seems like variable is not declare or simple empty. change if execute with simple execute for test it, then try again — Simone Rossaini, Mar 05 '20 at 07:12
You should enable error reporting: [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Mar 06 '20 at 18:57

image upload with foreign key

1 Answers1