Issue inserting data into MySQL with PHP

Question

I am new to PHP. I've been working on a simple form that submits data into a MySQL database and have hit a snag. I'm just not sure where the error in the code is, any help would be appreciated.

<?php

$host      = 'localhost'; // hostname
$username  = 'root'; // MySQL Username
$password  = 'root'; // MySQL Password
$db_name   = 'idp'; // Database name
$tbl_name  = 'data'; // Table name

// Attempt MySQL Connection
mysql_connect("$host", "$username", "$password")or die("Cannot connect.");
// Attempt database connection
mysql_select_db("$db_name")or die("Cannot select DB.");

$name      = $_POST['name'];
$mbr_name   = $_POST['mbr_name'];
$mbr_tel   = $_POST['mbr_tel'];
$date      = $_POST['date'];

$sql     = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('$name',       '$mbr_name', '$mbr_tel', '$date')";
$result  = mysql_query($sql);

if($result) {
echo "Entry Successful";
echo "<br>";
echo "<a href='form.php'>Return to Form</a>";
} else {
echo "<strong>Error</strong>";
}

mysql_close();
?>

This might not be the problem, but try putting actual values within the mysql_connect and mysql_select_db functions instead of variables: `mysql_connect("$host", "$username", "$password")or die("Cannot connect.");` `mysql_select_db("$db_name")or die("Cannot select DB.");` — Joel Murphy, Oct 14 '13 at 17:42
you could do a `$result = mysql_query($sql) or die(mysql_error());` but really, don't use the `mysql_*` functions anymore for new code, they're **deprecated**. — Wrikken, Oct 14 '13 at 17:43
Ahh I forgot to include that. It returns 'Error' so it appears that it connects to the database fine, but won't input the data into the table. — sbdthru, Oct 14 '13 at 17:44
And, if you _must_ use mysql, at least use `mysql_real_escape_string()`, — Wrikken, Oct 14 '13 at 17:45
@user2879819 Just a security suggestion before I move on, you might want to include some SQL injection protection in your code. http://xkcd.com/327/ — Ryan Leonard, Oct 14 '13 at 17:54
@Wrikken Thank you so much.... I didn't even see this at first and tried everyone else's responses before this one. Ended up getting the response 'field date does not have a default value'. Any idea why this would cause issues? — sbdthru, Oct 14 '13 at 21:24
@sbdthru: if a field is non-NULLable, and has no default, it **must** be present in an `INSERT` statement and valid (or possiblly made valid by a cast to that type). It seems here, that `$date` does not hold a value which could be considered valid for the `date` column, so it balks. Examine that value. — Wrikken, Oct 14 '13 at 22:53
`mysql` is deprecated. Consider changing the `mysql` functions to `mysqli` (http://php.net/manual/book.mysqli.php) or to `PDO` (http://br1.php.net/manual/book.pdo.php). — Minoru, Oct 15 '13 at 11:43

score 1 · Accepted Answer · edited May 23 '17 at 11:50

My guess is, one of the input parameters you are getting has unescaped ' or " character(s) in it. Thus, your query breaks due to the unintended quote characters. To verify if this is the case, introduce another print statement for the sql just after you have created your sql statement and run it in manually in your query browser and see if it works.

Further, the if($result) { block in your code prints out a success or failure message irrespective of why the failure occured, so the output information is not particularly useful either.

You should have a look at the [addslashes][1] method to escape those quote characters.

Your query will then become somthing like

$sql = "INSERT INTO $tbl_name(name, date)VALUES('".addslashes($name)."', '".$date."')";

Also, I would suggest having a look at pdo for your sql queries instead of mysql_* functions.

score 0 · Answer 2 · answered Oct 14 '13 at 17:49

0

Try you query like that :

$sql     = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('".$name."',       '".$mbr_name."', '".$mbr_tel."', '".$date."')";

answered Oct 14 '13 at 17:49

Anerty

81
1
1
9

This results in the same query. If there's sql injection code, this would not stop it. It would not fix the OP's issue. – aynber Oct 14 '13 at 18:15
this line of code is only to show (one of many ways) to escape variable inside a query string, protection against XSS attack, encoding issues, new lines, .... should be done before this step, and there is a lot of classes (Zend_DB_* classes) that could be implemented. – Anerty Oct 15 '13 at 11:35

score 0 · Answer 3 · answered Oct 14 '13 at 18:52

Remove the "" on your mysql_connect($host,$username,$password); mysql_select_db($db_name); Its a good habit

$sql = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('{$name}','{$mbr_name}','{$mbr_tel}', '{$date}')";

Also check your data type for date. If the date you are posting is current , you can use sql function instead of the POST for date for example:

$sql = "INSERT INTO $tbl_name(name, mbr_name, mbr_tel, date)VALUES('{$name}','{$mbr_name}','{$mbr_tel}',NOW())";

score 0 · Answer 4 · answered Oct 15 '13 at 11:39

your code is deprecated, please refer to Zend classes loader, with the Zend_db_* classes.

[http://framework.zend.com/manual/1.12/fr/zend.db.statement.html][1]

PS : Zend is a Framework, you could use any popular framework like Yii, CakePhp and others.

Issue inserting data into MySQL with PHP

4 Answers4