I'm having problems with inserting a PHP $_POST variable into my mysql table

Question

I have made a script for registration. The form has 7 fields. HTML code is as follows,

<input type="text" id="user_name" />
<input type="password" id="user_password" />  
<input type="text" id="user_surname" />
<input type="email" id="user_email" />               
<input type="text" id="user_phone" />
<input type="text" id="user_address" />
<input type="text" id="user_pin" />
<input type="submit" value="Submit" />

The corresponding PHP code insert into statement is

$query="INSERT INTO register 
VALUES      (

         user_name = '$user_name', 
         user_password = '$pass', 
         user_surname = '$surname', 
         user_email = '$email', 
         user_phone = '$phone', 
         user_address = '$address', 
         user_pin = '$pin') ";

$result = mysql_query($query,$con);
 if($result)
 {
 echo "Details updated successfully";
 }

The query works fine. After running the query I am getting Details updated successfully message. But in my TABLE nothing is updated correctly. Only 1 (number 1) is updated in all the columns in my table. Please help me to solve this issue. Thanks in advance.

You are doing it wrong. Read this :http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496 and update your code accordingly, else you are a danger to everyone who uses your website. Or everybody who uses your website is a danger to your server respectively. — Eike Pierstorff, Oct 28 '13 at 10:36
I hope you filter input data before inserting into database. Or you will — , Oct 28 '13 at 10:38
@Anthony `DEFAULT` is the default value for a column. In this context it's just used wrong and doesn't make sense. — Gerald Schneider, Oct 28 '13 at 10:55

웃웃웃웃웃 · Accepted Answer · 2013-10-28T10:40:13.687

You need to give name attribute to your html elements.Without name attribute no value will be posted after form submission.So try like the following.Remember whatever you give as the name attribute the same will be the key of $_POST array.So in your php after form submitting try the following to see what is are the values submitted via your form.Then do insert query after necessary coding.

echo '<pre>';
print_r($_POST);
die();
<form method="post" action="">
<div class="line"><label for="user_name">Username *: </label><input type="text" id="user_name" name="user_name" /></div>
        <div class="line"><label for="user_password">Password *: </label><input type="password" id="user_password" name="user_password" /></div>  
        <div class="line"><label for="user_surname">Surname *: </label><input type="text" id="user_surname" /></div>
        <div class="line"><label for="user_email">Email *: </label><input type="email" id="user_email" name="user_email" /></div>               
        <div class="line"><label for="user_phone">Telephone: </label><input type="text" id="user_phone" name="user_phone" /></div>
        <div class="line"><label for="user_address">Address *: </label><input type="text" id="user_address" name="user_address" /></div>
        <div class="line"><label for="user_pin">Post Code *: </label><input type="text" id="user_pin" name="user_pin"/></div> <br>
        <div style="margin-left:50px;"><input type="submit" value="Submit" /></div>
   </form>

I commented before you edited your answer. But, you could tell OP how inline styling is not semantically correct, if you want :) — samayo, Oct 28 '13 at 10:39

score 2 · Answer 2 · answered Oct 28 '13 at 10:47

2

Use following

<?php

$user_name = $_POST['user_name'];
$pass = $_POST['user_password'];
$surname = $_POST['user_surname'];
$email = $_POST['user_email'];
$phone = $_POST['user_phone'];
$address = $_POST['user_address'];
$pin = $_POST['user_pin'];


$query="INSERT INTO register 
         (`user_name`,`user_password`,`user_surname`,`user_email`,`user_phone`,`user_address`,`user_pin`) values ('".$user_name."','".$pass."','".$surname."','".$email."','".$phone."','".$address."','".$pin."')";

$result = mysql_query($query,$con);
 if($result)
 {
 echo "Details updated successfully";
 }

answered Oct 28 '13 at 10:47

w3b

823
6
14

`extract($_POST);` will be a much better idea than initializing the variables and assigning them – 웃웃웃웃웃 Oct 28 '13 at 10:49
`extract()` is another barn door for injections. I'd never ever use it on `$_POST` or `$_GET`. – Gerald Schneider Oct 28 '13 at 10:53
To avoid sql injenction prepared statements is the best way @GeraldSchneider – 웃웃웃웃웃 Oct 28 '13 at 11:04

score 1 · Answer 3 · answered Oct 28 '13 at 10:38

For your variables: You have to give the input elements a name attribut, otherwise it will not be available in PHP. On the PHP side, they will not show up magically as variables (hopefully, if they do you have register_globals enabled and you have a huge security risk), they will show up in $_GET or $_POST, depending of your form.

For your SQL: You are mixing two syntaxes, use either this:

INSERT INTO register (user_name,user_password,user_surname,user_email,user_phone,user_address,user_pin) VALUES ('$user_name','$pass','$surname','$email','$phone','$address','$pin')

or

INSERT INTO register SET user_name='$user_name',user_password='$pass',user_surname='$surname',user_email='$email',user_phone='$phone',user_address='$address',user_pin='$pin'

Notes: I have no idea what that DEFAULT is supposed to do/be. mysql_* functions are becoming deprecated, use mysqli_* or PDO instead. Take a look at prepared statements to prevent MYSQL injections.

score 0 · Answer 4 · answered Oct 28 '13 at 11:38

Your issue, as already indicated, is that you don't have the name attribute set for your inputs, which means that nothing is being posted to the server from your form. Once you have that fixed, you should also modify your backend mysql code to use mysqli and prepared statements to improve security and because mysql_ functions are deprecated. Here's an example with your current query:

$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_database');

$stmt = $mysqli->prepare("INSERT INTO register 
(user_name,user_password,user_surname,user_email,user_phone,user_address,user_pin) 
VALUES (?,?,?,?,?,?,?)");

$stmt->bind_param('sssssss', 
    $post_user_name,$post_user_password,$post_user_surname,$post_user_email,
    $post_user_phone,$post_user_address,$post_user_pin);

// Quick and easy way to get all of your POST array values 
    // set to prefixed variables:
extract($_POST, EXTR_PREFIX_ALL, "post");

// you can overwrite any of the variables before running the query, like:

$post_user_password = password_hash($post_user_password, PASSWORD_DEFAULT);

$stmt->execute();

if( $stmt->affected_rows ) {
    echo "Details updated successfully";
}

score 0 · Answer 5 · answered Oct 28 '13 at 12:08

Try this.

<?php
$con=mysql_connect("db_server","$db_user","$db_pass");//Establishing Database connection
mysql_select_db("$db_name",$con);//Selecting required database
$user_name = $_POST['user_name'];
$pass = $_POST['user_password'];
$surname = $_POST['user_surname'];
$email = $_POST['user_email'];
$phone = $_POST['user_phone'];
$address = $_POST['user_address'];
$pin = $_POST['user_pin'];


$query="INSERT INTO register 
         (`user_name`,`user_password`,`user_surname`,`user_email`,`user_phone`,`user_address`,`user_pin`) values ('".$user_name."','".$pass."','".$surname."','".$email."','".$phone."','".$address."','".$pin."')";

$result = mysql_query($query);
 if($result)
 {
 echo "Record is inserted successfully!!";
 }
else
{
echo "Record insertion failed";
}
?>

I'm having problems with inserting a PHP $_POST variable into my mysql table

5 Answers5