PHP-SQL，sql中的内容被推翻了

Question

When I connect the php to mysql right between sql and php.

I put the sheight and sheight1 in same column and trying to make AK in its own column. AK goes into the same colunm as sheight and pushes everything over.

<?php    
define('DB_NAME', 'sueng2_mp');
define('DB_USER', 'sueng2_jonathon');
define('DB_PASSWORD', 'jonathon');
define('DB_HOST', 'localhost');

$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db(DB_NAME, $link);

if (!$db_selected) {
    die('Can\'t use ' . DB_NAME . ': ' . mysql_error());
}


$value = $_POST['patient'];
$value2 = $_POST['newamputee'];
$value3 = $_POST['yearamputee'];
$value4 = $_POST['year'];
$value5 = $_POST['gender'];
$value6 = $_POST['age'];
$value7 = $_POST['height'] . '.' . $_POST['height_inch'];
$value8 = $_POST['weight'];
$value9 = $_POST['foot_length'];
$value10 = $_POST['sheight'] . '.' . $_POST['sheight1'];
$value11 = $_POST['ak'];
$value12 = $_POST['bk'];
$value13 = $_POST['left'];
$value14 = $_POST['right'];
$value15 = $_POST['bilateral'];
$value16 = $_POST['light_flesh'];
$value17 = $_POST['dark_flesh'];
$value18 = $_POST['k2'];
$value19 = $_POST['k3'];
$value20 = $_POST['k4'];


$sql = "INSERT INTO order_form (patient, newamputee, yearamputee, year, gender, age, height, weight, foot_length, sheight, ak, bk, left1, right1, bilateral, 

light_flesh, dark_flesh, k2, k3) VALUES ('$value', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10' '$value11', 

'$value12', '$value13', '$value14', '$value15', '$value16', '$value17', '$value18', '$value19', '$value20')";

if (!mysql_query($sql)) {
    die('Error: ' . mysql_error()) ;
mysql_close();
}
?>

Answer 1

You really should not use the mysql_* functions anymore. They are deprecated. Instead you should use PDO or mysqli.

Now on to your immediate issues... Basically your SQL is problematic:

1. You are missing a , between $value10 and $value11
2. You have more values you are trying to insert than you do columns defined.

You probably could have easily spotted these if you had just formatted your SQL better. For example you could use sprintf and/or strtr to make this much easier to read.

First off stop using sperate variables, use an array:

// we will use the column names for the query as the array keys
$data = array(
    'patient' => $_POST['patient'],
    'newamputee' => $_POST['newamputee'],
    'yearamputee' => $_POST['yearamputee'],
    'year' => $_POST['year'],
    'gender' => $_POST['gender'],
    'age' => $_POST['age'],
    'height' => $_POST['height'] . '.' . $_POST['height_inch'],
    'weight' => $_POST['weight'],
    'foot_length' => $_POST['foot_length'],
    'sheight' => $_POST['sheight'] . '.' . $_POST['sheight1'],
    'ak' => $_POST['ak'],
    'bk' => $_POST['bk'],
    'left1' => $_POST['left'],
    'right1' => $_POST['right'],
    'bilateral' => $_POST['bilateral'],
    'light_flesh' => $_POST['light_flesh'],
    'dark_flesh' => $_POST['dark_flesh'],
    'k2' => $_POST['k2'],
    'k3' => $_POST['k3'],
    'k4' => $_POST['k4'],
);

So now with some relatively simple manipulation we can format everything and get it ready to go without having to worry about the order:

// we will use sprintf to substitute our strings in the query
// so lets set a template to populate with the columns and values
$sql = 'INSERT INTO order_form (%s) VALUES (%s)';

// get our column names
$columns = array_keys($data);

// use array map to quote/escape all of our values!
// note you you are using an older version of PHP you may not be able
// to use an anonymous function here, you could define a function or
// just use a foreach loop... either way
$values = array_map(function($val) { 
    return "'" . mysql_real_escape_string($val) . "'";
}, $data);

// now we just do simple string formatting
$insert = sprintf($sql, implode(',', $columns), implode(',', $values));

// and run the query
if (!mysql_query($insert)) {
    die('Error: ' . mysql_error());
    mysql_close();
}