Adding Data to a MySQL Database from an HTML Form

Question

HI all i've a basic Web Form for putting data into a mysql database, I created code to report if i was connected to my Database correctly and it was so on completion of the form i tested it and it seems to do what i expected but when i goto my database no data was actually entered? I've tried this locally and on a server with both doing the same thing. Here is my two .php forms for you to look that i used on my local machine to test in MAMP just incase i have done something wrong:

virtualWalkLog.php

<form action="hazardsform.php" method="POST"  />
  <p>ROUTE: <input type="text" name="ROUTE" /></p>
  <p>ADDRESS: <input type="text" name="ADDRESS" /></p>
  <p>LATITUDE: <input type="text" name="LATITUDE" /></p>
  <p>LONGITUDE: <input type="text" name="LONGITUDE" /></p>
  <p>HAZARD: <input type="text" name="HAZARD" /></p>
  <p>RISK: <input type="text" name="RISK" /></p>
  <input type="submit" value="Submit" />
</form>

hazardsform.php

<?php

define('DB_NAME', 'virtualWalkLog');
define('DB_USER', 'root');
define('DB_PASSWORD', 'root');
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['ROUTE'];
     $value = $_POST['ADDRESS'];
     $value = $_POST['LATITUDE'];
     $value = $_POST['LONGITUTE'];
     $value = $_POST['HAZARD'];
     $value = $_POST['RISK'];

     $sql = "INSERT INTO rmbhazards (ROUTE, ADDRESS, LATITUDE, LONGITUDE, HAZARD, RISK) VALUES ('$value', '$value2', 
     '$value3', '$value4', '$value5', '$value6')";

     mysql_close();

Many Thanks in advance

You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Apr 21 '13 at 08:53

score 6 · Answer 1 · edited May 23 '17 at 11:53

6

you are not exectuing your query, this is why no data is inserted. Try to place after

$sql = "INSERT INTO rmbhazards (ROUTE, ADDRESS, LATITUDE, LONGITUDE, HAZARD, RISK) VALUES ('$value', '$value2', '$value3', '$value4', '$value5', '$value6')";

this

$result = mysql_query($sql);

also all values are in one variable $value, so you will end up with all the same result in your table so change to this to fit your query

 $value = $_POST['ROUTE'];
 $value2 = $_POST['ADDRESS'];
 $value3 = $_POST['LATITUDE'];
 $value4 = $_POST['LONGITUTE'];
 $value5 = $_POST['HAZARD'];
 $value6 = $_POST['RISK'];

I would also sugeest you to stop using mysql_ api since they are depecrated, please switch to PDO or mysqli

Furthermore you are ready to mysql injection. there is a nice tutorial here which explain you everything about that -> How can I prevent SQL injection in PHP?

edited May 23 '17 at 11:53

Community

1
1

answered Apr 21 '13 at 08:49

Fabio

23,183
12
55
64

Thank you so much for pointing out my mistakes, after reading your comments i managed to get this working thank you. – Elfuthark Apr 21 '13 at 10:14
Until i have read up on mysql_* i am going to work with a form, the form isn't going to be public knowledge to people, just to one person but can sql injection still find my form? also my form is sending latitude and longitude coordinates to my DB, The latitude sends perfectly but the latitude that is a negative number i.e. -6.12345 shows up in my database as 0. Any ideas why this might be happening? – Elfuthark Apr 21 '13 at 13:27
@futhark Is it a get form or a post one? – Fabio Apr 21 '13 at 14:24

alwaysLearn · Accepted Answer · 2013-04-21T08:58:18.717

Going through your script quickly you need to call mysql_query($sql) after

$sql = "INSERT INTO rmbhazards (ROUTE, ADDRESS, LATITUDE, LONGITUDE, HAZARD, RISK) VALUES ('$value', '$value2', '$value3', '$value4', '$value5', '$value6')";

mysql_sql query will actually execute the query.

Also as $value should be unique

 $value = $_POST['ROUTE'];
 $value2 = $_POST['ADDRESS'];
 $value3 = $_POST['LATITUDE'];

 -----

SUGGESTION Since you have just begin ..I will suggest you try mysql_* for just concepts but use mysqli_* or PDO .. You shold also know about sql injection

Here are some tutorials to help you

http://php.net/manual/en/security.database.sql-injection.php

http://php.net/manual/en/book.pdo.php

http://php.net/manual/en/book.mysqli.php

score 3 · Answer 3 · answered Apr 21 '13 at 08:51

you are assigning values to only one variable $value here

 $value = $_POST['ROUTE'];
 $value = $_POST['ADDRESS'];
 $value = $_POST['LATITUDE'];
 $value = $_POST['LONGITUTE'];
 $value = $_POST['HAZARD'];
 $value = $_POST['RISK'];

should be

 $value = $_POST['ROUTE'];
 $value2 = $_POST['ADDRESS'];
 $value3 = $_POST['LATITUDE'];
 $value4 = $_POST['LONGITUTE'];
 $value5 = $_POST['HAZARD'];
 $value6 = $_POST['RISK'];

Also call mysql_query($sql); for running the query.

Vivek Sadh · Answer 4 · 2013-04-21T08:56:21.060

You are capturing all the input fields value into one variable. You need to execute mysql_query for it to work. Change this :-

     $value = $_POST['ROUTE'];
     $value = $_POST['ADDRESS'];
     $value = $_POST['LATITUDE'];
     $value = $_POST['LONGITUTE'];
     $value = $_POST['HAZARD'];
     $value = $_POST['RISK'];

to:-

 $value = $_POST['ROUTE'];
 $value2 = $_POST['ADDRESS'];
 $value3 = $_POST['LATITUDE'];
 $value4 = $_POST['LONGITUTE'];
 $value5 = $_POST['HAZARD'];
 $value6 = $_POST['RISK'];

Once you have done that, you need to call mysql_query($sql) to execute the query.

score 2 · Answer 5 · answered Apr 21 '13 at 08:51

2

just rename:

$value = $_POST['ROUTE'];
$value2 = $_POST['ADDRESS'];
$value3 = $_POST['LATITUDE'];
$value4 = $_POST['LONGITUTE'];
$value5 = $_POST['HAZARD'];
$value6 = $_POST['RISK'];

answered Apr 21 '13 at 08:51

Triple_6

89
10

score 1 · Answer 6 · answered Apr 21 '13 at 08:50

You kep all the variables as the same name

 $value = $_POST['ROUTE'];
 $value = $_POST['ADDRESS'];
 $value = $_POST['LATITUDE'];
 $value = $_POST['LONGITUTE'];
 $value = $_POST['HAZARD'];
 $value = $_POST['RISK'];

change them to unique id's (as you referenced in the sql statement)

 $value1 = $_POST['ROUTE'];
 $value2 = $_POST['ADDRESS'];
 $value3 = $_POST['LATITUDE'];
 $value4 = $_POST['LONGITUTE'];
 $value5 = $_POST['HAZARD'];
 $value6 = $_POST['RISK'];

and change your query statement to actually execute

 $result = mysql_query("INSERT INTO rmbhazards (ROUTE, ADDRESS, LATITUDE, LONGITUDE, HAZARD, RISK) VALUES ('$value', '$value2', 
 '$value3', '$value4', '$value5', '$value6')");

Adding Data to a MySQL Database from an HTML Form

6 Answers6

Linked