Unable to INSERT data into MySQL through HTML form using PHP

Question

Here is my Form:

<html>
<head>
<title>Stats</title>
</head>
<body>
<h2>Member Information Form</h2>

<form action="submit_mbr_nfo.php" method="post">
    Member ID <input type ="text" name= "mbrid"/><br>
    Member Name <input type="text" name="mbrnm"/><br>
    Actual Name <input type="text" name="atlnm"/><br>
    <input type="submit" value="Save"/>
</form>

</body>
</html>

Here is my PHP file:

<?php
//Define database properties in global variables
define('DB_NAME', 'STATS');
define('DB_USER', 'root');
define('DB_PASSWORD', 'Test');
define('DB_HOST', 'localhost');

//store connection props in var
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);

//check connection
if (!$link) {
    die ('Could not connect to the Database: ' . mysql_error());
}

//map $_POST to vars
$mbr_id = mysql_real_escape_string($link, $_POST['mbrid']);
$mbr_nm = mysql_real_escape_string($link, $_POST['mbrnm']);
$atl_nm = mysql_real_escape_string($link, $_POST['atlnm']);

 $sql = 'INSERT INTO MBR_NFO '.'(MBR_ID,MBR_NM,ATL_NM) '.'VALUES ('$mbr_id', '$mbr_nm','$atl_nm')';

mysql_select_db('STATS');
$exe_query = mysql_query( $sql, $link);
?>

And here is my php error log:

PHP Parse error:  syntax error, unexpected '$mbr_id' (T_VARIABLE) in /Applications/MAMP/htdocs/stats/submit_mbr_nfo.php on line 21

I am very new and learning PHP and HTML, i tried several online solutions but nothing has worked so far. I am able to insert into DB if I don't use $_POST, i.e., manually typing in the values in php code, but that's not the goal, the goal is to use Form to populate MySQL DB. Any help is appreciated, thank you.

If you are new and learning php then start learning `mysqli OR PDO` Because mysql is deprecated — Saty, Dec 29 '15 at 06:34

score 2 · Answer 1 · answered Dec 29 '15 at 06:26

2

Try following query

 $sql = "INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES ('$mbr_id', '$mbr_nm','$atl_nm')";

answered Dec 29 '15 at 06:26

Sadikhasan

18,365
21
80
122

this worked, however, it inserted blank values for MBR_NM and ATL_NM except for PK(MBR_ID). – Amit Kumar Dec 29 '15 at 14:49

score 0 · Answer 2 · answered Dec 29 '15 at 06:55

0

You are having issues with string concatenation and quotes. Try following query:

$sql = "INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES('$mbr_id', '$mbr_nm','$atl_nm')";

answered Dec 29 '15 at 06:55

Suyog

2,472
1
14
27

score 0 · Answer 3 · answered Dec 29 '15 at 09:11

if you set primary key and auto increment on database for member id so it very easy.you are not write mbr_id in query

$query="INSERT INTO MBR_NFO (MBR_NM,ATL_NM) VALUES( '$mbr_nm','$atl_nm')";

it's simple way

if you want not set primary key and autoincrement and try this code

$query ="INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM). VALUES('$mbr_id', '$mbr_nm','$atl_nm')";

score 0 · Answer 4 · answered Dec 29 '15 at 10:09

You should use prepared statements (see below) instead of manually concateting the query string. But, since you’re new to PHP, let us first fix your code. The line

$sql = 'INSERT INTO MBR_NFO '.'(MBR_ID,MBR_NM,ATL_NM) '.'VALUES ('$mbr_id', '$mbr_nm','$atl_nm')';

has a couple of flaws. In PHP, string concatenation is done via the dot . operator, which you have used only partly. In order to construct the query string $sql, you have to add a couple of dots:

$sql = 'INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES (' . $mbr_id . ', ' . $mbr_nm . ',' . $atl_nm . ')';

While this is valid PHP syntax, it is still no valid SQL. If your user input is $mbr_id = 42, $mbr_nm = 'amit', $atl_nm = 'Amit Kumar', then after concatenation, $sql looks like

INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES (1, amit, Amit Kumar)

and is missing quotes around the strings amit and Amit Kumar. At best, this makes your query invalid; at worst, it makes your query prone to injection attacks. Therefore, build your query using

$sql = 'INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES ("' . $mbr_id . '", "' . $mbr_nm . '","' . $atl_nm . '")';

or, because in PHP, variables in strings that are quoted with double quotes – e.g. "my name is $name", but not 'my name is $name' – are evaluated:

$sql = "INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES ('$mbr_id', '$mbr_nm,'$atl_nm')";

By far the best practise, however, is using prepared statements and parameterized queries:

$con = new PDO('mysql:host=localhost;dbname=STATS', 'root', 'Test');

$stmt = $con->prepare('INSERT INTO MBR_NFO (MBR_ID,MBR_NM,ATL_NM) VALUES (:mbr_id, :mbr_nm, :atl_nm)');
$stmt->bindValue(':id', $mbr_id);
$stmt->bindValue(':mbr_nm', $mbr_nm);
$stmt->bindValue(':atl_nm', $atl_nm);
$stmt->execute();

thanks, I tried all the above and nothing worked, I am still missing something. Thanks for letting me know about the Prep statements and param queries, I will study those as well. — Amit Kumar, Dec 29 '15 at 20:47
@AmitKumar In which way did it not work? Do you get an error (which?) or an undesired behaviour? — Lumen, Dec 30 '15 at 09:01

Unable to INSERT data into MySQL through HTML form using PHP

4 Answers4