Inserting data into multiple tables not functioning correctly

Question

I have the following two tables

Table player:

player_id (int)(primary)
player_name (varchar)
player_report_count (int)

Table report:

report_id (int)(primary)
player_id
report_description
report_location

Firstly I ask the user for the player_name and insert it into the player database. From here the player is given an id.

Then I tried to grab the value of the players report count and increment the current value by one (which isn't working).

This is followed by grabbing the playerId from the player table and then inserting into the corresponding column from the report table (also does not work).

When I insert some values into the database, the names, description and report are added to the database however the playerID remains at 0 for all entries and the player_report_count remains at a consistent 0.

What is the correct way to make these two features function? And also is there a more efficient way of doing this?

<?php
$records = array();

if(!empty($_POST)){
if(isset($_POST['player_name'],
    $_POST['report_description'],
    $_POST['report_location'])){

    $player_name = trim($_POST['player_name']);
    $report_description = trim($_POST['report_description']);
    $report_location = trim($_POST['report_location']);

    if(!empty($player_name) && !empty($report_description) && !empty($report_location)){
        $insertPlayer = $db->prepare("

        INSERT INTO player (player_name)
        VALUES (?)

        ");

        $insertPlayer->bind_param('s', $player_name);


        $reportCount = $db->query("
            UPDATE player
            SET player_report_count = player_report_count + 1
            WHERE
            player_name = $player_name

        ");

        $getPlayerId = $db->query("
        SELECT player_id
        FROM player
        WHERE player_name = $player_name
        ");

        $insertReport = $db->prepare("

        INSERT INTO report (player_id, report_description, report_location)
        VALUES (?, ?, ?)

        ");
        $insertReport->bind_param('iss', $getPlayerId, $report_description, $report_location);

        if($insertPlayer->execute()
        && $insertReport->execute()
        ){
            header('Location: insert.php');
            die();
        }


    }


}

score 3 · Accepted Answer · edited May 23 '17 at 11:59

Main issue here is you are getting player details before inserting it. $getPlayerId will return empty result always.

Please follow the order as follows.

Insert player details in to player table and get payerid with mysql_insert_id. After binding you need to execute to insert details to the table.
Then bind and execute insert report .
Then update the player table by incrementing report count with playerid which you got in step 1.

Note : use transactions when inserting multiple table. This will help you to rollback if any insert fails.

MySQL Query will return result object. Refer it from here https://stackoverflow.com/a/13791544/3045153

I hope it will help you

score 1 · Answer 2 · answered Aug 07 '15 at 01:24

If you need to catch the ID of the last insterted player, This is the function you need if you're using PDO or if it's a custom Mysql Class, you need the return value of mysql_insert_id() (or mysqli_insert_id()) and then directly use it in the next INSERT INTO statement

Inserting data into multiple tables not functioning correctly

2 Answers2