2 Queries to 2 different tables in same DB, in PHP with MySQL PDO

Question

The basic idea is to take contact info as well as vehicle info from a single html form, and using a single php file, insert the corresponding data into a 'member' table and 'vehicle' table respectively.

I've looked at a few somewhat similar examples and haven't got them to work for me. Is this possible to do? Or would I need to separate the requested info into 2 different forms and handle via 2 php files?

I'm using phpMyAdmin with php and PDO.

User entered info stored as an array and binded via a for loop.

    // DB Interaction
    try{
        // Connection to DB
        require "includes/db-info.php";
        $dbh = new PDO("mysql:host=$serverName; dbname=$dbName", $userName, $password);

        // Inserts into member and vehicle table
        $stmt = $dbh->prepare("INSERT INTO member (first_name, last_name, address, state, zip_code, email, password, phone_number, member_status, 
            member_type, insurance_provider, policy_number, number_of_claims, business_id) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
            INSERT INTO vehicle (member_id, make, model, year, color, vin) 
            VALUES (?, ?, ?, ?, ?, ?)");

        // Inserts array elements into the placeholders
        for ($i = 1; $i < 22; $i++){
            $stmt->bindParam($i, $memberFormElements[$i]);
        }

        $stmt->execute();
        $stmt = null;
        $dbh = null;

    } catch(PDOException $e){
        echo $stmt . "<br/>" . $e->getMessage();
        die();
    }

I've also tried separating the two queries and assigning different variable names before proceeding to bind, but it ends up only entering data into the second 'vehicle' table.

// DB Interaction
    try{
        // Connection to DB
        require "includes/db-info.php";
        $dbh = new PDO("mysql:host=$serverName; dbname=$dbName", $userName, $password);

        // Inserts into member table
        $stmt = $dbh->prepare("INSERT INTO member (first_name, last_name, address, state, zip_code, email, password, phone_number, member_status, 
            member_type, insurance_provider, policy_number, number_of_claims, business_id) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        // Inserts array elements into the placeholders
        // Starts at 1 due to 'prepare' indexing. Array accounted for.
        for ($i = 1; $i < 16; $i++){
            $stmt->bindParam($i, $memberFormElements[$i]);
        }

        $stmt->execute();

        $stmt2 = dbh->prepare("INSERT INTO vehicle (member_id, make, model, year, color, vin) 
            VALUES (?, ?, ?, ?, ?, ?)");

        // Inserts array elements into the placeholders
        // Starts at 1 due to 'prepare' indexing. Array accounted for.
        for ($i = 1; $i < 7; $i++){
            $stmt2->bindParam($i, $vehicleFormElements[$i]);
        }  

        $stmt2->execute();

        $stmt = null;
        $dbh = null;

    } catch(PDOException $e){
        echo $stmt . "<br/>" . $e->getMessage();
        die();
    }

Please use error reporting and let us know what the behavior is when the first inset fails. Also, not your issue but, `phpMyAdmin` is not related to the question currently, that is just a UI for interacting with a mysql DB. — user3783243, May 24 '20 at 05:19
Weirdly enough, the statement completes without catching an error. Nothing appears in my DB however. This is the case with both examples, only in the second example the second table gets populated but not the first. — code_dough, May 24 '20 at 05:29
`var_dump($stmt->rowCount())` gives back what in statement two? — user3783243, May 24 '20 at 05:36
Obviously, you don't have to separate forms or PHP files. But you **have** to separate SQL queries, hence separate the form data into 2 arrays. And I never seen an HTML form that sends the data using numeric indices. — Your Common Sense, May 24 '20 at 06:36
Regarding your attempt to report errors, it's a double cargo cult. You are using try catch which is a cargo cult by itself, as PHP reports a thrown exception **without** this operator. At the same time you didn't tell PDO to throw exceptions, making this code double useless. Instead of try catch you have to set error reporting mode for PDO. Hope it is clear now — Your Common Sense, May 24 '20 at 06:41
All in all, there is nothing special in running 2 queries against 2 different tables. It can be ten queries against five different tables. or any other number. All queries are independent. You just run them one by one. — Your Common Sense, May 24 '20 at 07:10

2 Queries to 2 different tables in same DB, in PHP with MySQL PDO

0 Answers0