Code to Copy Entry from Database

[Anxious]

Assume we have three tables.

• characters with the fields id, project_id, name, basic_info_id, appearance_id.
• char_appearance with fields id, eyes, hair, body, clothing, other.
• char_basic_info with fields id, alias, age, description, personality, backstory.

Note that the basic_info_id and appearance_id fields in the characters table are remnants of an old design: now they are always the same value as the character id. Example entry: id="12" project_id="3" name="Harry Potter" basic_info_id="12" appearance_id="12", img_id="117". Notice how id=basic_info_id=appearance_id=12.

Anyway, I’ve written code to import (i.e. copy) a character.

        $pid = $_POST['projid'];
        $charid = $_POST['charid'];

        $charQuery = "SELECT name FROM characters WHERE id='$charid';";
        $infoQuery = "SELECT alias, age, description, personality, backstory FROM char_basic_info WHERE id='$charid';";
        $looksQuery = "SELECT eyes, hair, body, clothing, other FROM char_appearance WHERE id='$charid';";

        $result1 = mysqli_query($conn, $charQuery);
        $result2 = mysqli_query($conn, $infoQuery);
        $result3 = mysqli_query($conn, $looksQuery);
        $character = mysqli_fetch_all($result1, MYSQLI_ASSOC)[0];
        $basicinfo = mysqli_fetch_all($result2, MYSQLI_ASSOC)[0];
        $appearance = mysqli_fetch_all($result3, MYSQLI_ASSOC)[0];

        $name = $character['name'];
        $personality = $basicinfo['personality'];
        $backstory = $basicinfo['backstory'];
        $alias = $basicinfo['alias'];
        $age = $basicinfo['age'];
        $description = $basicinfo['description'];
        $eyes = $appearance['eyes'];
        $hair = $appearance['hair'];
        $body = $appearance['body'];
        $clothing = $appearance['clothing'];
        $other = $appearance['other'];
       
        //Create new character under current project
        $importQuery = "INSERT INTO characters(project_id, name) VALUES('$pid', '$name');";
        mysqli_query($conn, $importQuery);

        //add basic info and appearance id field to characters table
        $insertedCharId = mysqli_insert_id($conn);
        $query1 = "UPDATE characters SET basic_info_id='$insertedCharId', appearance_id='$insertedCharId' WHERE id='$insertedCharId';";
        mysqli_query($conn, $query1);

        //Create basicinfo and appearance entries
        $query2 = "INSERT INTO char_basic_info(id) VALUES('$insertedCharId');";
        $query3 = "INSERT INTO char_appearance(id) VALUES('$insertedCharId');";
        mysqli_query($conn, $query2);
        mysqli_query($conn, $query3);

        //now add all info
        $importInfoQuery = "UPDATE char_basic_info SET alias='$alias', age='$age', description='$description', personality='$personality', backstory='$backstory' WHERE id='$insertedCharId';";
        $importLooksQuery = "UPDATE char_appearance SET eyes='$eyes', hair='$hair', body='$body', clothing='$clothing', other='$other' WHERE id='$insertedCharId';";
        mysqli_query($conn, $importInfoQuery);
        mysqli_query($conn, $importLooksQuery);

Do you see anything wrong with that code?

For me, this works perfectly fine on my computer… but when I upload the files to my university’s student development server, it doesn’t work properly – it imports everything but the basic info stuff. So the name field from the characters table and everything from the char_appearance table properly transfer, but all of the fields in the char_basic_info table except the primary key id field get created as NULL.

Any idea why this might be happening? I’ve been stuck for only a day (usually wait at least three to post here), but this is already due and I’ve got other exams to study for ToT

[phdr]

What is the top level goal of this assignment? To see if you can design, write, test, and debug - select, insert, and update queries or if you can create a good database design?

The table relationship, with the extra id columns in the characters table, is backwards. The characters table is the primary data. It should have never had basic_info_id and appearance_id columns in it. The two other tables are related to the characters data. You should have a character_id column in both of the two other tables and you should not assume matching primary ids between the tables or manipulate the primary id column in those two tables. The processing, for either creating the original character data or your copy operation should have simply been - insert the new character data into the characters table, get the last insert id from that query, insert the data into the char_basic_info and char_appearance tables. These three insert queries should be part of a transaction so that if any one of the queries fail, they will all be rolled-back.

As to why you are left with no data/null values in the char_basic_info table. That update query is failing with an error, most likely due to either - duplicate data, out of range data, or sql special characters in a value breaking the sql query syntax. You ALWAYS need error handling for statements that can fail. For database statements that can fail - connection, query, prepare, and execute, the simplest way of adding error handling, without adding logic around each statement, is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) To use exceptions for errors for the msyqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Lastly, in order to prevent sql special characters in external, unknown, dynamic values from breaking the sql query syntax (one of the possible reasons for the update query not executing), you need to use a prepared query, instead of directly putting values into the sql query statements. Since the msyqli extension has a completely different programming interface for prepared and non-prepared queries, requiring you to learn essentially two different database extensions for one database type, this would be a good time to switch to use the much simpler and better designed PDO extension, which treats the result from a prepared and a non-prepared query identically.

[Anxious]

Hi,
Like I said in the original post, “the basic_info_id and appearance_id fields in the characters table are remnants of an old design.” I am aware that it is not the best, but I’m not going to be counted off for that (it’s not a database class) so I thought it easier to just leave them rather than having to change multiple files of already-written code.

Do you think you can explain the “processing” you talked about again? It’s kind of hard to follow.

Also, I thought prepared statements were only necessary for accepting user input? Since the original information from the table I am copying uses prepared statements and I’m only performing a hard copy, I didn’t think it necessary to re-prepare already-prepared statements.

Edit: I want to remind that this code works perfectly fine every single time on my computer. It just fails to update the char_basic_info table every single time on the hosted version.