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. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Emanuel Vintilă, Mar 14 '16 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, Mar 14 '16 at 16:29
[link](http://imgur.com/guSN6LL) Doesn't appear to show anything incorrect with the variables, that's the vardump of $SQL_patientinsert right after insertion. — Daniel Boyd, Mar 14 '16 at 16:40
Ok, really strange. Did you try to insert just 3 columns *`firstName`, `lastName`, `salutation`* ? — B.Kocaman, Mar 14 '16 at 17:14
**WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). **NEVER** put `$_POST` data directly into a query. — tadman, Mar 14 '16 at 17:24
Have you setup your table data types of column properly and auto increment value etc — Umair Khan, Mar 14 '16 at 16:35
[link](http://imgur.com/tg7ZPGk) - No autoincrements. All values allowed to be null. Can't see any issues with my column datatypes. Any ideas? — Daniel Boyd, Mar 14 '16 at 16:48

score 2 · Accepted Answer · answered Mar 14 '16 at 17:24

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 '16 at 17:24

B.Kocaman

800
4
13

When you have this many arguments, passing them all in as a singular `array` is not a bad idea. – tadman Mar 14 '16 at 17:25
That's fixed it. Thank you very much, it's well appreciated. Have a nice day! – Daniel Boyd Mar 14 '16 at 17:34

score 0 · Answer 2 · answered Mar 14 '16 at 16:27

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 '16 at 16:27

cpalinckx

369
2
5

[link](http://imgur.com/guSN6LL) 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. – Daniel Boyd Mar 14 '16 at 16:44

MySQL inserts values into wrong columns

2 Answers2