MySQL inserts values into wrong columns

Question

Problem

With a php website, I have a form to collect information which will then be inserted into the MySQL database, but there are these three columns that have the wrong values inserted into them. The rest are all in the correct order.

Values inserted as php variables via MySQL transaction.

Thank you for your time.

phpmyadmin display (first row is manually corrected)

Code:

 <?php
function registerPatient($ptUsername, $ptPassword, $ptFirstName, $ptLastName, $ptSalutation, $ptEmail, $ptDOB, $ptPostCode, $ptHouseNo, $ptTelNo, $link)
{
    $accType = "Patient";
    $dtID = $_COOKIE["ID"];
    $errors = "";
    $SQL_patientInsert =

        "START TRANSACTION;

        INSERT INTO accDetails (`username`, `hashPassword`, `accType`)
        VALUES ('" . $ptUsername . "',
                '" . $ptPassword . "',
                '" . $accType . "');


        INSERT INTO ptProfile (`firstName`, `lastName`, `salutation`, `email`, `DOB`, `postCode`, `houseNo`, `telephoneNo`, `dtID`, `ptID`)
        VALUES ('" . $ptFirstName . "',
                '" . $ptLastName . "',
                '" . $ptSalutation . "',
                '" . $ptEmail . "',
                '" . $ptDOB . "',
                '" . $ptPostCode . "',
                '" . $ptHouseNo . "',
                '" . $ptTelNo . "',
                '" . $dtID . "',
                LAST_INSERT_ID());
        COMMIT;";

        if (mysqli_multi_query($link, $SQL_patientInsert)) {
            $errors .= "";
        } else {
            $errors .= "MYSQL Error: ". mysqli_error($link);
        }


        return $errors;

    }

?>

Var_Dump of $SQL_patientInsert

string(495) "START TRANSACTION; INSERT INTO accDetails (`username`, `hashPassword`, `accType`) VALUES ('bingbong', '$2y$10$WDvSHSxzIxaYB8dPGLRIWOFyIdPXxSw5JDXagOxeYuJUtnvFhI.lO', 'Patient'); INSERT INTO ptProfile (`firstName`, `lastName`, `salutation`, `email`, `DOB`, `postCode`, `houseNo`, `telephoneNo`, `dtID`, `ptID`) VALUES ('Dr', 'Bing', 'Bong', 'EMAIL REMOVED FOR SO', '1996-08-02', 'POSTCODE REMOVED FOR SO', '7', '83824', '1256', LAST_INSERT_ID()); COMMIT;"

Table Structure

Table Structure in PHPMyAdmin, no autoincrements, all values allowed to be null

Warning! You are prone to SQL-injection. Read more here. stackoverflow.com/questions/60174/… — Emanuel Vintilă
– Emanuel Vintilă, Commented Mar 14, 2016 at 16:26
var_dump your $SQL_patientInsert, i'm pretty sure you have an error in varaible maybe order in registerPatient call — Benjamin Poignant
– Benjamin Poignant, Commented Mar 14, 2016 at 16:29
Have you setup your table data types of column properly and auto increment value etc — Umair Khan
– Umair Khan, Commented Mar 14, 2016 at 16:35
link Doesn't appear to show anything incorrect with the variables, that's the vardump of $SQL_patientinsert right after insertion. — Daniel Boyd
– Daniel Boyd, Commented Mar 14, 2016 at 16:40
link - No autoincrements. All values allowed to be null. Can't see any issues with my column datatypes. Any ideas? — Daniel Boyd
– Daniel Boyd, Commented Mar 14, 2016 at 16:48

B.Kocaman · Accepted Answer · 2016-03-14 17:24:54Z

2

Your are calling your function with wrong parameters order.

Change this line ($ptFirstName <-> $ptSalutation);

function registerPatient($ptUsername, $ptPassword, $ptFirstName, $ptLastName, $ptSalutation, $ptEmail, $ptDOB, $ptPostCode, $ptHouseNo, $ptTelNo, $link)

with

function registerPatient($ptUsername, $ptPassword, $ptSalutation, $ptFirstName, $ptLastName, $ptEmail, $ptDOB, $ptPostCode, $ptHouseNo, $ptTelNo, $link)

answered Mar 14, 2016 at 17:24

B.Kocaman

8255 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tadman Over a year ago

When you have this many arguments, passing them all in as a singular array is not a bad idea.

Daniel Boyd Over a year ago

That's fixed it. Thank you very much, it's well appreciated. Have a nice day!

cpalinckx · Accepted Answer · 2016-03-14 16:27:03Z

0

I think you just mixed up your variables somewhere. Have you checked the form? Try printing out all the variables right before you build the query and check if they correspond correctly.

answered Mar 14, 2016 at 16:27

cpalinckx

3692 silver badges5 bronze badges

1 Comment

Daniel Boyd Over a year ago

link Doesn't appear to show anything incorrect with the variables, that's the vardump of $SQL_patientinsert right after insertion, same occurs with vardump before query is run.

Collectives™ on Stack Overflow

MySQL inserts values into wrong columns

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related